public void EndWritingWithoutTemplate() { _writer.WriteEndElement(); _writer.WriteEndElement(); _writer.Close(); }
private static void Finish(OpenXmlWriter writer, SpreadsheetDocument xl, WorksheetPart worksheetpart) { // end worksheet writer.WriteEndElement(); writer.Close(); // write root element writer = OpenXmlWriter.Create(xl.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet { Name = "Sheet1", SheetId = 1, Id = xl.WorkbookPart.GetIdOfPart(worksheetpart) }); // end Sheets writer.WriteEndElement(); // end Workbook writer.WriteEndElement(); writer.Close(); xl.Close(); }
/// <summary> /// 共享字符串 /// </summary> /// <param name="sharedStringTablePart"></param> private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart) { using (OpenXmlWriter writer = OpenXmlWriter.Create(sharedStringTablePart)) { // SharedStringTable 开始 writer.WriteStartElement(new SharedStringTable() { Count = totalCount, UniqueCount = (uint)stringIndexDic.Keys.Count }); foreach (var str in stringIndexDic.Keys) { // SharedStringItem 开始 writer.WriteStartElement(new SharedStringItem()); // 写入 Text writer.WriteElement(new Text(str)); // SharedStringItem 结束 writer.WriteEndElement(); } // SharedStringTable 结束 writer.WriteEndElement(); writer.Close(); } }
public void Dispose() { rowsWriter?.Dispose(); CreateAutoFilterDefinedName(); sheetWriter?.Close(); document?.Dispose(); }
public void CreateSheets(string filename, string sheetname, int sheetid) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; if (sheetid == 1) { workbookPart.Workbook = new Workbook(); } Workbook workbook = myDoc.WorkbookPart.Workbook; //WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); OpenXmlWriter writer = OpenXmlWriter.Create(workbookPart); writer.WriteStartElement(workbook); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet() { Name = sheetname, SheetId = (uint)sheetid }); // this is for Sheets writer.WriteEndElement(); // this is for Workbook writer.WriteEndElement(); writer.Close(); myDoc.Close(); } }
/// <summary> /// 通过SAX创建工作表 /// </summary> /// <param name="worksheetPart"></param> private void CreateSheetBySAX(WorksheetPart worksheetPart, DrawingsPart drawingsPart) { using (_writer = OpenXmlWriter.Create(worksheetPart)) { _rowIndex = 1; //S: Worksheet _writer.WriteStartElement(new Worksheet()); if (drawingsPart != null) { _writer.WriteElement(new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) }); } InitSheetViews(); InitColumns(); MergeCells(); FillData(); //E: Worksheet _writer.WriteEndElement(); _writer.Close(); } }
static void WriteRandomValuesSAX(string filename, int numRows, int numCols) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last(); OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart); Row r = new Row(); Cell c = new Cell(); CellValue v = new CellValue("Test"); c.AppendChild(v); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); for (int row = 0; row < numRows; row++) { writer.WriteStartElement(r); for (int col = 0; col < numCols; col++) { writer.WriteElement(c); } writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); } }
public void Write(Sheet sheet) { if (sheet == null) { throw new ArgumentNullException(nameof(sheet)); } writer.WriteStartElement(sheetTemplate); if (sheet.Columns?.Any() ?? false) { Write(sheet.Columns); } if (sheet.Rows?.Any() ?? false) { Write(sheet.Rows); } if (sheet.MergeCells?.Any() ?? false) { Write(sheet.MergeCells); } writer.WriteEndElement(); writer.Close(); }
public void Dispose() { Writer.WriteEndElement(); //end of SheetData Writer.WriteEndElement(); //end of worksheet Writer.Close(); // TODO: poner en una clase sheet CreateShareStringPart(); Writer.Dispose(); }
public void EndWriteHead() { _writer.WriteEndElement(); //end of SheetData _writer.WriteEndElement(); //end of worksheet _writer.Close(); _spreadsheet.Dispose(); }
public void WriteRandomValuesSAX(string filename, int numRows, int numCols, int sheetid, int countRows) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last(); OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart); Row r = new Row(); Cell c = new Cell(); CellValue v = new CellValue("Test"); c.AppendChild(v); var s = new SheetData(); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(s); //writer.WriteStartElement(new Sheet()); for (int row = countRows; row < numRows; row++) { writer.WriteStartElement(r); for (int col = 0; col < numCols; col++) { writer.WriteElement(c); } writer.WriteEndElement(); } //writer.WriteEndElement(); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); //writer = OpenXmlWriter.Create(myDoc.WorkbookPart); //writer.WriteStartElement(new Workbook()); //writer.WriteStartElement(new Sheets()); //writer.WriteElement(new Sheet() //{ // Name = $"Sheet21{sheetid}", // SheetId = (uint)sheetid, // Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart) //}); //// this is for Sheets //writer.WriteEndElement(); //// this is for Workbook //writer.WriteEndElement(); //writer.Close(); myDoc.Close(); } }
private void FinishBuilding() { _writer.WriteEndElement(); // end SheetData if (_excelImages.Count > 0) { _writer.WriteElement(GetDrawing()); } _writer.WriteEndElement(); // end Worksheet _writer.Close(); _buildingIsFinished = true; }
public void Close() { _workBookWriter.WriteEndElement(); // End Writing Sheets _workBookWriter.WriteEndElement(); // End Writing Workbook _workBookWriter.Close(); _sharedStringWriter.Close(); _styleSheetWriter.WriteAndClose(); _xl.Close(); }
public void WriteAndClose() { _writer.WriteStartElement(new Stylesheet()); { WriteNumberingFormats(); WriteFonts(); WriteFills(); WriteBorders(); WriteCellFormats(); } _writer.WriteEndElement(); _writer.Close(); }
private static void WriteData(WorksheetPart workSheetPart) { OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart); { writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); // Write Values for (int row = 0; row < 2000; row++) { Row r = new Row() { RowIndex = Convert.ToUInt32(row + 2) }; writer.WriteStartElement(r); for (int col = 0; col < 20; col++) { Cell c = new Cell() { StyleIndex = Convert.ToUInt32(2), CellReference = new StringValue() }; CellValue v = new CellValue("xxx"); c.DataType = new EnumValue <CellValues>(CellValues.String); c.Append(v); writer.WriteElement(c); } writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); } }
public int writeHeader(List <ChannelModel> channels, OperatorModel operatorParam, FacilityModel facilityParam) { int retvalueTemp = 0; if (_doc != null && _workBook != null && _workSheet != null) { string originalPartId = _workBook.GetIdOfPart(_workSheet); WorksheetPart replacementPart = _workBook.AddNewPart <WorksheetPart>(); string replacementPartId = _workBook.GetIdOfPart(replacementPart); _reader = OpenXmlReader.Create(_workSheet); _writer = OpenXmlWriter.Create(replacementPart); while (_reader.Read()) { if (_reader.ElementType == typeof(Selection)) { continue; } if (_reader.ElementType == typeof(SheetData)) { if (_reader.IsEndElement) { continue; } _writer.WriteStartElement(new SheetData()); // beginning of sheetdata // append section begins var labelRow = new Row(); // label row labelRow.RowIndex = 1; _writer.WriteStartElement(labelRow); // begining of row var operatorLabelCell = new Cell(); var facilityLabelCell = new Cell(); var creationDateLabelCell = new Cell(); operatorLabelCell.CellValue = new CellValue(" Operatör "); facilityLabelCell.CellValue = new CellValue(" Tesis "); creationDateLabelCell.CellValue = new CellValue(" Oluşturulma Tarihi "); _writer.WriteElement(operatorLabelCell); _writer.WriteElement(facilityLabelCell); _writer.WriteElement(creationDateLabelCell); _writer.WriteEndElement(); // end of label row var infoRow = new Row(); // info row infoRow.RowIndex = 2; _writer.WriteStartElement(infoRow); // begining of row var operatorInfoCell = new Cell(); var facilityInfoCell = new Cell(); var creationDateInfoCell = new Cell(); operatorInfoCell.CellValue = new CellValue(operatorParam.DisplayName); facilityInfoCell.CellValue = new CellValue(facilityParam.ListDisplay); creationDateInfoCell.CellValue = new CellValue(DateTime.Now.ToShortTimeString()); _writer.WriteElement(operatorInfoCell); _writer.WriteElement(facilityInfoCell); _writer.WriteElement(creationDateInfoCell); _writer.WriteEndElement(); // end of info row var channelListRow = new Row(); // channelListRow row channelListRow.RowIndex = 3; _writer.WriteStartElement(channelListRow); // begining of row for (int k = 0; k < channels.Count; k++) { var dataInfoCell = new Cell(); dataInfoCell.CellValue = new CellValue(channels[k].name); _writer.WriteElement(dataInfoCell); } _writer.WriteEndElement(); // end of channel info row _writer.WriteEndElement(); // end of sheetdata } else { if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); if (_reader.ElementType == typeof(CellValue)) { _writer.WriteString(_reader.GetText()); } } else if (_reader.IsEndElement) { _writer.WriteEndElement(); } else { } } } _writer.Close(); _reader.Close(); Sheet sheet = _workBook.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(originalPartId)).First(); sheet.Id.Value = replacementPartId; _workBook.DeletePart(_workSheet); } else { retvalueTemp = -1; } return(retvalueTemp); }
public static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII); writer.WriteStartElement(new Worksheet()); UInt32 inx = 1; writer.WriteStartElement(new Columns()); foreach (DataColumn dc in dt.Columns) { writer.WriteElement(new Column { Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH }); inx++; } writer.WriteEndElement(); writer.WriteStartElement(new SheetData()); string cellValue = ""; string cellReference = ""; int numberOfColumns = dt.Columns.Count; bool[] IsIntegerColumn = new bool[numberOfColumns]; bool[] IsFloatColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex, Height = 20, CustomHeight = true }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.Columns[colInx]; writer.AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName); IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int")); IsFloatColumn[colInx] = (col.DataType.FullName == typeof(decimal).FullName) || (col.DataType.FullName == typeof(double).FullName) || (col.DataType.FullName == typeof(Single).FullName); IsDateColumn[colInx] = (col.DataType.FullName == typeof(DateTime).FullName); } writer.WriteEndElement(); // End of header "Row" double cellFloatValue = 0; CultureInfo ci = Thread.CurrentThread.CurrentCulture; //new CultureInfo("en-US"); foreach (DataRow dr in dt.Rows) { ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); cellValue = ReplaceHexadecimalSymbols(cellValue); cellReference = excelColumnNames[colInx] + rowIndex.ToString(); if (IsIntegerColumn[colInx] || IsFloatColumn[colInx]) { cellFloatValue = 0; bool bIncludeDecimalPlaces = IsFloatColumn[colInx]; if (double.TryParse(cellValue, out cellFloatValue)) { cellValue = cellFloatValue.ToString(ci); writer.AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces ? NumericFormatDecimaPlaces.Two : NumericFormatDecimaPlaces.Zero); } } else if (IsDateColumn[colInx]) { DateTime dateValue; if (DateTime.TryParse(cellValue, out dateValue)) { writer.AppendDateCell(cellReference, dateValue); } else { writer.AppendTextCell(cellReference, cellValue); } } else { writer.AppendTextCell(cellReference, cellValue); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }
public ActionResult Search(FormCollection model) { string selectedReport = model["cboReports"]; string datePeriod = model["DatePeriod"]; if (selectedReport.HasValue()) { Guid idReport = new Guid(selectedReport); Reports report = Reports.Get(idReport); IEnumerable <dynamic> data = Reports.Run(report, datePeriod); HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true") { Path = "/" }); var context = HttpContext.Response; context.Buffer = context.BufferOutput = false; context.Cache.SetCacheability(HttpCacheability.Private); context.Cache.SetExpires(DateTime.Now); //context.ContentType = (new ContentType("text/csv") { CharSet = "utf-8" }).ToString(); // CSV //context.ContentType = (new ContentType("application/vnd.ms-excel") { CharSet = "utf-8" }).ToString(); context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { CharSet = "utf-8" }.ToString(); context.AppendHeader("Content-Disposition", new ContentDisposition { DispositionType = DispositionTypeNames.Attachment, FileName = string.Format(CultureInfo.InvariantCulture, report.FileNamePrefix + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now) }.ToString() ); context.AppendHeader("X-Content-Type-Options", "nosniff"); using (MemoryStream mDocument = new MemoryStream()) { // Using SAX using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook)) { List <OpenXmlAttribute> attributes; document.AddWorkbookPart(); // Stylesheet WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>(); stylesheet.Stylesheet = new Stylesheet(new Fonts( new Font( // 0 = Default new Color() { Rgb = new HexBinaryValue() { Value = "000000" } } ), new Font( // 1 = Bold new Bold() ), new Font( // 2 = Red new Color() { Rgb = new HexBinaryValue() { Value = "FF0000" } } ) ), new Fills( new Fill() { } ), new Borders(new Border() { }), new CellFormats( new CellFormat() { FontId = 0 }, // 0 new CellFormat() { FontId = 1, ApplyFont = true }, // 1 new CellFormat() { FontId = 2, ApplyFont = true } // 2 ) ); stylesheet.Stylesheet.Save(); WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); IDictionary <string, object> firstRow = data.FirstOrDefault(); if (firstRow != null) { int row = 1; attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col1 = 1; foreach (var cols in firstRow.Keys.ToList()) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(col1) + row), new OpenXmlAttribute("s", "", "1") // Bold (Style 1) }; writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(cols)); writer.WriteEndElement(); col1++; } writer.WriteEndElement(); row++; foreach (IDictionary <string, object> row2 in data) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col = 1; foreach (var key in row2.Keys) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(col) + row) }; if (row2[key] is decimal) { if ((decimal)row2[key] < 0) { attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2) } } else if (row2[key] is double) { if ((double)row2[key] < 0) { attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2) } } writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : "")); writer.WriteEndElement(); col++; } writer.WriteEndElement(); row++; } } else { // Empty row (no data found) attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, "1") }; writer.WriteStartElement(new Row(), attributes); attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(1) + 1), new OpenXmlAttribute("s", "", "1") // Bold (Style 1) }; writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue("")); writer.WriteEndElement(); writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); writer = OpenXmlWriter.Create(document.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet() { Name = "Sheet 1", SheetId = 1, Id = document.WorkbookPart.GetIdOfPart(workSheetPart) }); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); document.Save(); document.Close(); mDocument.WriteTo(context.OutputStream); } } return(null); } return(null); }
public int write(List <List <IWritable> > dataList) { int retvalueTemp = 0; if (_doc != null && _workBook != null && _workSheet != null) { string originalPartId = _workBook.GetIdOfPart(_workSheet); WorksheetPart replacementPart = _workBook.AddNewPart <WorksheetPart>(); string replacementPartId = _workBook.GetIdOfPart(replacementPart); _reader = OpenXmlReader.Create(_workSheet); _writer = OpenXmlWriter.Create(replacementPart); while (_reader.Read()) { if (_reader.ElementType == typeof(Selection)) { continue; } if (_reader.ElementType == typeof(SheetData)) { if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); continue; } // append section begins //_writer.WriteStartElement(new SheetData()); // beginning of sheetdata for (int rowIndex = 0; rowIndex < dataList.Count; rowIndex++) { Row row = new Row(); _writer.WriteStartElement(row); // begining of row for (int colIndex = 0; colIndex < dataList.Count; colIndex++) { Cell cell = new Cell(); cell.CellValue = new CellValue(dataList[rowIndex][colIndex].ToString()); _writer.WriteElement(cell); } _writer.WriteEndElement(); // end of row } _writer.WriteEndElement(); // end of sheetdata } else { if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); if (_reader.ElementType == typeof(CellValue)) { _writer.WriteString(_reader.GetText()); } } else if (_reader.IsEndElement) { _writer.WriteEndElement(); } else { } } } _writer.Close(); _reader.Close(); Sheet sheet = _workBook.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(originalPartId)).First(); sheet.Id.Value = replacementPartId; _workBook.DeletePart(_workSheet); } else { retvalueTemp = -1; } return(retvalueTemp); }
public void WriteRandomValuesSAX(string filename, int numRows, int numCols) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart); WorksheetPart replacementPart = workbookPart.AddNewPart <WorksheetPart>(); string replacementPartId = workbookPart.GetIdOfPart(replacementPart); OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart); Row r = new Row(); Cell c = new Cell(); CellFormula f = new CellFormula(); f.CalculateCell = true; f.Text = "RAND()"; c.Append(f); CellValue v = new CellValue(); c.Append(v); while (reader.Read()) { if (reader.ElementType == typeof(SheetData)) { if (reader.IsEndElement) { continue; } writer.WriteStartElement(new SheetData()); for (int row = 0; row < numRows; row++) { writer.WriteStartElement(r); for (int col = 0; col < numCols; col++) { writer.WriteElement(c); } writer.WriteEndElement(); } writer.WriteEndElement(); } else { if (reader.IsStartElement) { writer.WriteStartElement(reader); } else if (reader.IsEndElement) { writer.WriteEndElement(); } } } reader.Close(); writer.Close(); Sheet sheet = workbookPart.Workbook.Descendants <Sheet>() .Where(s => s.Id.Value.Equals(origninalSheetId)).First(); sheet.Id.Value = replacementPartId; workbookPart.DeletePart(worksheetPart); } }
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); string cellValue = ""; int numberOfColumns = dt.Columns.Count; bool[] IsNumericColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.Columns[colInx]; AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer); IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single"); IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime"); } writer.WriteEndElement(); double cellNumericValue = 0; foreach (DataRow dr in dt.Rows) { ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); cellValue = ReplaceHexadecimalSymbols(cellValue); if (IsNumericColumn[colInx]) { cellNumericValue = 0; if (double.TryParse(cellValue, out cellNumericValue)) { cellValue = cellNumericValue.ToString(); AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } } else if (IsDateColumn[colInx]) { DateTime dtValue; string strValue = ""; if (DateTime.TryParse(cellValue, out dtValue)) { strValue = dtValue.ToShortDateString(); } AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer); } else { AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } } writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); }
/// <summary> /// 表格内容 /// </summary> /// <param name="worksheetPart"></param> private void GenerateWorksheetPartContent(WorksheetPart worksheetPart) { using (OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart)) { // worksheet 开始 writer.WriteStartElement(new Worksheet()); // 是否有自定义列 bool hasCustomColume = _columnInfos.Any(x => x.Width != null); if (hasCustomColume) { // cols 开始 writer.WriteStartElement(new Columns()); } int columnCount = _columnInfos.Count; // 表头 Row headRow = new Row() { RowIndex = 1, Spans = new ListValue <StringValue>() { InnerText = $"1:{columnCount}" } }; Cell[] headCells = new Cell[columnCount]; for (int i = 0; i < columnCount; i++) { ColumnInfo citem = _columnInfos[i]; uint cindex = (uint)i + 1; if (citem.Width != null) { var column = new Column() { Min = cindex, Max = cindex, Width = citem.Width.Value, BestFit = true, CustomWidth = true }; // col writer.WriteElement(column); } Cell cell = ConstructHeadCell(citem.Show); cell.CellReference = CellReferenceUtil.GetCellReference(0, (uint)i); headCells[i] = cell; } headRow.Append(headCells); if (hasCustomColume) { // cols 结束 writer.WriteEndElement(); } // sheetData 开始 writer.WriteStartElement(new SheetData()); // 写入表头行 writer.WriteElement(headRow); // 写入数据 uint rowIndex = 2; foreach (T data in _sourceDatas) { Row row = new Row() { RowIndex = rowIndex, Spans = new ListValue <StringValue>() { InnerText = $"1:{columnCount}" } }; // row 开始 writer.WriteStartElement(row); for (int k = 0; k < columnCount; k++) { ColumnInfo citem = _columnInfos[k]; PropertyInfo pi = _propertyDic[citem.PropertyName]; object v = pi.GetValue(data, null); Cell cell = ConstructCell(v, citem.FormatString, pi.PropertyType); cell.CellReference = CellReferenceUtil.GetCellReference(rowIndex - 1, (uint)k); // 写入 c writer.WriteElement(cell); } // row 结束 writer.WriteEndElement(); rowIndex++; } // sheetData 结束 writer.WriteEndElement(); // worksheet 结束 writer.WriteEndElement(); writer.Close(); } }
public void WriteEndSheet() { _workSheetWriter.WriteEndElement(); // End Writing SheetData _workSheetWriter.WriteEndElement(); // End Writing Worksheet _workSheetWriter.Close(); }
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII); writer.WriteStartElement(new Worksheet()); // To demonstrate how to set column-widths in Excel, here's how to set the width of all columns to our default of "25": UInt32 inx = 1; writer.WriteStartElement(new Columns()); foreach (DataColumn dc in dt.Columns) { writer.WriteElement(new Column { Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH }); inx++; } writer.WriteEndElement(); writer.WriteStartElement(new SheetData()); string cellValue = ""; string cellReference = ""; // Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable. // // We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual // cells of data, we'll know if to write Text values or Numeric cell values. int numberOfColumns = dt.Columns.Count; bool[] IsIntegerColumn = new bool[numberOfColumns]; bool[] IsFloatColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } // // Create the Header row in our Excel Worksheet // We'll set the row-height to 20px, and (using the "AppendHeaderTextCell" function) apply some formatting to the cells. // uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex, Height = 20, CustomHeight = true }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.Columns[colInx]; AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer); IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int")); IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single"); IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime"); // Uncomment the following lines, for an example of how to create some Named Ranges in your Excel file #if FALSE // For each column of data in this worksheet, let's create a Named Range, showing where there are values in this column // eg "NamedRange_UserID" = "Drivers!$A2:$A6" // "NamedRange_Surname" = "Drivers!$B2:$B6" string columnHeader = col.ColumnName.Replace(" ", "_"); string NamedRange = string.Format("{0}!${1}2:${2}{3}", worksheetName, excelColumnNames[colInx], excelColumnNames[colInx], dt.Rows.Count + 1); DefinedName definedName = new DefinedName() { Name = "NamedRange_" + columnHeader, Text = NamedRange }; definedNamesCol.Append(definedName); #endif } writer.WriteEndElement(); // End of header "Row" // // Now, step through each row of data in our DataTable... // double cellFloatValue = 0; CultureInfo ci = new CultureInfo("en-US"); foreach (DataRow dr in dt.Rows) { // ...create a new row, and append a set of this row's data to it. ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); cellValue = ReplaceHexadecimalSymbols(cellValue); cellReference = excelColumnNames[colInx] + rowIndex.ToString(); // Create cell with data if (IsIntegerColumn[colInx] || IsFloatColumn[colInx]) { // For numeric cells without any decimal places. // If this numeric value is NULL, then don't write anything to the Excel file. cellFloatValue = 0; bool bIncludeDecimalPlaces = IsFloatColumn[colInx]; if (double.TryParse(cellValue, out cellFloatValue)) { cellValue = cellFloatValue.ToString(ci); AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces, writer); } } else if (IsDateColumn[colInx]) { // For date values, we save the value to Excel as a number, but need to set the cell's style to format // it as either a date or a date-time. DateTime dateValue; if (DateTime.TryParse(cellValue, out dateValue)) { AppendDateCell(cellReference, dateValue, writer); } else { // This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank. AppendTextCell(cellReference, cellValue, writer); } } else { // For text cells, just write the input data straight out to the Excel file. AppendTextCell(cellReference, cellValue, writer); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }
public static Stream ToXLSX(TranslationModule project) { MemoryStream xlsStream = new MemoryStream(); var worksheet = new MemoryWorksheet(); Export.ToIWorksheet(project, worksheet); var ss = new Stylesheet(); ss.CellStyleFormats = new CellStyleFormats(); ss.CellStyleFormats.Count = 1; ss.CellStyles = new CellStyles(); ss.CellStyles.Count = 1; ss.DifferentialFormats = new DifferentialFormats(); ss.DifferentialFormats.Count = 0; ss.TableStyles = new TableStyles(); ss.TableStyles.Count = 0; using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(xlsStream, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = myDoc.AddWorkbookPart(); //workbookPart.Workbook = new Workbook(); WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = ss; stylesPart.Stylesheet.Save(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); //SheetData sheetData = new SheetData(); //worksheetPart.Worksheet = new Worksheet(sheetData); /* * Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets()); * Sheet sheet = new Sheet() * { * Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart), * SheetId = 1, * Name = "mySheet" * }; * sheets.Append(sheet); */ OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); for (int row = 0; row < worksheet.Rows; row++) { var oxa = new List <OpenXmlAttribute>(); // this is the row index oxa.Add(new OpenXmlAttribute("r", null, row.ToString())); writer.WriteStartElement(new Row(), oxa); for (int col = 0; col < worksheet.Columns; col++) { oxa = new List <OpenXmlAttribute>(); // this is the data type ("t"), with CellValues.String ("str") oxa.Add(new OpenXmlAttribute("t", null, "str")); string val = worksheet[row, col] != null ? worksheet[row, col].ToString() : ""; //var cell = new Cell(new CellValue(val)); writer.WriteStartElement(new Cell(), oxa); //Cell c = new Cell(); CellValue v = new CellValue(val); writer.WriteElement(v); //c.AppendChild(v); //writer.WriteElement(); writer.WriteEndElement(); } writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); writer = OpenXmlWriter.Create(myDoc.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); // you can use object initialisers like this only when the properties // are actual properties. SDK classes sometimes have property-like properties // but are actually classes. For example, the Cell class has the CellValue // "property" but is actually a child class internally. // If the properties correspond to actual XML attributes, then you're fine. writer.WriteElement(new Sheet() { Name = "Sheet1", SheetId = 1, Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart) }); // this is for Sheets writer.WriteEndElement(); // this is for Workbook writer.WriteEndElement(); writer.Close(); myDoc.Close(); } return(xlsStream); }
public virtual void Export(string fileName, LayoutList list) { this.list = list; using (SpreadsheetDocument xl = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookpart = xl.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); //add styles WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>(); wbsp.Stylesheet = CreateStylesheet(); wbsp.Stylesheet.Save(); // Add a WorksheetPart to the WorkbookPart. var worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); // Add a SharedStringTablePart to the WorkbookPart. var stringPart = workbookpart.AddNewPart <SharedStringTablePart>(); var stringTable = new StringKeyList(); // Add Sheets to the Workbook. var sheets = xl.WorkbookPart.Workbook.AppendChild(new Sheets()); // Append a new worksheet and associate it with the workbook. var sheet = new Sheet() { Id = xl.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "DataSheet" }; sheets.Append(sheet); workbookpart.Workbook.Save(); mcells = new List <MergeCell>(); writer = OpenXmlWriter.Create(worksheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetProperties()); writer.WriteElement(new OutlineProperties() { SummaryBelow = false, SummaryRight = false }); writer.WriteEndElement(); mc = 0; writer.WriteStartElement(new Columns()); WriteMapColumns(list.ListInfo.Columns, 0, 0); writer.WriteEndElement(); writer.WriteStartElement(new SheetData()); int ind = 1; var row = new Row() { RowIndex = (uint)ind, Height = 25 }; row.AppendChild(GetCell(list.Description, 0, ind, (uint)13, stringTable)); WriteRows(writer, new List <Row>(new Row[] { row })); mcells.Add(new MergeCell() { Reference = new CellRange(0, 1, mc - 1, 1).ToString() }); WriteMapItem(list.ListInfo.Columns, -1, null, 0, 0, ref ind, stringTable); if (list.Selection.Count > 1) { var items = list.Selection.GetItems <object>(); for (var i = 0; i < items.Count; i++) { var item = items[i]; WriteMapItem(list.ListInfo.Columns, i, item, 0, 0, ref ind, stringTable); } } else if (list.NodeInfo != null) { var items = list.NodeInfo.Nodes.GetTopLevel().ToList(); for (var i = 0; i < items.Count; i++) { var item = items[i] as Node; WriteMapItem(list.ListInfo.Columns, i, item, 0, 0, ref ind, stringTable); } } else if (list.ListInfo.GroupVisible) { foreach (LayoutGroup g in list.Groups) { this.group = g; if (list.ListInfo.GroupHeader) { ind++; var header = new Row() { RowIndex = (uint)ind, CustomHeight = true, Height = 20 }; header.AppendChild(GetCell(g.TextValue, 0, ind, 8, stringTable)); mcells.Add(new MergeCell() { Reference = new CellRange(0, ind, mc - 1, ind).ToString() }); WriteRow(writer, header); } for (int i = g.IndexStart; i <= g.IndexEnd; i++) { WriteMapItem(list.ListInfo.Columns, i, list.ListSource[i], 0, 0, ref ind, stringTable); } if (list.ListInfo.CollectingRow) { WriteMapItem(list.ListInfo.Columns, -2, null, 0, 0, ref ind, stringTable); } //ind++; } } else { for (int i = 0; i < list.ListSource.Count; i++) { WriteMapItem(list.ListInfo.Columns, i, list.ListSource[i], 0, 0, ref ind, stringTable); } if (list.ListInfo.CollectingRow) { WriteMapItem(list.ListInfo.Columns, -2, null, 0, 0, ref ind, stringTable); } } writer.WriteEndElement(); if (mcells.Count > 0) { writer.WriteStartElement(new MergeCells()); foreach (var cell in mcells) { writer.WriteElement(cell); } writer.WriteEndElement(); } writer.WriteEndElement(); writer.Close(); } }
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); string cellValue = ""; // Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable. // // We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual // cells of data, we'll know if to write Text values or Numeric cell values. int numberOfColumns = dt.Columns.Count; bool[] IsNumericColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } // // Create the Header row in our Excel Worksheet // uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.Columns[colInx]; AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer); IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single"); IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime"); } writer.WriteEndElement(); // End of header "Row" // // Now, step through each row of data in our DataTable... // double cellNumericValue = 0; foreach (DataRow dr in dt.Rows) { // ...create a new row, and append a set of this row's data to it. ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); cellValue = ReplaceHexadecimalSymbols(cellValue); // Create cell with data if (IsNumericColumn[colInx]) { // For numeric cells, make sure our input data IS a number, then write it out to the Excel file. // If this numeric value is NULL, then don't write anything to the Excel file. cellNumericValue = 0; if (double.TryParse(cellValue, out cellNumericValue)) { cellValue = cellNumericValue.ToString(); AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } } else if (IsDateColumn[colInx]) { // This is a date value. DateTime dtValue; string strValue = ""; if (DateTime.TryParse(cellValue, out dtValue)) { strValue = dtValue.ToShortDateString(); } AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer); } else { // For text cells, just write the input data straight out to the Excel file. AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }
private static void WriteDataTableToExcelWorksheet(ReportCondition dtCondition, DataTable dtDetail, WorksheetPart worksheetPart, WorkbookStylesPart stylesPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.Unicode); //ASCII writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); string cellValue = string.Empty; uint rowIndexHeader = 1; // Create the Conditon Report writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "BOC Detailed Utilization", 1, stylesPart.Stylesheet, System.Drawing.Color.White, false, 20, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Date:", 2, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat( "B" , string.Format("{0} - {1}", dtCondition.FromDate.ToString("dd.MM.yyyy"), dtCondition.ToDate.AddDays(-1).ToString("dd.MM.yyyy")) , 2, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Location:", 3, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.LocationPath.ToString(), 3, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Group:", 4, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.GroupName.ToString(), 4, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Resource:", 5, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.ResourceName.ToString(), 5, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Method:", 6, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.Method.ToString(), 6, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Include weekends:", 7, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", SetWeekendTitle(dtCondition), 7, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Upper threshold (%):", 8, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.UpperThreshold.ToString(), 8, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Lower threshold (%):", 9, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.LowerThreshold.ToString(), 9, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); // End of Conditon Report // Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable. int numberOfColumns = dtDetail.Columns.Count; bool[] IsNumericColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } // Create the Header row in our Excel Worksheet writer.WriteStartElement(new Row { RowIndex = 11 }); AppendTextCellFormat("A", "Resource", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer); AppendTextCellFormat("B", "Hours Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer); AppendTextCellFormat("C", "% Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer); AppendTextCellFormat("D", "Flag", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); AppendTextCellFormat("E", "Group", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); AppendTextCellFormat("F", "Hours Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); AppendTextCellFormat("G", "% Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dtDetail.Columns[colInx]; //AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer); IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single"); IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime"); } writer.WriteEndElement(); // End of header row // Now, step through each row of data in our DataTable... uint rowIndex = 11; double cellNumericValue = 0; foreach (DataRow dr in dtDetail.Rows) { // ...create a new row, and append a set of this row's data to it. ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); // Create cell with data if (IsNumericColumn[colInx]) { cellNumericValue = 0; if (double.TryParse(cellValue, out cellNumericValue)) { if (cellNumericValue >= 0) { cellValue = ((float)cellNumericValue).ToString("0.00"); AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } else { cellValue = string.Empty; //AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, false, 9, ref writer); } } } else if (IsDateColumn[colInx]) { DateTime dtValue = DateTime.Parse(cellValue); string strValue = dtValue.ToShortDateString(); AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer); } else if (colInx == 0) { AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, false, 9, ref writer); } else { //AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }
public void ExportSAX(DataTable dt, string excelFilePath) { if (dt == null || excelFilePath == string.Empty) { throw new ArgumentNullException(); } var fi = new FileInfo(excelFilePath); #region 文件已存在 if (File.Exists(excelFilePath) && fi.Length != 0) { using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFilePath, true)) { Sheet sheet = document.WorkbookPart.Workbook.Descendants <Sheet>() .FirstOrDefault(s => s.Name == EscapeSheetNameInvalidChar(dt.TableName)); if (sheet != null) { var worksheetPart = (WorksheetPart)(document.WorkbookPart.GetPartById(sheet.Id)); sheet.Remove(); document.WorkbookPart.DeletePart(worksheetPart); document.WorkbookPart.Workbook.Save(); } uint sheetId = 1; WorksheetPart wsp = document.WorkbookPart.AddNewPart <WorksheetPart>(); Sheets sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>(); if (sheets == null) { throw new OpenXmlPackageException("已有Excel文件损坏,请删除"); } if (sheets.Elements <Sheet>().Any()) { sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1; } //create worksheet part, and add it to the sheets collection in workbook Sheet sheet1new = new Sheet { Id = document.WorkbookPart.GetIdOfPart(wsp), SheetId = sheetId, Name = EscapeSheetNameInvalidChar(dt.TableName) }; sheets.Append(sheet1new); // List<OpenXmlAttribute> oxa; OpenXmlWriter writer = OpenXmlWriter.Create(wsp); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); ///TODO:auto row page for (int i = 0; i < dt.Rows.Count; ++i) { // oxa = new List<OpenXmlAttribute>(); // oxa.Add(new OpenXmlAttribute("r", null, (i + 1).ToString(CultureInfo.InvariantCulture))); writer.WriteStartElement(new Row()); for (int j = 0; j < dt.Columns.Count; ++j) { //if use WriteStartElement must manual add OpenXmlAttribute // oxa = new List<OpenXmlAttribute>(); // oxa.Add(new OpenXmlAttribute("t", null, "str")); writer.WriteElement(new Cell { CellValue = new CellValue(dt.Rows[i].ItemArray[j].ToString()), DataType = CellValues.String }); // writer.WriteStartElement(new Cell()); // writer.WriteEndElement(); } writer.WriteEndElement(); } // this is for SheetData writer.WriteEndElement(); // this is for Worksheet writer.WriteEndElement(); writer.Close(); } } #endregion #region 新建文件 else { using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilePath, SpreadsheetDocumentType.Workbook)) { document.AddWorkbookPart(); // List<OpenXmlAttribute> oxa; document.WorkbookPart.Workbook = new Workbook(); WorksheetPart wsp = document.WorkbookPart.AddNewPart <WorksheetPart>(); Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); //create worksheet part, and add it to the sheets collection in workbook Sheet sheet = new Sheet { Id = document.WorkbookPart.GetIdOfPart(wsp), SheetId = 1, Name = dt.TableName }; sheets.Append(sheet); OpenXmlWriter writer = OpenXmlWriter.Create(wsp); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); ///TODO:auto row page for (int i = 0; i < dt.Rows.Count; ++i) { // oxa = new List<OpenXmlAttribute>(); // oxa.Add(new OpenXmlAttribute("r", null, (i + 1).ToString(CultureInfo.InvariantCulture))); writer.WriteStartElement(new Row()); for (int j = 0; j < dt.Columns.Count; ++j) { //if use WriteStartElement must manual add OpenXmlAttribute // oxa = new List<OpenXmlAttribute>(); // oxa.Add(new OpenXmlAttribute("t", null, "str")); // you can use object initialisers like this only when the properties // are actual properties. SDK classes sometimes have property-like properties // but are actually classes. For example, the Cell class has the CellValue // "property" but is actually a child class internally. // If the properties correspond to actual XML attributes, then you're fine. writer.WriteElement(new Cell { CellValue = new CellValue(dt.Rows[i].ItemArray[j].ToString()), DataType = CellValues.String }); // writer.WriteStartElement(new Cell()); // writer.WriteEndElement(); } writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); } } #endregion }
public static void GetDataResultsAdjust(IList <RecordAdjust> lstRecord, string filePath, string templatePath, string status, string fromdate, string todate) { if ((!File.Exists(templatePath))) { //If we do not have a template file, we need to create one CreateSpreadsheetWorkbook(templatePath); } //Copy the template file to the output file location File.Copy(templatePath, filePath, true); //Open the copied file using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, true)) { //Navigate to the workbook part WorkbookPart workbookPart = myDoc.WorkbookPart; //open the first worksheet WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); //Get the id of this sheet. We need this because we are going to add a new //worksheet to this workbook, then we are going to delete this worksheet //This ID will tell us which one to delete string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart); //Add the new worksheet WorksheetPart replacementPart = workbookPart.AddNewPart <WorksheetPart>(); //This is the ID of the new worksheet string replacementPartId = workbookPart.GetIdOfPart(replacementPart); //We are going to read from the original worksheet to get the //templated items that we added to the worksheet in the traditional way OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); //We are goint to copy the items from the original worksheet by using //an XML writer, this overcomes the memory limitations of having //an extremely large dataset. OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart); //The template does not have any data so we will be creating new rows and cells //Then writing them using XML. Row r = new Row(); Cell c = new Cell(); while ((reader.Read())) { //This iterates through the sheet data and copies it if ((object.ReferenceEquals(reader.ElementType, typeof(SheetData)))) { //Exit the loop if we hit a sheetdata end element if ((reader.IsEndElement)) { break; // TODO: might not be correct. Was : Exit While } //We create a new sheetdata element (basically this is the reoot container for a sheet) writer.WriteStartElement(new SheetData()); if (status == "0") { int rowIndex2 = 2; r.RowIndex = (UInt32)rowIndex2; writer.WriteStartElement(r); c = CreateCell(5, "BÁO CÁO HÓA ĐƠN THAY THẾ", rowIndex2); writer.WriteElement(c); writer.WriteEndElement(); } else if (status == "1") { int rowIndex2 = 2; r.RowIndex = (UInt32)rowIndex2; writer.WriteStartElement(r); c = CreateCell(5, "BÁO CÁO HÓA ĐƠN ĐIỀU CHỈNH", rowIndex2); writer.WriteElement(c); writer.WriteEndElement(); } int rowIndextieudea = 3; r.RowIndex = (UInt32)rowIndextieudea; writer.WriteStartElement(r); c = CreateCell(0, "Từ ngày:" + fromdate, rowIndextieudea); writer.WriteElement(c); writer.WriteEndElement(); int rowIndextieudeb = 4; r.RowIndex = (UInt32)rowIndextieudeb; writer.WriteStartElement(r); c = CreateCell(0, "Đến ngày:" + todate, rowIndextieudeb); writer.WriteElement(c); writer.WriteEndElement(); int rowIndextieudec = 5; r.RowIndex = (UInt32)rowIndextieudec; writer.WriteStartElement(r); c = CreateCell(0, "Tổng số hóa đơn:" + lstRecord.Count(), rowIndextieudec); writer.WriteElement(c); writer.WriteEndElement(); int rowIndex = 6; r.RowIndex = (UInt32)rowIndex; //Start the first row. writer.WriteStartElement(r); //Iterate through the columns to write a row conaining the column names c = CreateCell(0, "STT", rowIndex); writer.WriteElement(c); c = CreateCell(1, "Ký hiệu mẫu", rowIndex); writer.WriteElement(c); c = CreateCell(2, "Ký hiệu hóa đơn", rowIndex); writer.WriteElement(c); c = CreateCell(3, "Số hóa đơn", rowIndex); writer.WriteElement(c); c = CreateCell(4, "Khách hàng", rowIndex); writer.WriteElement(c); c = CreateCell(5, "Ký hiệu mẫu", rowIndex); writer.WriteElement(c); c = CreateCell(6, "Ký hiệu hóa đơn", rowIndex); writer.WriteElement(c); c = CreateCell(7, "Số hóa đơn", rowIndex); writer.WriteElement(c); c = CreateCell(8, "Khách hàng", rowIndex); writer.WriteElement(c); c = CreateCell(9, "Người thực hiện", rowIndex); writer.WriteElement(c); c = CreateCell(10, "Ngày thực hiện", rowIndex); writer.WriteElement(c); c = CreateCell(11, "Trạng thái", rowIndex); writer.WriteElement(c); //End first row writer.WriteEndElement(); foreach (var item in lstRecord) { if (item == null) { continue; } rowIndex = rowIndex + 1; r.RowIndex = (UInt32)rowIndex; writer.WriteStartElement(r); //iterate through the columns to write their data c = CreateCell(0, item.stt.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(1, item.patternOlder.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(2, item.serialOlder.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(3, string.Format("{0:0000000}", Convert.ToInt32(item.noOlder)), rowIndex); writer.WriteElement(c); //c = CreateCell(4, item.cusnameOlder.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(5, item.patternNew.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(6, item.serialNew.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(7, string.Format("{0:0000000}", Convert.ToInt32(item.noNew)), rowIndex); writer.WriteElement(c); c = CreateCell(8, item.cusnameNew.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(9, item.username.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(10, String.Format("{0:dd/MM/yyyy}", DateTime.Parse(item.proccessdate)), rowIndex); writer.WriteElement(c); if (item.status == 1) { c = CreateCell(11, "Lập hóa đơn thay thế", rowIndex); writer.WriteElement(c); } else if (item.status == 2) { c = CreateCell(11, "Lập hóa đơn điều chỉnh", rowIndex); writer.WriteElement(c); } //write the end of the row writer.WriteEndElement(); } writer.WriteEndElement(); } else if ((reader.IsStartElement)) { //Start elements are directly copied writer.WriteStartElement(reader); } else if ((reader.IsEndElement)) { //End elements are directly copied writer.WriteEndElement(); } } //Close the reader and writer reader.Close(); writer.Close(); //Get the newly created sheet (same id as the old sheet, but it is the first one) Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First(); //Assign it the new sheet id sheet.Id.Value = replacementPartId; //remove the old sheet workbookPart.DeletePart(worksheetPart); //Done myDoc.Close(); //Delete template file if (File.Exists(templatePath)) { File.Delete(templatePath); } } }