public void WorksheetMultipleStyleTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
style.IsItalic = true;
style.SetBackgroundColor("FF0000");//(red)
WorksheetWriter.PasteText(doc, worksheetPart, "B", 2, "Hello world");
WorksheetWriter.SetStyle(style, doc, worksheetPart, "B", 2);
style = SpreadsheetReader.GetDefaultStyle(doc);
style.IsBold = true;
style.SetBackgroundColor("0000FF");//(blue)
WorksheetWriter.SetStyle(style, doc, worksheetPart, "C", 3);
WorksheetWriter.PasteText(doc, worksheetPart, "C", 3, "Hello world2");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\styled2.xlsx", GetOutputFolder()), stream);
}
/// <summary>
/// Excutes the Command
/// </summary>
public override void Execute()
{
WorksheetWriter.MoveTo("A1");
WorksheetWriter.WriteRow(header);
WriteItems(hierarchy.Enterprise, 1);
}
public void WorksheetSetStylesTest()
{
List <string> values = new List <string>(new string[] { "alpha", "beta", "charlie", "delta" });
List <string> values2 = new List <string>(new string[] { "echo", "foxtrot", "golf", "hotel" });
//Write to the spreadsheet
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteValues("A2", values, CellValues.String);
writer.PasteValues("A3", values2, CellValues.String);
//The centre four styles should be aligned to center
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
style.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
writer.SetStyle(style, "B2", "C3");
//Set style in non existing cells
writer.SetStyle(style, "B5", "C6");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\stylerange.xlsx", GetOutputFolder()), stream);
}
public void WorksheetAddStyleTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
SpreadsheetStyle defaultStyle = SpreadsheetReader.GetDefaultStyle(doc);
defaultStyle.IsItalic = true;
defaultStyle.IsBold = true;
defaultStyle.IsUnderline = true;
defaultStyle.SetColor("FF0000");
//r = 255, g = 0, b = 0 (Red)
defaultStyle.SetBackgroundColor("DDDDDD");
//(light grey)
defaultStyle.SetBorder("00FF00", BorderStyleValues.Medium);
//(green medium border)
WorksheetWriter.PasteText(doc, worksheetPart, "E", 5, "Hello world");
WorksheetWriter.SetStyle(defaultStyle, doc, worksheetPart, "E", 5);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\styled.xlsx", GetOutputFolder()), stream);
}
public void WorksheetDataTablePasteColumnsTemplate()
{
DataTable dt = new DataTable();
//Manually create table for mockup purposes
dt.TableName = "tblParameters";
dt.Columns.Add("intID", typeof(int));
dt.Columns.Add("vchrDescription", typeof(string));
dt.Columns.Add("decPrice", typeof(float));
dt.AcceptChanges();
dt.Rows.Add(new object[] { 1, "Parts", 1.00F });
dt.Rows.Add(new object[] { 2, "Cheque", 1.50F });
dt.Rows.Add(new object[] { 3, "Products", 1.45F });
dt.Rows.Add(new object[] { 4, "Gifts", 0.00F });
dt.Rows.Add(new object[] { 5, "DealerRepair", 2.50F });
//Write to the spreadsheet
MemoryStream stream = SpreadsheetReader.Copy(string.Format("{0}\\Templates\\template.xlsx", Directory.GetCurrentDirectory()));
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteDataTable(dt, "B3", new List <string>(new string[] { "vchrDescription" }));
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\datatabletemplate.xlsx", GetOutputFolder()), stream);
}
/// <summary>
/// Write values to the spreadsheet.
/// </summary>
/// <param name="cellLocation">Row Column Value.</param>
/// <param name="strValue">Value to write.</param>
/// <param name="spreadSheet">Spreadsheet to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
int intValue = 0;
DateTime dateValue = new DateTime();
if (strValue.Contains("$"))
{
strValue = strValue.Replace("$", "");
strValue = strValue.Replace(",", "");
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (int.TryParse(strValue, out intValue))
{
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (string.IsNullOrEmpty(strValue))
{
workSheetWriter.PasteText(cellLocation, strValue);
}
//else if (DateTime.TryParse(strValue,out dateValue))
//{
// workSheetWriter.PasteDate(cellLocation, dateValue);
//}
else
{
workSheetWriter.PasteText(cellLocation, strValue, SpreadsheetStyle.GetDefault(spreadSheet));
}
}
/// <summary>
/// Write the values for the rows from headers.
/// </summary>
/// <param name="rowData">Excel row values.</param>
/// <param name="headerData">Excel header values.</param>
/// <param name="rowNum">Row number.</param>
/// <param name="maxWidth">Max width.</param>
/// <param name="spreadSheet">Spreadsheet to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteRowsFromHeaders(IEnumerable <T> rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
maxWidth = 0;
foreach (object row in rowData)
{
int colNum = 0;
foreach (string header in headerData)
{
string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument <T> .WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
}
public void WorksheetDataTablePasteTest()
{
DataTable dt = new DataTable();
//Manually create table for mockup purposes
dt.TableName = "tblParameters";
dt.Columns.Add("intID", typeof(int));
dt.Columns.Add("vchrDescription", typeof(string));
dt.Columns.Add("dteValidFrom", typeof(DateTime));
dt.Columns.Add("decPrice", typeof(float));
dt.Columns.Add("bitFlag", typeof(Boolean));
dt.AcceptChanges();
dt.Rows.Add(new object[] { 1, "Parts", new DateTime(1974, 1, 2), 1.00F, true });
dt.Rows.Add(new object[] { 2, "Cheque", new DateTime(1974, 2, 2), 1.50F, false });
dt.Rows.Add(new object[] { 3, "Products", new DateTime(1974, 3, 2), 1.45F, true });
dt.Rows.Add(new object[] { 4, "Gifts", new DateTime(1974, 4, 2), 0.00F, false });
dt.Rows.Add(new object[] { 5, "DealerRepair", new DateTime(1974, 5, 2), 2.50F, true });
//Write to the spreadsheet
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
style.IsBold = true;
writer.PasteDataTable(dt, "B3", style);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\datatable.xlsx", GetOutputFolder()), stream);
}
/// <summary>
/// Write values to the spreadsheet.
/// </summary>
/// <param name="cellLocation">Row Column Value.</param>
/// <param name="strValue">Value to write.</param>
/// <param name="spreadSheet">Spreadsheet to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
int intValue = 0;
if (strValue.Contains("$"))
{
strValue = strValue.Replace("$", "");
strValue = strValue.Replace(",", "");
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (int.TryParse(strValue, out intValue))
{
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (string.IsNullOrEmpty(strValue))
{
workSheetWriter.PasteText(cellLocation, strValue);
}
else
{
workSheetWriter.PasteText(cellLocation, strValue);
}
}
public ExcelWriter()
{
_stream = SpreadsheetReader.Create();
_doc = SpreadsheetDocument.Open(_stream, true);
var worksheetPart = SpreadsheetReader.GetWorksheetPartByName(_doc, "Sheet1");
_writer = new WorksheetWriter(_doc, worksheetPart);
}
/// <summary>
/// 将查询出的DataSet导出为Excel。
/// </summary>
/// <param name="dataSet">要导出的DataSet数据集，可以包含多个DataTable数据，使用DataSetName作为导出的文件名</param>
public static void ExportDataSet(DataSet dataSet)
{
if (dataSet == null)
{
throw new ArgumentException("dataSet");
}
if (dataSet.Tables.Count == 0)
{
return;
}
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
//首先清空原有的Sheet
var allSheet = doc.WorkbookPart.Workbook.Descendants <Sheet>();
if (allSheet.Count() > 0)
{
doc.WorkbookPart.Workbook.Sheets.RemoveAllChildren();
}
//构造Excel
foreach (DataTable dt in dataSet.Tables)
{
WorksheetPart sheetPart = SpreadsheetWriter.InsertWorksheet(doc, dt.TableName);
WorksheetWriter sheetWriter = new WorksheetWriter(doc, sheetPart);
//打印列名
int colIndex = 0;//当前第几列，从0开始
foreach (DataColumn col in dt.Columns)
{
string reference = GetExcelColumnHeader(colIndex) + "1";
sheetWriter.PasteText(reference, col.ColumnName);
colIndex++;
}
sheetWriter.PasteDataTable(dt, "A2");
SpreadsheetWriter.Save(doc);
}
//Write to response stream
System.Web.HttpContext context = System.Web.HttpContext.Current;
context.Response.Clear();
string UserAgent = context.Request.ServerVariables["http_user_agent"].ToLower();
string FileName = dataSet.DataSetName;
if (UserAgent.IndexOf("firefox") == -1)
{//非火狐浏览器
FileName = context.Server.UrlEncode(FileName);
}
context.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", FileName + ".xlsx"));
context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
stream.WriteTo(context.Response.OutputStream);
context.Response.End();
}
public void WorksheetPasteDate()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
Cell cell = writer.PasteDate("D3", new DateTime(2009, 12, 20, 15, 23, 05));
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\date.xlsx", GetOutputFolder()), stream);
}
public void WorksheetColumnSplitTest()
{
MemoryStream stream = SpreadsheetReader.Copy(string.Format("{0}\\Templates\\columnstemplate.xlsx", Directory.GetCurrentDirectory()));
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.FindColumn("C").Width = 20;
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\columnsplit.xlsx", GetOutputFolder()), stream);
}
public void WorksheetNumberFormatTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
style.FormatCode = "0.00";
writer.PasteNumber("B3", "123", style);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\numberformat.xlsx", GetOutputFolder()), stream);
}
public void WorksheetSharedTextTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteSharedText("B2", "Shared Text");
writer.PasteSharedText("B3", "Shared Text");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\sharedtext.xlsx", GetOutputFolder()), stream);
}
public void WorksheetPasteValuesTest()
{
List <string> values = new List <string>(new string[] { "alpha", "beta", "charlie", "delta" });
//Write to the spreadsheet
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter.PasteValues(doc, worksheetPart, "A", 2, values, CellValues.String, null);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\pastevalues.xlsx", GetOutputFolder()), stream);
}
public void WorksheetOverlappingBorderTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
//Also use coordinates that are reversed
writer.DrawBorder("B4", "D2", "FF0000", BorderStyleValues.Medium);
writer.DrawBorder("E5", "C3", "0000FF", BorderStyleValues.Medium);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\borderoverlap.xlsx", GetOutputFolder()), stream);
}
public void WorksheetColumnWidthTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.FindColumn("A").Width = 20;
writer.FindColumn(2).Width = 20;
writer.SetColumnWidth("C", 20);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\columnwidths.xlsx", GetOutputFolder()), stream);
}
public void InsertSheetTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
var worksheetPart = SpreadsheetWriter.InsertWorksheet(doc);
Assert.IsNotNull(worksheetPart, "A worksheet part was not returned.");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteText("B2", "Hello World");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\createsheet.xlsx", GetOutputFolder()), stream);
}
/// <summary>
/// Writes the items.
/// </summary>
/// <param name="item">The item.</param>
/// <param name="depth">The depth.</param>
private void WriteItems(IItem item, int depth)
{
List <string> row = new List <string> {
Convert.ToString(depth), item.GetType().Name, string.Empty
};
for (int i = 1; i < depth; i++)
{
row.Add(string.Empty);
}
row.Add(item.Name);
WorksheetWriter.WriteRow(row);
foreach (IItem child in item.GetItems())
{
WriteItems(child, depth + 1);
}
}
public bool SaveToExcel(string FileName)
{
try {
using (ExcelFileWriter file = ExcelFileWriter.OpenExcelApplication()) {
if (file == null)
{
throw new NullReferenceException("Failed to open the Excel application.");
}
using (WorkbookWriter book = file.CreateNewWorkbook()) {
if (book == null)
{
throw new NullReferenceException("Failed to create a new Excel workbook.");
}
WorksheetWriter sheet = book.GetActiveWorksheet();
if (sheet == null)
{
throw new NullReferenceException("Failed to find the active worksheet.");
}
sheet.Name = "Data Acquisition";
int col = WorksheetWriter.MinColumn;
int row;
foreach (string cat in orderedCategories)
{
row = WorksheetWriter.MinRow;
sheet[row, col] = cat;
sheet.Bold(row, col);
foreach (object data in allData[cat])
{
sheet[++row, col] = data.ToString();
}
col++;
}
sheet.AutoFitAllColumns();
return(book.Save(FileName + WorkbookWriter.DEFAULT_FILE_EXTENSION));
}
}
} catch (Exception) {
Console.WriteLine("Unable to save data.");
return(false);
}
}
public void WorksheetPrintAreaTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteText("A1", "Set print area to A1:B9");
//Test setting the print area
DefinedName area = writer.SetPrintArea("Sheet1", "A1", "B9");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\printarea.xlsx", GetOutputFolder()), stream);
//Assert.IsTrue(area != null, "Print area reference not returned.");
}
public void WorksheetAddAlignmentTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
style.IsWrapped = true;
writer.PasteText("E5", "Wrapped text", style);
style.IsWrapped = false;
style.SetHorizontalAlignment(HorizontalAlignmentValues.Center);
writer.PasteText("E7", "Aligned Test", style);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\wrapped.xlsx", GetOutputFolder()), stream);
}
public void WorksheetPasteTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteNumber("D3", "2");
writer.PasteNumber("D4", "3");
writer.PasteNumber("D5", "4");
//Add total without a calc chain
writer.FindCell("D6").CellFormula = new CellFormula("SUM(D3:D5)");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\output.xlsx", GetOutputFolder()), stream);
}
public void WorksheetDeleteRowsTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteText("D3", "Row 3");
writer.PasteText("D4", "Row 4");
writer.PasteText("D5", "Row 5");
//Delete row 3
writer.DeleteRows(3, 2);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\deleterows.xlsx", GetOutputFolder()), stream);
}
private static void WriteRowsFromHeaders(System.Data.DataTable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
maxWidth = 0;
for (int i = 0; i < rowData.Rows.Count; i++)
{
for (int j = 0; j < headerData.Count(); j++)
{
int colNum = 0;
string getValue = rowData.Rows[i][headerData[j]] != null ? rowData.Rows[i][headerData[j]].ToString() : "";
string strValue = getValue;
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
//foreach (object row in rowData)
//{
// int colNum = 0;
// foreach (string header in headerData)
// {
// string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
// strValue = ReplaceSpecialCharacters(strValue);
// maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
// string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
// ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
// colNum++;
// }
// rowNum++;
//}
}
public void WorksheetBorderTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
//Fill a background cell to make sure it is not overwritten
SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc);
style.SetBackgroundColor("C0C0C0");
//(grey)
writer.SetStyle(style, "B2");
writer.DrawBorder("B2", "D4", "FF0000", BorderStyleValues.Medium);
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\border.xlsx", GetOutputFolder()), stream);
}
public void WorksheetFindCellTest()
{
MemoryStream stream = SpreadsheetReader.Create();
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
//Test that the findcell function can accurately add cells past z
writer.PasteText("A1", "A1");
writer.PasteText("Z1", "Z1");
writer.PasteText("Z2", "Z2");
writer.PasteText("AA1", "AA1");
writer.PasteText("AA2", "AA2");
writer.PasteText("BA1", "BA1");
writer.PasteText("AA9", "AA9");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\findcell.xlsx", GetOutputFolder()), stream);
}
private void DeleteRows(SpreadsheetDocument RecultDoc, WorksheetWriter RecultWriter, uint rowIndex, uint count)
{
RecultWriter.DeleteRows(rowIndex, count);
if (RecultWriter.Worksheet.Worksheet.Elements <MergeCells>().Count() > 0)
{
MergeCells mergeCells = RecultWriter.Worksheet.Worksheet.Elements <MergeCells>().First();
//Удаление обединенных ячеек попавших в облать удаления
for (int i = mergeCells.Elements <MergeCell>().Count() - 1; i >= 0; i--)
{
string Range = mergeCells.Elements <MergeCell>().ElementAt(i).Reference.Value;
UInt32 startRow = SpreadsheetReader.RowFromReference(FirstRefFromRange(Range));
UInt32 endRow = SpreadsheetReader.RowFromReference(LastRefFromRange(Range));
if (startRow >= rowIndex && endRow <= (rowIndex + count - 1))
{
mergeCells.Elements <MergeCell>().ElementAt(i).Remove();
}
}
//Обновления ссылок всех ниже стоящих обединенных ячеек
foreach (MergeCell mCell in mergeCells.Elements <MergeCell>())
{
string Range = mCell.Reference.Value;
string StartRef = FirstRefFromRange(Range);
string EndRef = LastRefFromRange(Range);
UInt32 startRow = SpreadsheetReader.RowFromReference(StartRef);
UInt32 endRow = SpreadsheetReader.RowFromReference(EndRef);
if (startRow > (rowIndex + count - 1))
{
string newRangeFirst = SpreadsheetReader.ColumnFromReference(StartRef) + (SpreadsheetReader.RowFromReference(StartRef) - count);
string newRangeEnd = SpreadsheetReader.ColumnFromReference(EndRef) + (SpreadsheetReader.RowFromReference(EndRef) - count);
mCell.Reference.Value = newRangeFirst + ":" + newRangeEnd;
}
}
}
}
public void WorksheetCopyTest()
{
MemoryStream stream = SpreadsheetReader.Copy(string.Format("{0}\\Templates\\template.xlsx", Directory.GetCurrentDirectory()));
SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);
writer.PasteNumber("B3", "10");
writer.PasteNumber("B4", "20");
writer.PasteNumber("B5", "40");
//Add total without a calc chain
writer.FindCell("B6").CellFormula = new CellFormula("SUM(B3:B5)");
//Change the print area from A1:I30
writer.SetPrintArea("Sheet1", "A1", "D10");
//Save to the memory stream, and then to a file
SpreadsheetWriter.Save(doc);
SpreadsheetWriter.StreamToFile(string.Format("{0}\\templatetest.xlsx", GetOutputFolder()), stream);
}
/// <summary>
/// Write values to the spreadsheet.
/// </summary>
/// <param name="cellLocation">Row Column Value.</param>
/// <param name="strValue">Value to write.</param>
/// <param name="spreadSheet">Spreadsheet to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
int intValue = 0;
DateTime dateValue = new DateTime();
if (strValue.Contains("$"))
{
strValue = strValue.Replace("$", "");
strValue = strValue.Replace(",", "");
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (int.TryParse(strValue, out intValue))
{
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (string.IsNullOrEmpty(strValue))
{
workSheetWriter.PasteText(cellLocation, strValue);
}
//else if (DateTime.TryParse(strValue,out dateValue))
//{
// workSheetWriter.PasteDate(cellLocation, dateValue);
//}
else
{
workSheetWriter.PasteText(cellLocation, strValue,SpreadsheetStyle.GetDefault(spreadSheet));
}
}
private static void WriteRowsFromHeaders(System.Data.DataTable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
maxWidth = 0;
for (int i = 0; i < rowData.Rows.Count; i++)
{
for (int j = 0; j < headerData.Count(); j++)
{
int colNum = 0;
string getValue = rowData.Rows[i][headerData[j]] != null ? rowData.Rows[i][headerData[j]].ToString() : "";
string strValue = getValue;
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
//foreach (object row in rowData)
//{
// int colNum = 0;
// foreach (string header in headerData)
// {
// string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
// strValue = ReplaceSpecialCharacters(strValue);
// maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
// string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
// ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
// colNum++;
// }
// rowNum++;
//}
}
/// <summary>
/// Write the values for the rows from headers.
/// </summary>
/// <param name="rowData">Excel row values.</param>
/// <param name="headerData">Excel header values.</param>
/// <param name="rowNum">Row number.</param>
/// <param name="maxWidth">Max width.</param>
/// <param name="spreadSheet">Spreadsheet to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteRowsFromHeaders(IQueryable rowData, string[] headerData, int rowNum, out int maxWidth, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
maxWidth = 0;
foreach (object row in rowData)
{
int colNum = 0;
foreach (string header in headerData)
{
string strValue = row.GetType().GetProperty(header).GetValue(row, null).ToString();
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}", GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
}
private void Create(string fn)
{
_stream = SpreadsheetReader.Create();
_doc = SpreadsheetDocument.Open(_stream, true);
wsPart = SpreadsheetReader.GetWorksheetPartByName(_doc, wsNames[0]);
_writer = new WorksheetWriter(_doc, wsPart);
SpreadsheetWriter.Save(_doc);
}
/// <summary>
/// Write values to the spreadsheet.
/// </summary>
/// <param name="cellLocation">Row Column Value.</param>
/// <param name="strValue">Value to write.</param>
/// <param name="spreadSheet">Spreadsheet to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteValues(string cellLocation, string strValue, SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter = new WorksheetWriter(spreadSheet, workSheet);
int intValue = 0;
if (strValue.Contains("$"))
{
strValue = strValue.Replace("$", "");
strValue = strValue.Replace(",", "");
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (int.TryParse(strValue, out intValue))
{
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (string.IsNullOrEmpty(strValue))
{
workSheetWriter.PasteText(cellLocation, strValue);
}
else
{
workSheetWriter.PasteText(cellLocation, strValue);
}
}