static void CreateExcelReport(int x) { log.Info("Thread " + x + " started"); Console.WriteLine("Thread " + x + " started"); string filePath = @"e:\SolbegSoft\Excel\" + Guid.NewGuid() + ".xlsx"; int rowNumber = RandomNumber(300000, 500000); int skipNumber = RandomNumber(0, 1500000); log.Info(filePath + " " + skipNumber + " " + rowNumber); Console.WriteLine(filePath + " " + skipNumber + " " + rowNumber); using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) { xl.CompressionOption = CompressionOption.SuperFast; //IEnumerable<FakeObject> objects = db.Objects.OrderBy(ob => ob.Id).Skip(skipNumber).Take(rowNumber); xl.AddWorkbookPart(); WorksheetPart wsp = xl.WorkbookPart.AddNewPart <WorksheetPart>(); using (var oxw = OpenXmlWriter.Create(wsp)) { oxw.WriteStartElement(new Worksheet()); oxw.WriteStartElement(new SheetData()); int chunkSize = 40000; var ostatok = rowNumber % chunkSize; int chunksCount = ostatok == 0 ? (rowNumber / chunkSize) : (rowNumber / chunkSize + 1); List <string> headers = new List <string>(); headers.Add("1"); headers.Add("2"); headers.Add("3"); headers.Add("4"); headers.Add("5"); headers.Add("6"); headers.Add("7"); headers.Add("8"); headers.Add("9"); headers.Add("10"); headers.Add("11"); WriteRow(headers, oxw, 1); try { Enumerable.Range(0, chunksCount).ToList().ForEach(number => { var currentChunkSize = number == chunksCount - 1 ? ostatok : chunkSize; FakeDbContext db = new FakeDbContext(); log.Info("Thread " + x + " chunk " + number); Console.WriteLine("Thread " + x + " chunk " + number); var chunk = db.Objects.OrderBy(ob => ob.Id) .Skip(skipNumber + number * chunkSize) .Take(currentChunkSize) .ToList(); WriteRow(chunk, oxw, number * chunkSize + 2); }); } catch (Exception e) { log.Error(e.Message); } // this is for SheetData oxw.WriteEndElement(); // this is for Worksheet oxw.WriteEndElement(); oxw.Close(); using (var oxw1 = OpenXmlWriter.Create(xl.WorkbookPart)) { oxw1.WriteStartElement(new Workbook()); oxw1.WriteStartElement(new Sheets()); oxw1.WriteElement(new Sheet() { Name = "Sheet1", SheetId = 1, Id = xl.WorkbookPart.GetIdOfPart(wsp) }); // this is for Sheets oxw1.WriteEndElement(); // this is for Workbook oxw1.WriteEndElement(); oxw1.Close(); } } xl.Close(); } log.Info("Thread " + x + " finished"); Console.WriteLine("Thread " + x + " finished"); }
private static 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 = ""; 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 // uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex }); 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"); } 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; if (double.TryParse(cellValue, out cellFloatValue)) { cellValue = cellFloatValue.ToString(ci); AppendNumericCell(cellReference, cellValue, writer); } } else if (IsDateColumn[colInx]) { // This is a date value. 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(); }
/* * report.GetReportData() */ public ActionResult Export(Reports report) { try { var reportData = report.GetReportData(true); 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("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { CharSet = "utf-8" }.ToString(); context.AppendHeader("Content-Disposition", new ContentDisposition { DispositionType = DispositionTypeNames.Attachment, FileName = string.Format(CultureInfo.InvariantCulture, RemoveInvalidFilePathCharacters(reportData.title) + "_{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(new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }), // 0 new Font(new Bold()), // 1 new Font(new Color() { Rgb = new HexBinaryValue() { Value = "FF0000" } }) // 2 ), 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 = new Dictionary <string, object>() { { Words.Reports_Name, "" }, { Words.Reports_Value, "" } }; if (firstRow != null) { int row = 1; AddLine(writer, row, new string[] { reportData.title }); row++; AddLine(writer, row, new string[] { "" }); row++; 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++; for (int i = 0; i < reportData.labels.Length; i++) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col = 1; var row2 = new Dictionary <string, object>() { { "Name", reportData.labels[i] }, { "Value", reportData.datasets[0].data[i] } }; 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); } } } catch (Exception ex) { ex.Log(); } return(null); }
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); }
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart); 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]; 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"); } 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(); // 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 { // 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(); }
/// <summary> /// OpenWriter Style, for Multiple DataTable into Multiple Worksheets in a single Workbook. A real f*****g pain. /// </summary> /// <param name="filePath">Where your file will be.</param> /// <param name="listDataTables">List of dataTables. Can contain just 1, doesn't matter.</param> /// <param name="yesHeader">You want headers?</param> /// <param name="yesZero">You want zero instead of null?</param> public void LargeExportOneWorkbook( string filePath, IEnumerable <DataTable> listDataTables, bool yesHeader = false, bool yesZero = false) { try { using (SpreadsheetDocument document = SpreadsheetDocument.Create( filePath, SpreadsheetDocumentType.Workbook)) { document.AddWorkbookPart(); OpenXmlWriter writerXb = OpenXmlWriter.Create(document.WorkbookPart); writerXb.WriteStartElement(new Workbook()); writerXb.WriteStartElement(new Sheets()); var count = 0; foreach (DataTable dt in listDataTables) { var dicColName = new Dictionary <int, string>(); for (var colIndex = 0; colIndex < dt.Columns.Count; colIndex++) { int dividend = colIndex + 1; string columnName = string.Empty; while (dividend > 0) { int modifier = (dividend - 1) % 26; columnName = $"{Convert.ToChar(65 + modifier).ToString(CultureInfo.InvariantCulture)}{columnName}"; dividend = (dividend - modifier) / 26; } dicColName.Add(colIndex + 1, columnName); } // var dicType = new Dictionary<Type, CellValues>(4) // { // { typeof(DateTime), CellValues.Date }, // { typeof(string), CellValues.InlineString }, // { typeof(double), CellValues.Number }, // { typeof(int), CellValues.Number }, // { typeof(bool), CellValues.Boolean } // }; var dicType = new Dictionary <Type, string>(4) { { typeof(DateTime), "d" }, { typeof(string), "s" }, { typeof(double), "n" }, { typeof(int), "n" }, { typeof(bool), "b" } }; // this list of attributes will be used when writing a start element List <OpenXmlAttribute> attributes; var workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); if (yesHeader) { // create a new list of attributes attributes = new List <OpenXmlAttribute> { // add the row index attribute to the list new OpenXmlAttribute("r", null, 1.ToString()) }; // write the row start element with the row index attribute writer.WriteStartElement(new Row(), attributes); for (var columnNum = 1; columnNum <= dt.Columns.Count; ++columnNum) { // reset the list of attributes attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", string.Empty, $"{dicColName[columnNum]}1") }; // add data type attribute - in this case inline string (you might want to look at the shared strings table) // add the cell reference attribute // write the cell start element with the type and reference attributes writer.WriteStartElement(new Cell(), attributes); // write the cell value writer.WriteElement(new CellValue(dt.Columns[columnNum - 1].ColumnName)); // writer.WriteElement(new CellValue(string.Format("This is Row {0}, Cell {1}", rowNum, columnNum))); // write the end cell element writer.WriteEndElement(); } // write the end row element writer.WriteEndElement(); } for (var rowNum = 1; rowNum <= dt.Rows.Count; rowNum++) { // create a new list of attributes attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, (yesHeader ? rowNum + 1 : rowNum).ToString()) }; // add the row index attribute to the list // write the row start element with the row index attribute writer.WriteStartElement(new Row(), attributes); DataRow dr = dt.Rows[rowNum - 1]; for (var columnNum = 1; columnNum <= dt.Columns.Count; columnNum++) { Type type = dt.Columns[columnNum - 1].DataType; // reset the list of attributes attributes = new List <OpenXmlAttribute> { // Add data type attribute - in this case inline string (you might want to look at the shared strings table) new OpenXmlAttribute("t", null, type == typeof(string) ? "str" : dicType[type]), // Add the cell reference attribute new OpenXmlAttribute("r", string.Empty, $"{dicColName[columnNum]}{(yesHeader ? rowNum + 1 : rowNum).ToString(CultureInfo.InvariantCulture)}") }; // write the cell start element with the type and reference attributes writer.WriteStartElement(new Cell(), attributes); // write the cell value if (yesZero | (dr[columnNum - 1].ToString() != "0")) { writer.WriteElement(new CellValue(dr[columnNum - 1].ToString())); } // write the end cell element writer.WriteEndElement(); } // write the end row element writer.WriteEndElement(); } // write the end SheetData element writer.WriteEndElement(); // write the end Worksheet element writer.WriteEndElement(); writer.Close(); writerXb.WriteElement( new Sheet { Name = dt.TableName, SheetId = Convert.ToUInt32(count + 1), Id = document.WorkbookPart.GetIdOfPart(workSheetPart) }); count++; } // End Sheets writerXb.WriteEndElement(); // End Workbook writerXb.WriteEndElement(); writerXb.Close(); document.WorkbookPart.Workbook.Save(); document.Close(); } } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } }
/// <summary> /// 将对象保存到文件中。 /// </summary> /// <param name="path">路径。</param> /// <param name="models">模型数据表格。</param> /// <param name="sheetName">工作表名称。</param> /// <param name="sheetId">索引Id。</param> public void Save(string path, DataTable models, string sheetName = "sheet1", uint sheetId = 1) { using var document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook); var index = 1U; var workbookPart = document.AddWorkbookPart(); //写入样式 WriteStylesheet(workbookPart, null); //工作表 var workSheetPart = workbookPart.AddNewPart <WorksheetPart>(); var writer = OpenXmlWriter.Create(workSheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); //字段定义 var sharedStrings = new List <string>(); //第一行标题 var row = new Row(); row.RowIndex = index; for (var i = 0; i < models.Columns.Count; i++) { var descriptor = models.Columns[i]; var cell = new Cell(); cell.StyleIndex = 11; cell.DataType = CellValues.String; cell.CellValue = new CellValue(descriptor.ColumnName); cell.CellReference = $"{(char)('A' + i)}{index}"; row.AppendChild(cell); } writer.WriteElement(row); index++; //写入数据 foreach (DataRow model in models.Rows) { row = new Row(); row.RowIndex = index; for (var i = 0; i < models.Columns.Count; i++) { var descriptor = models.Columns[i]; var value = model[descriptor.ColumnName]; if (value == null) { continue; } var type = CellValues.Error; if (value is DateTime date) { value = date.ToOADate(); } else if (value is DateTimeOffset dateTimeOffset) { value = dateTimeOffset.DateTime.ToOADate(); } else if (value is bool bValue) { value = bValue ? 1 : 0; type = CellValues.Boolean; } else if (!value.GetType().IsValueType) { type = CellValues.SharedString; var current = value.ToString(); var si = sharedStrings.IndexOf(current); if (si == -1) { si = sharedStrings.Count; sharedStrings.Add(current); } value = si; } var cell = new Cell(); cell.StyleIndex = 10; if (type != CellValues.Error) { cell.DataType = type; } cell.CellReference = $"{(char)('A' + i)}{index}"; cell.CellValue = new CellValue(value.ToString()); row.AppendChild(cell); } writer.WriteElement(row); index++; } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); //工作区 writer = OpenXmlWriter.Create(document.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet { Name = sheetName, SheetId = UInt32Value.FromUInt32(sheetId), Id = document.WorkbookPart.GetIdOfPart(workSheetPart) }); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); //写入字符串 var shared = workbookPart.AddNewPart <SharedStringTablePart>(); var table = new SharedStringTable(); foreach (var sharedString in sharedStrings) { table.AppendChild(new SharedStringItem(new Text(sharedString))); } table.Save(shared); }
private static async Task <MemoryStream> DataToExcelStreamAsync(WriteRowsDelegate writeRows, IList <String> headers, string sheetName, List <int> columnWidths = null) { var xmlStream = ReportingHelper.GetResourceStream("Shesha.Web.DataTable.Excel.template.xlsx", typeof(ExcelUtility).Assembly); using (var document = SpreadsheetDocument.Open(xmlStream, true)) { var workbookPart = document.WorkbookPart; var worksheetPart = workbookPart.WorksheetParts.First(); var originalSheetId = workbookPart.GetIdOfPart(worksheetPart); var replacementPart = workbookPart.AddNewPart <WorksheetPart>(); var replacementPartId = workbookPart.GetIdOfPart(replacementPart); // Configure the spreadsheet SetSheetName(sheetName, document); SetStyleSheet(document); // Fit to page var sp = new SheetProperties(new PageSetupProperties()); var ws = worksheetPart.Worksheet; ws.SheetProperties = sp; // Set the FitToPage property to true ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true); var pgOr = new PageSetup { // Page size A4 landscape PaperSize = 9, Orientation = OrientationValues.Landscape, // Scale to fit to page width FitToWidth = 1, FitToHeight = 0 }; ws.AppendChild(pgOr); var maxWidth = 0; if (columnWidths != null) { var idx = 1; var columns = columnWidths .Select( w => new Column { CustomWidth = true, Min = Convert.ToUInt32(idx), Max = Convert.ToUInt32(idx++), Width = w, BestFit = false }) .ToList(); var cols = new DocumentFormat.OpenXml.Spreadsheet.Columns(columns); worksheetPart.Worksheet.InsertBefore(cols, worksheetPart.Worksheet.GetFirstChild <SheetData>()); } else { maxWidth = headers.Select(h => h.Length).Max(); AddCellWidthStyles(Convert.ToUInt32(1), Convert.ToUInt32(headers.Count), maxWidth, document, worksheetPart); } worksheetPart.Worksheet.Save(); document.WorkbookPart.Workbook.Save(); using (var xmlReader = OpenXmlReader.Create(worksheetPart)) { using (var xmlWriter = OpenXmlWriter.Create(replacementPart)) { while (xmlReader.Read()) { if (xmlReader.ElementType == typeof(SheetData)) { if (xmlReader.IsEndElement) { continue; } xmlWriter.WriteStartElement(new SheetData()); var headerCell = new Cell(new CellValue()); headerCell.DataType = new EnumValue <CellValues>(CellValues.String); // write headers xmlWriter.WriteStartElement(new Row()); SetHeaderStyle(document, headerCell); foreach (var header in headers) { headerCell.CellValue.Text = header; xmlWriter.WriteElement(headerCell); } xmlWriter.WriteEndElement(); await writeRows.Invoke(xmlWriter); xmlWriter.WriteEndElement(); } else { if (xmlReader.IsStartElement) { xmlWriter.WriteStartElement(xmlReader); } else if (xmlReader.IsEndElement) { xmlWriter.WriteEndElement(); } } } } } var sheet = workbookPart.Workbook.Descendants <Sheet>().First(s => s.Id.Value.Equals(originalSheetId)); sheet.Id.Value = replacementPartId; workbookPart.DeletePart(worksheetPart); } return(xmlStream); }
public void ExportToExcel() { try { using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel|*.xlsx", ValidateNames = true, }) { if (sfd.ShowDialog() == DialogResult.OK) { SpreadsheetDocument xl = SpreadsheetDocument.Create(sfd.FileName, SpreadsheetDocumentType.Workbook); List <OpenXmlAttribute> oxa; OpenXmlWriter oxw; xl.AddWorkbookPart(); WorksheetPart wsp = xl.WorkbookPart.AddNewPart <WorksheetPart>(); oxw = OpenXmlWriter.Create(wsp); oxw.WriteStartElement(new Worksheet()); oxw.WriteStartElement(new SheetData()); foreach (ListViewItem item in listView1.Items) { oxa = new List <OpenXmlAttribute>(); oxw.WriteStartElement(new Row(), oxa); foreach (ListViewItem.ListViewSubItem subItem in item.SubItems) { oxa = new List <OpenXmlAttribute>(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(subItem.ToString())); // this is for Cell oxw.WriteEndElement(); } // this is for Row oxw.WriteEndElement(); } // this is for SheetData oxw.WriteEndElement(); // this is for Worksheet oxw.WriteEndElement(); oxw.Close(); oxw = OpenXmlWriter.Create(xl.WorkbookPart); oxw.WriteStartElement(new Workbook()); oxw.WriteStartElement(new Sheets()); oxw.WriteElement(new Sheet() { Name = "Sheet1", SheetId = 1, Id = xl.WorkbookPart.GetIdOfPart(wsp) }); // this is for Sheets oxw.WriteEndElement(); // this is for Workbook oxw.WriteEndElement(); oxw.Close(); xl.Close(); MessageBox.Show("Veriler Excel Dosyasına Başarıyla Kaydedildi", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } catch (Exception exception) { Console.WriteLine(exception); throw; } }
private void CreateDrawingBySAX(DrawingsPart drawingsPart) { var data = _data as IReadOnlyList <Sheet3Data>; var dataDic = new Dictionary <C.Values, C.SeriesText>(); for (uint i = 1; i <= data.First().DataDic.Count; i++) { var columnName = OpenXMLExcels.GetColumnNameByIndex(i); dataDic[new C.Values() { NumberReference = new C.NumberReference() { Formula = new C.Formula($"{_sheetName}!${columnName}$2:${columnName}${data.Count + 2}") } }] = new C.SeriesText() { StringReference = new C.StringReference() { Formula = new C.Formula($"{_sheetName}!${columnName}$1") } }; } var chartPart = drawingsPart.AddNewPart <ChartPart>(); drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing(); var twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new Xdr.TwoCellAnchor() { FromMarker = new Xdr.FromMarker() { ColumnId = new Xdr.ColumnId("5"), ColumnOffset = new Xdr.ColumnOffset("581025"), RowId = new Xdr.RowId("4"), RowOffset = new Xdr.RowOffset("114300") }, ToMarker = new Xdr.ToMarker() { ColumnId = new Xdr.ColumnId("13"), ColumnOffset = new Xdr.ColumnOffset("276225"), RowId = new Xdr.RowId("19"), RowOffset = new Xdr.RowOffset("0") } }); twoCellAnchor.Append(new Xdr.GraphicFrame() { NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties() { NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties() { Id = 2, Name = "Chart 1", Title = "产品每月产量折线图" }, NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties() }, Transform = new Xdr.Transform() { Offset = new Offset() { X = 0, Y = 0 }, Extents = new Extents() { Cx = 0, Cy = 0 } }, Graphic = new Graphic(new GraphicData(new C.ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) }) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart", }) }); twoCellAnchor.Append(new Xdr.ClientData()); using (var writer = OpenXmlWriter.Create(chartPart)) { //S: ChartSpace writer.WriteStartElement(new C.ChartSpace()); writer.WriteElement(new C.EditingLanguage() { Val = "zh-CN" }); //S: Chart writer.WriteStartElement(new C.Chart()); //S: PlotArea writer.WriteStartElement(new C.PlotArea()); writer.WriteElement(new C.Layout()); //S: LineChart writer.WriteStartElement(new C.LineChart()); writer.WriteElement(new C.Grouping() { Val = C.GroupingValues.Standard }); uint index = 0; foreach (var dataKvp in dataDic) { //S: LineChartSeries writer.WriteStartElement(new C.LineChartSeries()); writer.WriteElement(dataKvp.Key); writer.WriteElement(new C.Index() { Val = index }); writer.WriteElement(new C.Order() { Val = index }); writer.WriteElement(dataKvp.Value); if (index++ == 0) { var axisData = new C.CategoryAxisData() { StringReference = new C.StringReference() { Formula = new C.Formula($"{_sheetName}!$A$2:$A${data.Count + 2}") } }; writer.WriteElement(axisData); } //E: LineChartSeries writer.WriteEndElement(); } writer.WriteElement(new C.AxisId() { Val = 0 }); writer.WriteElement(new C.AxisId() { Val = 1 }); //E: LineChart writer.WriteEndElement(); writer.WriteElement(new C.CategoryAxis( new C.Crosses() { Val = C.CrossesValues.AutoZero }, new C.AutoLabeled() { Val = true }, new C.LabelAlignment() { Val = C.LabelAlignmentValues.Center }, new C.LabelOffset() { Val = 100 }) { AxisId = new C.AxisId() { Val = 0 }, Scaling = new C.Scaling(new C.Orientation() { Val = C.OrientationValues.MinMax }), AxisPosition = new C.AxisPosition() { Val = C.AxisPositionValues.Bottom }, TickLabelPosition = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo }, CrossingAxis = new C.CrossingAxis() { Val = 1 }, }); writer.WriteElement(new C.ValueAxis( new C.Crosses() { Val = C.CrossesValues.AutoZero }, new C.CrossBetween() { Val = C.CrossBetweenValues.Between }) { AxisId = new C.AxisId() { Val = 1 }, Scaling = new C.Scaling(new C.Orientation() { Val = C.OrientationValues.MinMax }), AxisPosition = new C.AxisPosition() { Val = C.AxisPositionValues.Left }, MajorGridlines = new C.MajorGridlines(), NumberingFormat = new C.NumberingFormat() { FormatCode = "General", SourceLinked = true }, TickLabelPosition = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo }, CrossingAxis = new C.CrossingAxis() { Val = 0 }, }); //E: PlotArea writer.WriteEndElement(); writer.WriteElement(new C.Legend( new C.LegendPosition() { Val = C.LegendPositionValues.Right }, new C.Layout() )); writer.WriteElement(new C.PlotVisibleOnly() { Val = true }); //E: Chart writer.WriteEndElement(); //E: ChartSpace writer.WriteEndElement(); writer.Close(); } }
private void WriteCommentPart(WorksheetCommentsPart wcp, VmlDrawingPart vdp) { List <SLCellPoint> listCommentKeys = slws.Comments.Keys.ToList <SLCellPoint>(); listCommentKeys.Sort(new SLCellReferencePointComparer()); bool bAuthorFound = false; int iAuthorIndex = 0; SLComment comm; int i = 0; SLRowProperties rp; SLColumnProperties cp; SLCellPoint pt; // just in case if (slws.Authors.Count == 0) { if (this.DocumentProperties.Creator.Length > 0) { slws.Authors.Add(this.DocumentProperties.Creator); } else { slws.Authors.Add(SLConstants.ApplicationName); } } int iDataRange = 1; // hah! optional... we'll see... int iOptionalShapeTypeId = 202; string sShapeTypeId = string.Format("_x0000_t{0}", iOptionalShapeTypeId); int iShapeIdBase = iDataRange * 1024; int iRowID = 0; int iColumnID = 0; double fRowRemainder = 0; double fColumnRemainder = 0; long lRowEMU = 0; long lColumnEMU = 0; long lRowRemainder = 0; long lColumnRemainder = 0; int iEMU = 0; double fMargin = 0; double fFrac = 0; int iFrac = 0; string sFrac = string.Empty; ImagePart imgp; string sFileName = string.Empty; // image data in base 64, relationship ID Dictionary <string, string> dictImageData = new Dictionary <string, string>(); // not supporting existing VML drawings. But if supporting, process the VmlDrawingPart for // ImageParts. // Apparently, Excel chokes if a "non-standard" relationship ID is given // to VML drawings. It seems to only accept the form "rId{num}", and {num} seems // to have to start from 1. I don't know if Excel will also choke if you jump // from 1 to 3, but I *do* know you can't even start from rId3. // Excel VML seems to be particularly strict on this... // The error originated by having a "non-standard" relationship ID for a // VML image. Say "R2dk723lgsjg2" or whatever. Then fire up Excel and open // that spreadsheet. Then save. Then open it again. You'll get an error. // Apparently, Excel will put "rId1" on the tag o:relid. The error is that // the original o:relid with "R2dk723lgsjg2" as the value is still there. // Meaning the o:relid attribute is duplicated, hence the error. // Why don't I just use the number of vdp.Parts or even vdp.ImageParts to // get the next valid relationship ID? I don't know. Paranoia? // The existing relationship IDs *might* be in sequential order, but you never // know what Excel accepts... If you can get me the Microsoft Excel developer // who can explain this, I'll gladly change the algorithm... // So why the dictionary? Apparently, Excel also chokes if there are duplicates of // the VML image. So even 2 unique relationship IDs that *happens* to have identical // image data will tie Excel into knots. I am so upset with Excel right now... // I know it will keep file size down if only unique image data is stored, but still... StringBuilder sbVml = new StringBuilder(); sbVml.Append("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"); sbVml.Append("<o:shapelayout v:ext=\"edit\">"); sbVml.AppendFormat("<o:idmap v:ext=\"edit\" data=\"{0}\"/>", iDataRange); sbVml.Append("</o:shapelayout>"); sbVml.AppendFormat("<v:shapetype id=\"{0}\" coordsize=\"21600,21600\" o:spt=\"{1}\" path=\"m,l,21600r21600,l21600,xe\">", sShapeTypeId, iOptionalShapeTypeId); sbVml.Append("<v:stroke joinstyle=\"miter\"/><v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>"); sbVml.Append("</v:shapetype>"); using (OpenXmlWriter oxwComment = OpenXmlWriter.Create(wcp)) { oxwComment.WriteStartElement(new Comments()); oxwComment.WriteStartElement(new Authors()); for (i = 0; i < slws.Authors.Count; ++i) { oxwComment.WriteElement(new Author(slws.Authors[i])); } oxwComment.WriteEndElement(); oxwComment.WriteStartElement(new CommentList()); for (i = 0; i < listCommentKeys.Count; ++i) { pt = listCommentKeys[i]; comm = slws.Comments[pt]; bAuthorFound = false; for (iAuthorIndex = 0; iAuthorIndex < slws.Authors.Count; ++iAuthorIndex) { if (comm.Author.Equals(slws.Authors[iAuthorIndex])) { bAuthorFound = true; break; } } if (!bAuthorFound) { iAuthorIndex = 0; } oxwComment.WriteStartElement(new Comment() { Reference = SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex), AuthorId = (uint)iAuthorIndex }); oxwComment.WriteElement(comm.rst.ToCommentText()); oxwComment.WriteEndElement(); sbVml.AppendFormat("<v:shape id=\"_x0000_s{0}\" type=\"#{1}\"", iShapeIdBase + i + 1, sShapeTypeId); sbVml.Append(" style='position:absolute;"); if (!comm.HasSetPosition) { comm.Top = pt.RowIndex - 1 + SLConstants.DefaultCommentTopOffset; comm.Left = pt.ColumnIndex + SLConstants.DefaultCommentLeftOffset; if (comm.Top < 0) { comm.Top = 0; } if (comm.Left < 0) { comm.Left = 0; } } if (comm.UsePositionMargin) { sbVml.AppendFormat("margin-left:{0}pt;", comm.LeftMargin.ToString("0.##", CultureInfo.InvariantCulture)); sbVml.AppendFormat("margin-top:{0}pt;", comm.TopMargin.ToString("0.##", CultureInfo.InvariantCulture)); } else { iRowID = (int)Math.Floor(comm.Top); fRowRemainder = comm.Top - iRowID; iColumnID = (int)Math.Floor(comm.Left); fColumnRemainder = comm.Left - iColumnID; lRowEMU = 0; lColumnEMU = 0; for (iEMU = 1; iEMU <= iRowID; ++iEMU) { if (slws.RowProperties.ContainsKey(iEMU)) { rp = slws.RowProperties[iEMU]; lRowEMU += rp.HeightInEMU; } else { lRowEMU += slws.SheetFormatProperties.DefaultRowHeightInEMU; } } if (slws.RowProperties.ContainsKey(iRowID + 1)) { rp = slws.RowProperties[iRowID + 1]; lRowRemainder = Convert.ToInt64(fRowRemainder * rp.HeightInEMU); lRowEMU += lRowRemainder; } else { lRowRemainder = Convert.ToInt64(fRowRemainder * slws.SheetFormatProperties.DefaultRowHeightInEMU); lRowEMU += lRowRemainder; } for (iEMU = 1; iEMU <= iColumnID; ++iEMU) { if (slws.ColumnBreaks.ContainsKey(iEMU)) { cp = slws.ColumnProperties[iEMU]; lColumnEMU += cp.WidthInEMU; } else { lColumnEMU += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } } if (slws.ColumnProperties.ContainsKey(iColumnID + 1)) { cp = slws.ColumnProperties[iColumnID + 1]; lColumnRemainder = Convert.ToInt64(fColumnRemainder * cp.WidthInEMU); lColumnEMU += lColumnRemainder; } else { lColumnRemainder = Convert.ToInt64(fColumnRemainder * slws.SheetFormatProperties.DefaultColumnWidthInEMU); lColumnEMU += lColumnRemainder; } fMargin = (double)lColumnEMU / (double)SLConstants.PointToEMU; sbVml.AppendFormat("margin-left:{0}pt;", fMargin.ToString("0.##", CultureInfo.InvariantCulture)); fMargin = (double)lRowEMU / (double)SLConstants.PointToEMU; sbVml.AppendFormat("margin-top:{0}pt;", fMargin.ToString("0.##", CultureInfo.InvariantCulture)); } if (comm.AutoSize) { sbVml.Append("width:auto;height:auto;"); } else { sbVml.AppendFormat("width:{0}pt;", comm.Width.ToString("0.##", CultureInfo.InvariantCulture)); sbVml.AppendFormat("height:{0}pt;", comm.Height.ToString("0.##", CultureInfo.InvariantCulture)); } sbVml.AppendFormat("z-index:{0};", i + 1); sbVml.AppendFormat("visibility:{0}'", comm.Visible ? "visible" : "hidden"); if (!comm.Fill.HasFill) { // use #ffffff ? sbVml.Append(" fillcolor=\"window [65]\""); } else if (comm.Fill.Type == SLA.SLFillType.NoFill) { sbVml.Append(" filled=\"f\""); sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } else if (comm.Fill.Type == SLA.SLFillType.SolidFill) { sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } else if (comm.Fill.Type == SLA.SLFillType.GradientFill) { if (comm.Fill.GradientColor.GradientStops.Count > 0) { sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.R.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.G.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.B.ToString("x2")); } } else if (comm.Fill.Type == SLA.SLFillType.BlipFill) { // don't have to do anything } else if (comm.Fill.Type == SLA.SLFillType.PatternFill) { sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.PatternForegroundColor.DisplayColor.R.ToString("x2"), comm.Fill.PatternForegroundColor.DisplayColor.G.ToString("x2"), comm.Fill.PatternForegroundColor.DisplayColor.B.ToString("x2")); } if (comm.LineColor != null) { sbVml.AppendFormat(" strokecolor=\"#{0}{1}{2}\"", comm.LineColor.Value.R.ToString("x2"), comm.LineColor.Value.G.ToString("x2"), comm.LineColor.Value.B.ToString("x2")); } if (comm.fLineWeight != null) { sbVml.AppendFormat(" strokeweight=\"{0}pt\"", comm.fLineWeight.Value.ToString("0.##", CultureInfo.InvariantCulture)); } sbVml.Append(" o:insetmode=\"auto\">"); sbVml.Append("<v:fill"); if (comm.Fill.Type == SLA.SLFillType.SolidFill || comm.Fill.Type == SLA.SLFillType.GradientFill) { if (comm.Fill.Type == SLA.SLFillType.SolidFill) { fFrac = 100.0 - (double)comm.Fill.SolidColor.Transparency; } else { fFrac = 100.0 - comm.bFromTransparency; } iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } // default is 1 if (!sFrac.Equals("1")) { sbVml.AppendFormat(" opacity=\"{0}\"", sFrac); } } if (comm.Fill.Type == SLA.SLFillType.SolidFill) { sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } else if (comm.Fill.Type == SLA.SLFillType.GradientFill) { if (comm.Fill.GradientColor.GradientStops.Count > 0) { sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.R.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.G.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.B.ToString("x2")); } else { // shouldn't happen, but you know, in case... sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } fFrac = 100.0 - comm.bToTransparency; iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } // default is 1 if (!sFrac.Equals("1")) { sbVml.AppendFormat(" o:opacity=\"{0}\"", sFrac); } sbVml.Append(" rotate=\"t\""); if (comm.Fill.GradientColor.GradientStops.Count > 0) { sbVml.Append(" colors=\""); for (int iGradient = 0; iGradient < comm.Fill.GradientColor.GradientStops.Count; ++iGradient) { // you take the position/gradient value straight fFrac = (double)comm.Fill.GradientColor.GradientStops[iGradient].Position; iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } if (iGradient > 0) { sbVml.Append(";"); } sbVml.AppendFormat("{0} #{1}{2}{3}", sFrac, comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.R.ToString("x2"), comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.G.ToString("x2"), comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.B.ToString("x2")); } sbVml.Append("\""); } if (comm.Fill.GradientColor.IsLinear) { // use temporarily // VML increases angles in counter-clockwise direction, // otherwise we'd just use the angle straight from the property //...fFrac = 360.0 - (double)comm.Fill.GradientColor.Angle; fFrac = (double)comm.Fill.GradientColor.Angle; sbVml.AppendFormat(" angle=\"{0}\"", fFrac.ToString("0.##", CultureInfo.InvariantCulture)); sbVml.Append(" focus=\"100%\" type=\"gradient\""); } else { switch (comm.Fill.GradientColor.PathType) { case A.PathShadeValues.Shape: sbVml.Append(" focusposition=\"50%,50%\" focus=\"100%\" type=\"gradientradial\""); break; case A.PathShadeValues.Rectangle: case A.PathShadeValues.Circle: // because there's no way to do a circular gradient with VML... switch (comm.Fill.GradientColor.Direction) { case SLA.SLGradientDirectionValues.Center: sbVml.Append(" focusposition=\"50%,50%\""); break; case SLA.SLGradientDirectionValues.CenterToBottomLeftCorner: // so the "centre" is at the top-right sbVml.Append(" focusposition=\"100%,0%\""); break; case SLA.SLGradientDirectionValues.CenterToBottomRightCorner: // so the "centre" is at the top-left sbVml.Append(" focusposition=\"0%,0%\""); break; case SLA.SLGradientDirectionValues.CenterToTopLeftCorner: // so the "centre" is at the bottom-right sbVml.Append(" focusposition=\"100%,100%\""); break; case SLA.SLGradientDirectionValues.CenterToTopRightCorner: // so the "centre" is at the bottom-left sbVml.Append(" focusposition=\"0%,100%\""); break; } sbVml.Append(" focus=\"100%\" type=\"gradientradial\""); break; } } } else if (comm.Fill.Type == SLA.SLFillType.BlipFill) { string sRelId = "rId1"; using (FileStream fs = new FileStream(comm.Fill.BlipFileName, FileMode.Open)) { byte[] ba = new byte[fs.Length]; fs.Read(ba, 0, ba.Length); string sImageData = Convert.ToBase64String(ba); if (dictImageData.ContainsKey(sImageData)) { sRelId = dictImageData[sImageData]; comm.Fill.BlipRelationshipID = sRelId; } else { // if we haven't found a viable relationship ID by 10 million iterations, // then we have serious issues... for (int iIDNum = 1; iIDNum <= SLConstants.VmlTenMillionIterations; ++iIDNum) { sRelId = string.Format("rId{0}", iIDNum.ToString(CultureInfo.InvariantCulture)); // we could use a hashset to store the relationship IDs so we // don't use the ContainsValue() because ContainsValue() is supposedly // slow... I'm not gonna care because if this algorithm slows enough // that ContainsValue() is inefficient, that means there are enough VML // drawings to choke a modestly sized art museum. if (!dictImageData.ContainsValue(sRelId)) { break; } } imgp = vdp.AddImagePart(SLA.SLDrawingTool.GetImagePartType(comm.Fill.BlipFileName), sRelId); fs.Position = 0; imgp.FeedData(fs); comm.Fill.BlipRelationshipID = vdp.GetIdOfPart(imgp); dictImageData[sImageData] = sRelId; } } sbVml.AppendFormat(" o:relid=\"{0}\"", comm.Fill.BlipRelationshipID); // all this to get from "myawesomepicture.jpg" to "myawesomepicture" sFileName = comm.Fill.BlipFileName; // use temporarily iFrac = sFileName.LastIndexOfAny("\\/".ToCharArray()); sFileName = sFileName.Substring(iFrac + 1); iFrac = sFileName.LastIndexOf("."); sFileName = sFileName.Substring(0, iFrac); sbVml.AppendFormat(" o:title=\"{0}\"", sFileName); sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); sbVml.Append(" recolor=\"t\" rotate=\"t\""); fFrac = 100.0 - (double)comm.Fill.BlipTransparency; iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } // default is 1 if (!sFrac.Equals("1")) { sbVml.AppendFormat(" o:opacity=\"{0}\"", sFrac); } if (comm.Fill.BlipTile) { sbVml.Append(" type=\"tile\""); sbVml.AppendFormat(" size=\"{0}%,{1}%\"", comm.Fill.BlipScaleX.ToString("0.##", CultureInfo.InvariantCulture), comm.Fill.BlipScaleY.ToString("0.##", CultureInfo.InvariantCulture)); } else { sbVml.Append(" type=\"frame\""); // use temporarily //fFrac = (50.0 - (double)comm.Fill.BlipLeftOffset) + (50.0 - (double)comm.Fill.BlipRightOffset); fFrac = 100.0 - (double)comm.Fill.BlipLeftOffset - (double)comm.Fill.BlipRightOffset; sbVml.AppendFormat(" size=\"{0}%,", fFrac.ToString("0.##", CultureInfo.InvariantCulture)); fFrac = 100.0 - (double)comm.Fill.BlipTopOffset - (double)comm.Fill.BlipBottomOffset; sbVml.AppendFormat("{0}%\"", fFrac.ToString("0.##", CultureInfo.InvariantCulture)); } } else if (comm.Fill.Type == SLA.SLFillType.PatternFill) { string sRelId = "rId1"; using (MemoryStream ms = new MemoryStream()) { using (System.Drawing.Bitmap bm = SLA.SLDrawingTool.GetVmlPatternFill(comm.Fill.PatternPreset)) { bm.Save(ms, System.Drawing.Imaging.ImageFormat.Png); } byte[] ba = new byte[ms.Length]; ms.Read(ba, 0, ba.Length); string sImageData = Convert.ToBase64String(ba); if (dictImageData.ContainsKey(sImageData)) { sRelId = dictImageData[sImageData]; comm.Fill.BlipRelationshipID = sRelId; } else { // go check the "normal" image part additions for comments... for (int iIDNum = 1; iIDNum <= SLConstants.VmlTenMillionIterations; ++iIDNum) { sRelId = string.Format("rId{0}", iIDNum.ToString(CultureInfo.InvariantCulture)); if (!dictImageData.ContainsValue(sRelId)) { break; } } imgp = vdp.AddImagePart(ImagePartType.Png, sRelId); ms.Position = 0; imgp.FeedData(ms); comm.Fill.BlipRelationshipID = vdp.GetIdOfPart(imgp); dictImageData[sImageData] = sRelId; } } sbVml.AppendFormat(" o:relid=\"{0}\"", comm.Fill.BlipRelationshipID); sbVml.AppendFormat(" o:title=\"{0}\"", SLA.SLDrawingTool.ConvertToVmlTitle(comm.Fill.PatternPreset)); sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.PatternBackgroundColor.DisplayColor.R.ToString("x2"), comm.Fill.PatternBackgroundColor.DisplayColor.G.ToString("x2"), comm.Fill.PatternBackgroundColor.DisplayColor.B.ToString("x2")); sbVml.Append(" recolor=\"t\" type=\"pattern\""); } sbVml.Append("/>"); if (comm.LineStyle != StrokeLineStyleValues.Single || comm.vLineDashStyle != null) { sbVml.Append("<v:stroke"); switch (comm.LineStyle) { case StrokeLineStyleValues.Single: // don't have to do anything break; case StrokeLineStyleValues.ThickBetweenThin: sbVml.Append(" linestyle=\"thickBetweenThin\"/>"); break; case StrokeLineStyleValues.ThickThin: sbVml.Append(" linestyle=\"thickThin\"/>"); break; case StrokeLineStyleValues.ThinThick: sbVml.Append(" linestyle=\"thinThick\"/>"); break; case StrokeLineStyleValues.ThinThin: sbVml.Append(" linestyle=\"thinThin\"/>"); break; } if (comm.vLineDashStyle != null) { switch (comm.vLineDashStyle.Value) { case SLDashStyleValues.Solid: sbVml.Append(" dashstyle=\"solid\"/>"); break; case SLDashStyleValues.ShortDash: sbVml.Append(" dashstyle=\"shortdash\"/>"); break; case SLDashStyleValues.ShortDot: sbVml.Append(" dashstyle=\"shortdot\"/>"); break; case SLDashStyleValues.ShortDashDot: sbVml.Append(" dashstyle=\"shortdashdot\"/>"); break; case SLDashStyleValues.ShortDashDotDot: sbVml.Append(" dashstyle=\"shortdashdotdot\"/>"); break; case SLDashStyleValues.Dot: sbVml.Append(" dashstyle=\"dot\"/>"); break; case SLDashStyleValues.Dash: sbVml.Append(" dashstyle=\"dash\"/>"); break; case SLDashStyleValues.LongDash: sbVml.Append(" dashstyle=\"longdash\"/>"); break; case SLDashStyleValues.DashDot: sbVml.Append(" dashstyle=\"dashdot\"/>"); break; case SLDashStyleValues.LongDashDot: sbVml.Append(" dashstyle=\"longdashdot\"/>"); break; case SLDashStyleValues.LongDashDotDot: sbVml.Append(" dashstyle=\"longdashdotdot\"/>"); break; } } if (comm.vEndCap != null) { switch (comm.vEndCap.Value) { case StrokeEndCapValues.Flat: sbVml.Append(" endcap=\"flat\"/>"); break; case StrokeEndCapValues.Round: sbVml.Append(" endcap=\"round\"/>"); break; case StrokeEndCapValues.Square: sbVml.Append(" endcap=\"square\"/>"); break; } } sbVml.Append("/>"); } if (comm.HasShadow) { sbVml.AppendFormat("<v:shadow on=\"t\" color=\"#{0}{1}{2}\" obscured=\"t\"/>", comm.ShadowColor.R.ToString("x2"), comm.ShadowColor.G.ToString("x2"), comm.ShadowColor.B.ToString("x2")); } sbVml.Append("<v:path o:connecttype=\"none\"/>"); sbVml.Append("<v:textbox style='mso-direction-alt:auto;"); switch (comm.Orientation) { case SLCommentOrientationValues.Horizontal: // don't have to do anything break; case SLCommentOrientationValues.TopDown: sbVml.Append("layout-flow:vertical;mso-layout-flow-alt:top-to-bottom;"); break; case SLCommentOrientationValues.Rotated270Degrees: sbVml.Append("layout-flow:vertical;mso-layout-flow-alt:bottom-to-top;"); break; case SLCommentOrientationValues.Rotated90Degrees: sbVml.Append("layout-flow:vertical;"); break; } if (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft) { sbVml.Append("direction:RTL;"); } // no else because don't have to do anything if (comm.AutoSize) { sbVml.Append("mso-fit-shape-to-text:t;"); } sbVml.Append("'><div"); if (comm.HorizontalTextAlignment != SLHorizontalTextAlignmentValues.Distributed || comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft) { sbVml.Append(" style='"); switch (comm.HorizontalTextAlignment) { case SLHorizontalTextAlignmentValues.Left: sbVml.Append("text-align:left;"); break; case SLHorizontalTextAlignmentValues.Justify: sbVml.Append("text-align:justify;"); break; case SLHorizontalTextAlignmentValues.Center: sbVml.Append("text-align:center;"); break; case SLHorizontalTextAlignmentValues.Right: sbVml.Append("text-align:right;"); break; case SLHorizontalTextAlignmentValues.Distributed: // don't have to do anything break; } if (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft) { sbVml.Append("direction:rtl;"); } sbVml.Append("'"); } sbVml.Append("></div>"); sbVml.Append("</v:textbox>"); sbVml.Append("<x:ClientData ObjectType=\"Note\">"); sbVml.Append("<x:MoveWithCells/>"); sbVml.Append("<x:SizeWithCells/>"); // anchors are bloody hindering awkward inconvenient to calculate... //sbVml.Append("<x:Anchor>"); //sbVml.Append("2, 15, 2, 14, 4, 23, 6, 19"); //sbVml.Append("</x:Anchor>"); sbVml.Append("<x:AutoFill>False</x:AutoFill>"); switch (comm.HorizontalTextAlignment) { case SLHorizontalTextAlignmentValues.Left: // don't have to do anything break; case SLHorizontalTextAlignmentValues.Justify: sbVml.Append("<x:TextHAlign>Justify</x:TextHAlign>"); break; case SLHorizontalTextAlignmentValues.Center: sbVml.Append("<x:TextHAlign>Center</x:TextHAlign>"); break; case SLHorizontalTextAlignmentValues.Right: sbVml.Append("<x:TextHAlign>Right</x:TextHAlign>"); break; case SLHorizontalTextAlignmentValues.Distributed: sbVml.Append("<x:TextHAlign>Distributed</x:TextHAlign>"); break; } switch (comm.VerticalTextAlignment) { case SLVerticalTextAlignmentValues.Top: // don't have to do anything break; case SLVerticalTextAlignmentValues.Justify: sbVml.Append("<x:TextVAlign>Justify</x:TextVAlign>"); break; case SLVerticalTextAlignmentValues.Center: sbVml.Append("<x:TextVAlign>Center</x:TextVAlign>"); break; case SLVerticalTextAlignmentValues.Bottom: sbVml.Append("<x:TextVAlign>Bottom</x:TextVAlign>"); break; case SLVerticalTextAlignmentValues.Distributed: sbVml.Append("<x:TextVAlign>Distributed</x:TextVAlign>"); break; } sbVml.AppendFormat("<x:Row>{0}</x:Row>", pt.RowIndex - 1); sbVml.AppendFormat("<x:Column>{0}</x:Column>", pt.ColumnIndex - 1); if (comm.Visible) { sbVml.Append("<x:Visible/>"); } sbVml.Append("</x:ClientData>"); sbVml.Append("</v:shape>"); } oxwComment.WriteEndElement(); // this is for Comments oxwComment.WriteEndElement(); } sbVml.Append("</xml>"); using (MemoryStream mem = new MemoryStream(Encoding.ASCII.GetBytes(sbVml.ToString()))) { vdp.FeedData(mem); } }
/// <summary> /// Conducts the import of the file to the workbook /// </summary> public override void Import() { Trace.Indent(); Trace.WriteLine(string.Format("Importing file {0}", FileInfo.AbsolutePath), TraceCategory); if (!File.Exists(FileInfo.AbsolutePath)) { Trace.TraceWarning("Importing file {0}", FileInfo.AbsolutePath); return; } var csvSourcePath = File.OpenRead(FileInfo.AbsolutePath); // get the existing sheets collection var sheets = TargetWorkbook.WorkbookPart.Workbook.GetFirstChild <Sheets>(); // used as the name for the sheet and import tracking display var sheetName = GetSheetName(sheets); // we need to check to see if a sheet with this name already exists if (sheets.Cast <Sheet>().Where(s => s.Name.Value.Equals(sheetName, StringComparison.OrdinalIgnoreCase)).Any()) { throw new TargetSheetExistsException(sheetName); } var totalLineCount = File.ReadAllLines(FileInfo.AbsolutePath).LongCount(); _progressCallback(new SMATGeneratorProgressInfo() { CurrentFileMax = totalLineCount, CurrentFileName = FileInfo.Filename, CurrentFilePosition = 0, TotalFilesMax = 0, TotalFilesPosition = 0, }); // create the new worksheet var worksheetPart = TargetWorkbook.WorkbookPart.AddNewPart <WorksheetPart>(); var sheet = new Sheet() { Id = TargetWorkbook.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = sheets.NextAvailableSheetId(), Name = sheetName }; sheets.Append(sheet); using (var csvReader = new TextFieldParser(csvSourcePath)) { csvReader.Delimiters = new[] { "," }; csvReader.TextFieldType = FieldType.Delimited; csvReader.HasFieldsEnclosedInQuotes = true; csvReader.TrimWhiteSpace = true; // used to track the current row uint currentTargetRowPointer = 1; var typeEnumMapping = new List <CellValues>(); string cellAddress; string[] fields; using (var writer = OpenXmlWriter.Create(worksheetPart)) { writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); while (!csvReader.EndOfData) { fields = csvReader.ReadFields(); try { // line 2 means "fields" actually has the values from row 1 but the pointer has been advanced in LineNumber if (csvReader.LineNumber == 2) { using (writer.StartRow(currentTargetRowPointer)) { // the original template sheets had two empty columns, so we match that writer.WriteCell(string.Format("B{0}", currentTargetRowPointer), "Remediation", CellValues.InlineString); for (var i = 0; i < fields.Length; i++) { // the +3 here is derived from a need to +1 for the 1 based columns and +2 as we skip the first two columns by design cellAddress = GetCellAddress((uint)(i + 3), currentTargetRowPointer); writer.WriteCell(cellAddress, fields[i], CellValues.InlineString); } } } else { // line 3 means "fields" actually has the values from row 2 but the pointer has been advanced in LineNumber if (typeEnumMapping.Count < 1) { // we need to use the first row of data to determine as best we can the column types for (var i = 0; i < fields.Length; i++) { typeEnumMapping.Add(GetCellDataType(fields[i])); } } using (writer.StartRow(currentTargetRowPointer)) { for (var i = 0; i < fields.Length; i++) { // the +3 here is derived from a need to +1 for the 1 based columns and +2 as we skip the first two columns by design cellAddress = GetCellAddress((uint)(i + 3), currentTargetRowPointer); writer.WriteCell(cellAddress, fields[i], typeEnumMapping[i]); } } } } catch (Exception err) { Trace.TraceError(string.Format("Error writing row {0} (csv line number: {1}) for target sheet {2}. Error: {3}", currentTargetRowPointer, csvReader.LineNumber, sheetName, err)); } finally { // move to the next row currentTargetRowPointer++; } if (csvReader.LineNumber > 0) { _progressCallback(new SMATGeneratorProgressInfo() { CurrentFileMax = totalLineCount, CurrentFileName = FileInfo.Filename, CurrentFilePosition = currentTargetRowPointer, TotalFilesMax = 0, TotalFilesPosition = 0, }); } } writer.WriteEndElement(); // end sheet data writer.WriteEndElement(); // end worksheet } } // always end with full progress reported _progressCallback(new SMATGeneratorProgressInfo() { CurrentFileMax = totalLineCount, CurrentFileName = FileInfo.Filename, CurrentFilePosition = totalLineCount, TotalFilesMax = 0, TotalFilesPosition = 0, }); Trace.Unindent(); }
/// <summary> /// 将对象保存到文件中。 /// </summary> /// <typeparam name="TModel">模型列表类型。</typeparam> /// <param name="models">模型列表实例。</param> /// <param name="path">路径。</param> public void Save <TModel>(IEnumerable <TModel> models, string path) where TModel : class, new() { var data = models as ExcelEnumerable <TModel> ?? new ExcelEnumerable <TModel>(models); using (var document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook)) { var index = 1U; var workbookPart = document.AddWorkbookPart(); //写入样式 WriteStylesheet(workbookPart, data); //工作表 var workSheetPart = workbookPart.AddNewPart <WorksheetPart>(); var writer = OpenXmlWriter.Create(workSheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); //字段定义 var descriptors = data.Descriptors.OrderBy(x => x.Index).ToList(); var sharedStrings = new List <string>(); //第一行标题 var row = new Row(); row.RowIndex = index; for (var i = 0; i < data.Columns; i++) { var descriptor = descriptors[i]; var cell = new Cell(); cell.StyleIndex = descriptor.HeadCellFormat.FormatId; cell.DataType = CellValues.String; cell.CellValue = new CellValue(descriptor.ColumnName); cell.CellReference = $"{(char)('A' + i)}{index}"; row.AppendChild(cell); } writer.WriteElement(row); index++; //写入数据 foreach (var model in data) { row = new Row(); row.RowIndex = index; for (var i = 0; i < data.Columns; i++) { var descriptor = descriptors[i]; var value = descriptor.Get(model); if (value == null) { continue; } var type = CellValues.Error; if (value is DateTime date) { value = date.ToOADate(); } else if (value is DateTimeOffset dateTimeOffset) { value = dateTimeOffset.DateTime.ToOADate(); } else if (value is bool bValue) { value = bValue ? 1 : 0; type = CellValues.Boolean; } else if (!value.GetType().IsValueType) { type = CellValues.SharedString; var current = value.ToString(); var si = sharedStrings.IndexOf(current); if (si == -1) { si = sharedStrings.Count; sharedStrings.Add(current); } value = si; } var cell = new Cell(); cell.StyleIndex = descriptor.CellFormat.FormatId; if (type != CellValues.Error) { cell.DataType = type; } cell.CellReference = $"{(char)('A' + i)}{index}"; cell.CellValue = new CellValue(value.ToString()); row.AppendChild(cell); } writer.WriteElement(row); index++; } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); //工作区 writer = OpenXmlWriter.Create(document.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet { Name = data.SheetName, SheetId = 1, Id = document.WorkbookPart.GetIdOfPart(workSheetPart) }); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); //写入字符串 var shared = workbookPart.AddNewPart <SharedStringTablePart>(); var table = new SharedStringTable(); foreach (var sharedString in sharedStrings) { table.AppendChild(new SharedStringItem(new Text(sharedString))); } table.Save(shared); } }
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(); } }
public static void CreateExcel() { using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create("CustomerReport_Hard.xlsx", SpreadsheetDocumentType.Workbook)) { OpenXmlWriter oxw; List <OpenXmlAttribute> oxa; spreadsheetDoc.AddWorkbookPart(); WorksheetPart wsp = spreadsheetDoc.WorkbookPart.AddNewPart <WorksheetPart>(); oxw = OpenXmlWriter.Create(wsp); oxw.WriteStartElement(new Worksheet()); //columnas oxw.WriteStartElement(new Columns()); oxa = new List <OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("min", null, "1")); oxa.Add(new OpenXmlAttribute("max", null, "4")); oxa.Add(new OpenXmlAttribute("width", null, "25")); oxw.WriteStartElement(new Column(), oxa); oxw.WriteEndElement(); oxa = new List <OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("min", null, "6")); oxa.Add(new OpenXmlAttribute("max", null, "6")); oxa.Add(new OpenXmlAttribute("width", null, "40")); oxw.WriteStartElement(new Column(), oxa); oxw.WriteEndElement(); oxw.WriteEndElement(); oxw.WriteStartElement(new SheetData()); oxw.WriteStartElement(new Row(), new List <OpenXmlAttribute>()); oxa = new List <OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("t", null, "str")); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Name")); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Register Date")); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Last Buy")); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Product")); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Cost")); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Quantity")); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue("Total")); oxw.WriteEndElement(); oxw.WriteEndElement(); foreach (Customer customer in Report.GetCustomers()) { oxw.WriteStartElement(new Row(), new List <OpenXmlAttribute>()); oxa = new List <OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("t", null, "str")); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.Name)); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.RegisterDate)); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.LastBuy)); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.Item)); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.ItemCost.ToString())); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(customer.Quantity.ToString())); oxw.WriteEndElement(); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue((customer.Quantity * customer.ItemCost).ToString())); oxw.WriteEndElement(); oxw.WriteEndElement(); } oxw.WriteEndElement(); oxw.WriteEndElement(); oxw.Close(); oxw = OpenXmlWriter.Create(spreadsheetDoc.WorkbookPart); oxw.WriteStartElement(new Workbook()); oxw.WriteStartElement(new Sheets()); oxw.WriteElement(new Sheet() { Name = "Sheet1", SheetId = 1, Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(wsp) }); oxw.WriteEndElement(); oxw.WriteEndElement(); oxw.Close(); spreadsheetDoc.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(); }
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); // Create cell with data 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(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }
public void CreateSpreadsheetWorkbook1 <T>(string filePath, List <T> data, List <OpenExcelColumn <T> > columns) { using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) { OpenXmlWriter oxw; xl.AddWorkbookPart(); AddStyleSheetOld(xl); WorksheetPart wsp = xl.WorkbookPart.AddNewPart <WorksheetPart>(); oxw = OpenXmlWriter.Create(wsp); { oxw.WriteStartElement(new Worksheet()); { oxw.WriteStartElement(new SheetData()); { AddData(oxw, data, columns); } // this is for SheetData oxw.WriteEndElement(); } oxw.WriteEndElement(); // this is for Worksheet } oxw.Close(); oxw = OpenXmlWriter.Create(xl.WorkbookPart); { oxw.WriteStartElement(new Workbook()); { oxw.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. oxw.WriteElement(new Sheet() { Name = "Sheet1", SheetId = 1, Id = xl.WorkbookPart.GetIdOfPart(wsp) }); oxw.WriteElement(new Sheet() { Name = "Sheet2", SheetId = 2, Id = xl.WorkbookPart.GetIdOfPart(wsp) }); } // this is for Sheets oxw.WriteEndElement(); } // this is for Workbook oxw.WriteEndElement(); } oxw.Close(); xl.Close(); } }
public static void Write(this SpreadsheetDocument spreadsheet, string sheetName, IDataReader reader) { var fieldCount = reader.FieldCount; var columns = new List <ExcelColumn>(fieldCount); var rowRead = reader.Read(); for (var index = 0; index < fieldCount; index++) { columns.Add(new ExcelColumn(index, reader.GetName(index), reader.GetFieldType(index))); } //create workbook part var wbp = spreadsheet.AddWorkbookPart(); wbp.Workbook = new Workbook(); wbp.Workbook.Append(new BookViews(new WorkbookView())); var sheets = wbp.Workbook.AppendChild <Sheets>(new Sheets()); var stylesPart = wbp.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = GenerateStyleSheet(); stylesPart.Stylesheet.Save(); //create worksheet part, and add it to the sheets collection in workbook var wsp = wbp.AddNewPart <WorksheetPart>(); var sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsp), SheetId = 1, Name = sheetName }; sheets.Append(sheet); var sheetCount = 1; // Start Writing Values var writer = OpenXmlWriter.Create(wsp); writer.WriteStartElement(new Worksheet()); WriteFreezeTopRow(writer); writer.WriteStartElement(new SheetData()); WriteColumnsHeader(writer, columns); var sheetRowCount = 1; if (rowRead) { do { writer.WriteStartElement(new Row()); foreach (var col in columns) { var cell = new Cell() { DataType = col.CellValues }; if (col.CellValues == CellValues.Date) { cell.DataType = CellValues.Number; cell.StyleIndex = 7; // Date (Stylesheet) } if (!reader.IsDBNull(col.Index)) { if (col.CellValues == CellValues.Date) { cell.CellValue = new CellValue(reader.GetDateTime(col.Index).ToOADate().ToString(CultureInfo.InvariantCulture)); } else if (col.CellValues == CellValues.Boolean) { cell.CellValue = new CellValue(reader.GetBoolean(col.Index) ? "1" : "0"); } else { var str = reader[col.Index].ToString().Replace("\a", ""); cell.CellValue = new CellValue(str); } } writer.WriteElement(cell); } writer.WriteEndElement(); // end of Row sheetRowCount++; if (sheetRowCount == 1048576) // MAX ROW { writer.WriteEndElement(); //end of SheetData WriteAutoFilter(writer, columns.Count); writer.WriteEndElement(); //end of Worksheet writer.Close(); sheetCount++; wsp = wbp.AddNewPart <WorksheetPart>(); sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsp), SheetId = (uint)sheetCount, Name = sheetName + sheetCount.ToString() }; sheets.Append(sheet); writer = OpenXmlWriter.Create(wsp); writer.WriteStartElement(new Worksheet()); WriteFreezeTopRow(writer); writer.WriteStartElement(new SheetData()); WriteColumnsHeader(writer, columns); sheetRowCount = 1; } } while (reader.Read()); } writer.WriteEndElement(); //end of SheetData if (sheetRowCount > 0) { WriteAutoFilter(writer, columns.Count); } writer.WriteEndElement(); //end of worksheet writer.Close(); }