/// <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); } }
/// <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)); } }
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); }
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 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); }
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); }
/// <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 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); }
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 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); }
public void WorksheetInsertTest() { 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)"); //Insert a row writer.InsertRow(6); writer.PasteText("H6", "New content test"); //Save to the memory stream, and then to a file SpreadsheetWriter.Save(doc); SpreadsheetWriter.StreamToFile(string.Format("{0}\\insertrow.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 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); } }