EditWorkbook.aspx.cs
using System;
using System.Data;
using System.IO;
using System.Web.UI;
using ExpertXls.ExcelLib;
using ExcelLibraryDemos;
/// <summary>
/// Edit a Workbook: the workbook code is in App_Code\EditWorkbookDemo.cs.
/// </summary>
public partial class EditWorkbookPage : Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void buttonCreate_Click(object sender, EventArgs e)
{
ExcelWorkbookFormat format = radioXlsx.Checked ? ExcelWorkbookFormat.Xlsx_2007 : ExcelWorkbookFormat.Xls_2003;
byte[] workbookBytes;
try
{
// create the workbook in memory; the input files are in the folders of the web site
using (MemoryStream stream = new MemoryStream())
{
EditWorkbookDemo.CreateWorkbook(format, Server.MapPath("~"), stream);
workbookBytes = stream.ToArray();
}
}
catch (Exception ex)
{
labelError.Text = "The workbook could not be created: " + Server.HtmlEncode(ex.Message);
return;
}
WorkbookDownload.Send(Response, workbookBytes, EditWorkbookDemo.OutputName + DemoLayout.GetExtension(format), format);
}
}
App_Code/EditWorkbookDemo.cs
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;
namespace ExcelLibraryDemos
{
/// <summary>
/// Opens an existing workbook (TestDocs\GettingStarted.xls or .xlsx), renames its worksheets, writes new cells,
/// inserts a row, adds a chart and saves the result as a new workbook.
/// </summary>
public static class EditWorkbookDemo
{
/// <summary>The name of the generated file, without extension.</summary>
public const string OutputName = "EditedWorkbook";
/// <summary>
/// Edits the test document of the given format and saves the result to the output stream.
/// </summary>
/// <param name="format">The format of the document to edit, which is also the format of the result.</param>
/// <param name="inputFolder">The folder that contains the TestDocs folder of the sample.</param>
/// <param name="output">The stream that receives the workbook.</param>
public static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
{
string testDocument = Path.Combine(inputFolder, "TestDocs",
"GettingStarted" + DemoLayout.GetExtension(format));
// the format of an opened workbook is the format of its file
ExcelWorkbook workbook = new ExcelWorkbook(testDocument);
// workbook.LicenseKey = "your license key";
workbook.DocumentProperties.Subject = "Edited workbook";
workbook.DocumentProperties.Comments = "Edited with ExpertXls Excel Library for .NET";
try
{
ExcelWorksheet firstWorksheet = workbook.Worksheets[0];
ExcelWorksheet secondWorksheet = workbook.Worksheets[1];
firstWorksheet.Name = "Edited " + firstWorksheet.Name;
secondWorksheet.Name = "Edited " + secondWorksheet.Name;
// new named styles can be added to an existing workbook
ExcelCellStyle labelStyle = workbook.Styles.AddStyle("EditLabelStyle");
labelStyle.Font.Size = 12;
labelStyle.Font.Bold = true;
labelStyle.Font.Color = Color.Green;
labelStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
ExcelCellStyle dateStyle = workbook.Styles.AddStyle("EditDateStyle");
dateStyle.Font.Size = 12;
dateStyle.Font.Bold = true;
dateStyle.Font.Color = Color.DarkBlue;
dateStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Right;
dateStyle.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center;
dateStyle.Number.NumberFormatString = "mm/dd/yyyy hh:mm:ss";
foreach (ExcelWorksheet worksheet in new ExcelWorksheet[] { firstWorksheet, secondWorksheet })
{
// insert a row at the top: the existing cells, merged ranges, pictures and charts move down
worksheet.InsertRow(1);
worksheet["A1:D1"].Merge();
worksheet["A1"].Text = "Edited on:";
worksheet["A1:D1"].Style = labelStyle;
worksheet["A1:D1"].RowHeightInPoints = 30;
worksheet["E1:G1"].Merge();
worksheet["E1"].Value = DateTime.Now;
worksheet["E1:G1"].Style = dateStyle;
}
// read a value of the opened document: the title of the second worksheet, now in row 3
string chartSheetTitle = secondWorksheet["A3"].Text;
// add a chart to the existing data of the second worksheet (B8:G13 after the inserted row)
secondWorksheet["A15:G15"].Merge();
secondWorksheet["A15"].Text = "The chart below was added to the existing worksheet \"" + chartSheetTitle + "\"";
secondWorksheet["A15:G15"].Style = labelStyle;
secondWorksheet["A15:G15"].RowHeightInPoints = 30;
ExcelChart chart = secondWorksheet.Charts.AddChart(ExcelChartType.LineMarkers, secondWorksheet["B8:G12"],
false, 2, 16, 8, 33);
chart.Title.Text = "Units Sold per Product (added chart)";
DemoLayout.FormatChart(chart, workbook.Format, "Units sold", "Quarters");
workbook.Save(output);
}
finally
{
workbook.Close();
}
}
}
}