public static string GetOrAddSharedString(this DocumentContext documentContext, string text) { if (text == null) { return(null); } if (!documentContext.SharedStringTable.TryGetValue(text, out string stringIndex)) { SharedStringTable sharedStringTable = documentContext.GetSharedStringTable(); SharedStringItem sharedStringItem = new SharedStringItem(); Text text1 = new Text { Text = text }; sharedStringItem.Append(text1); sharedStringTable.AppendChild(sharedStringItem); uint itemCount = (uint)sharedStringTable.ChildElements.Count; sharedStringTable.Count = itemCount; sharedStringTable.UniqueCount = itemCount; stringIndex = (itemCount - 1).ToString(); documentContext.SharedStringTable.Add(text, stringIndex); } return(stringIndex); }
// Given the main workbook part, and a text value, insert the text into // the shared string table. private static int InsertSharedStringItem(SharedStringTable stringTable, WorkbookPart wbPart, string value) { int _index = 0; bool _found = false; // Iterate through all the items in the SharedStringTable. // If the text already exists, return its index. foreach (SharedStringItem _item in stringTable.Elements <SharedStringItem>()) { if (_item.InnerText == value) { _found = true; break; } _index++; } if (!_found) { stringTable.AppendChild(new SharedStringItem(new Text(value))); } return(_index); }
public static void AddStringCell(string str, Row row, SharedStringTable sharedStringTable) { var item = sharedStringTable.AppendChild(new SharedStringItem(new Text(str ?? ""))); var cell = new Cell(); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); cell.CellValue = new CellValue(item.ElementsBefore().Count().ToString()); row.Append(cell); }
public uint SetSharedString(string text) { uint index; if (TryGetSharedStringIndex(text, out index)) { return(index); } SharedStringTable.AppendChild(new SharedStringItem(new Text(text))); SharedStringTable.Save(); index = SharedStringTable.Count.Value - 1; Index[text] = index; return(index); }
public static bool AddSharedString(SpreadsheetDocument spreadsheet, string stringItem, bool save = true) { SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable; if (0 == sharedStringTable.Where(item => item.InnerText == stringItem).Count()) { sharedStringTable.AppendChild(new SharedStringItem(new Text(stringItem))); if (save) { sharedStringTable.Save(); } } return(true); }
public void buildTable(SpreadsheetDocument doc) { SharedStringTablePart sharedStringPart = doc.WorkbookPart.AddNewPart <SharedStringTablePart>(); SharedStringTable table = new SharedStringTable(); table.Count = new UInt32Value((uint)list.Count()); table.UniqueCount = new UInt32Value((uint)list.Count()); sharedStringPart.SharedStringTable = table; foreach (string str in list) { SharedStringItem si = new SharedStringItem(); si.AppendChild <Text>(new Text(str)); table.AppendChild <SharedStringItem>(si); } }
private static int GetSharedStringIndex(SharedStringTable sharedStrTbl, string str) { int i = 0; foreach (SharedStringItem item in sharedStrTbl.Elements <SharedStringItem>()) { if (item.InnerText == str) { return(i); } i++; } sharedStrTbl.AppendChild(new SharedStringItem(new Text(str))); return(i); }
private int InsertSharedString(string text) { int i = 0; // Iterate through all the items in the SharedStringTable. If the text already exists, return its index. foreach (SharedStringItem item in _stringTable.Elements <SharedStringItem>()) { if (item.InnerText == text) { return(i); } i++; } // The text does not exist in the part. Create the SharedStringItem and return its index. _stringTable.AppendChild(new SharedStringItem(new Text(text))); return(i); }
/// <summary> /// 作成した Excelファイルを持つ、MemoryStream を返す /// </summary> /// <returns>作成した Excelファイル</returns> public System.IO.MemoryStream GetExcelFile() { Int32 count = 0; foreach (string key in _sharedStringDic.Keys) { if (count >= _sharedStringId) { _sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(key))); } ++count; } _sharedStringTable.Save(); _ws.Save(); _wbPart.Workbook.Save(); _document.Close(); _ms.Position = 0; return(_ms); }
private void CreateSharedStringTable(Dictionary <string, object[]> fieldDictionary, DataTable dataTable, WorkbookPart workbookPart, Dictionary <string, int> sharedStringIndices) { var sharedStringTablePart = workbookPart.AddNewPart <SharedStringTablePart>("rId100"); var sharedStringTable = new SharedStringTable { Count = (UInt32Value) (fieldDictionary.Count * (2 + dataTable.Rows.Count) + 0U), UniqueCount = (UInt32Value)(sharedStringIndices.Count + 0U) }; foreach (var pair in sharedStringIndices) { sharedStringTable.AppendChild(new SharedStringItem(new Text(pair.Key))); } sharedStringTablePart.SharedStringTable = sharedStringTable; }
//获取指定字符串在SharedStringTable中的索引值,不存在就创建 private int getSharedStringItemIndex(string value) { //字符串从0开始标记 int index = 0; //寻找是否有与value相同的字符串,若有,则将index设置为对应的标记值,并返回 //SharedStringItem:共享字符串的数据类型 //sharedStringTable:共享字符串表 foreach (SharedStringItem item in sharedStringTable.Elements <SharedStringItem>()) { if (item.InnerText == value) { return(index); } index++; } //若没有与value相同的字符串,则添加一个字符串到共享字符串表中,并将其内容设置为value sharedStringTable.AppendChild(new SharedStringItem(new Text(value))); sharedStringTable.Save(); return(index); }
private static int InsertSharedStringItem(string text, SharedStringTable sharedString) { int i = 0; // Iterate through all the items in the SharedStringTable. If the text already exists, return its index. foreach (SharedStringItem item in sharedString.Elements <SharedStringItem>()) { if (item.InnerText == text) { return(i); } i++; } // The text does not exist in the part. Create the SharedStringItem and return its index. sharedString.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text))); sharedString.Save(); return(i); }
int GetOrCreateSharedStringItem(string text) { // Iterate through all the items in the SharedStringTable. If the text already exists, return its index. int index = 0; foreach (var item in SharedStringTable.Elements <SharedStringItem>()) { if (item.InnerText == text) { return(index); } ++index; } // The text does not exist in the part. Create the SharedStringItem and return its index. var result = new SharedStringItem(new Text(text)); SharedStringTable.AppendChild(result); SharedStringTable.Save(); return(index); }
/// <summary> /// Gets or adds shared string and returns its index. /// </summary> /// <param name="documentContext">Source document.</param> /// <param name="text">Text to add.</param> /// <returns>Index in excel SharedStringTable.</returns> public static string?GetOrAddSharedString(this DocumentContext documentContext, string?text) { if (text == null) { return(null); } if (!documentContext.Cache.SharedStringTable.TryGetValue(text, out string stringIndex)) { SharedStringTable sharedStringTable = documentContext.GetSharedStringTable(); SharedStringItem sharedStringItem = new SharedStringItem(new Text { Text = text }); sharedStringTable.AppendChild(sharedStringItem); sharedStringTable.Count += 1; sharedStringTable.UniqueCount += 1; stringIndex = (sharedStringTable.Count - 1).ToString(); documentContext.Cache.SharedStringTable.TryAdd(text, stringIndex); } return(stringIndex); }
private static void FillSpreadsheetDocument(SpreadsheetDocument spreadsheetDocument, IList <ColumnModel> columnModels, string[][] data, string sheetName) { if (columnModels == null) { throw new ArgumentNullException("columnModels"); } if (data == null) { throw new ArgumentNullException("data"); } // add empty workbook and worksheet to the SpreadsheetDocument var workbookPart = spreadsheetDocument.AddWorkbookPart(); var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>(); // create styles for the header and columns workbookStylesPart.Stylesheet = new Stylesheet( new Fonts( // Index 0 - The default font. new Font( new FontSize { Val = 11 }, new Color { Rgb = new HexBinaryValue { Value = "00000000" } }, new FontName { Val = "Calibri" } ), // Index 1 - The bold font. new Font( new Bold(), new FontSize { Val = 11 }, new Color { Rgb = new HexBinaryValue { Value = "00000000" } }, new FontName { Val = "Calibri" } ) ), new Fills( // Index 0 - required, reserved by Excel - no pattern new Fill(new PatternFill { PatternType = PatternValues.None }), // Index 1 - required, reserved by Excel - fill of gray 125 new Fill(new PatternFill { PatternType = PatternValues.Gray125 }), // Index 2 - no pattern text on gray background new Fill(new PatternFill { PatternType = PatternValues.Solid, BackgroundColor = new BackgroundColor { Indexed = 64U }, ForegroundColor = new ForegroundColor { Rgb = "FFD9D9D9" } }) ), new Borders( // Index 0 - The default border. new Border( new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder() ), // Index 1 - Applies a Left, Right, Top, Bottom border to a cell new Border( new LeftBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new RightBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new TopBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new BottomBorder(new Color { Auto = true }) { Style = BorderStyleValues.Thin }, new DiagonalBorder() ) ), new CellFormats( // Index 0 - The default cell style. If a cell does not have a style iCol applied it will use this style combination instead new CellFormat { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }, // Index 1 - Alignment Left, Text new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Left }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 2 - Interger Number new CellFormat { NumberFormatId = (UInt32Value)1U, // "0" - integer format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true }, // Index 3 - Interger Date new CellFormat { NumberFormatId = (UInt32Value)14U, // "14" - date format mm-dd-yy - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true }, // Index 4 - Text for headers new CellFormat(new Alignment { Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Center }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 5 - Text for headers rotated new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Center, TextRotation = (UInt32Value)90U }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 6 - Alignment Center, Text new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Center }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true }, // Index 7 - Alignment Right, Text new CellFormat(new Alignment { Horizontal = HorizontalAlignmentValues.Right }) { NumberFormatId = (UInt32Value)49U, // "@" - text format - see http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)1U, ApplyNumberFormat = true, ApplyAlignment = true } ) ); workbookStylesPart.Stylesheet.Save(); // create and fill SheetData var sheetData = new SheetData(); // first row is the header uint iRow = 1; sheetData.AppendChild(CreateColumnHeaderRow(iRow++, columnModels)); // first of all collect all different strings var sst = new SharedStringTable(); var sharedStrings = new SortedDictionary <string, int>(); foreach (var dataRow in data) { for (var iCol = 0; iCol < dataRow.Length; iCol++) { if (iCol >= columnModels.Count || columnModels[iCol].Type != DataType.Integer) { string text = columnModels[iCol].Type == DataType.Date ? dataRow[iCol] : ConvertDateToString(dataRow[iCol]); if (!sharedStrings.ContainsKey(text)) { sst.AppendChild(new SharedStringItem(new Text(text))); sharedStrings.Add(text, sharedStrings.Count); } } } } var shareStringPart = workbookPart.AddNewPart <SharedStringTablePart>(); shareStringPart.SharedStringTable = sst; shareStringPart.SharedStringTable.Save(); foreach (var dataRow in data) { sheetData.AppendChild(CreateRow(iRow++, dataRow, columnModels, sharedStrings)); } // add sheet data to Worksheet worksheetPart.Worksheet = new Worksheet(sheetData); worksheetPart.Worksheet.Save(); // fill workbook with the Worksheet spreadsheetDocument.WorkbookPart.Workbook = new Workbook( new FileVersion { ApplicationName = "Microsoft Office Excel" }, new Sheets(new Sheet { Name = sheetName, SheetId = (UInt32Value)1U, Id = workbookPart.GetIdOfPart(worksheetPart) // generate the id for sheet }) ); spreadsheetDocument.WorkbookPart.Workbook.Save(); spreadsheetDocument.Close(); }
public async Task <FileInfo> GetExcelFile() { Dictionary <string, Int32> sharedStringDic = new Dictionary <string, int>(); const string excelTemplate = "CalInProcessTemplateVer4.xlsx"; const string sheetname = "Sheet1"; uint styleString = 0; uint styleDate = 0; string providername = Startup.AppSettings["StorageProvider"]; FileInfo fileinfo; Int32 sharedStringId = 0; UInt32 lineIndex; SysIO.MemoryStream ms = new SysIO.MemoryStream();; if (providername == "localfile") { // Response.WriteFile(AppResources.GetCalCertPath(pdffilename)); TemplateFolder string folder = Startup.AppSettings["PdfFoldername"]; string filepath = SysIO.Path.Combine(folder, excelTemplate); //fileinfo.FileByteStream = SysIO.File.Open(filepath, SysIO.FileMode.Open); SysIO.FileStream fs; fs = SysIO.File.Open(filepath, System.IO.FileMode.Open); fs.CopyTo(ms); long bytesInStream = ms.Length; fs.Close(); } else if (providername == "AzureBlob") { string connectionstring = Startup.AppSettings["AzureBlob"]; CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionstring); CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient(); CloudBlobContainer container = blobClient.GetContainerReference("templates"); CloudBlockBlob blockBlob = container.GetBlockBlobReference(excelTemplate); //SysIO.Stream templateStream = blockBlob.OpenRead(); //fileinfo.FileByteStream = blockBlob.OpenRead(); SysIO.Stream frs = await blockBlob.OpenReadAsync(); frs.CopyTo(ms); long bytesInStream = ms.Length; bool canread = ms.CanRead; bool canwrite = ms.CanWrite; frs.Close(); } SpreadsheetDocument document = SpreadsheetDocument.Open(ms, true); WorkbookPart wbPart = document.WorkbookPart; Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetname).FirstOrDefault(); if (theSheet == null) { throw new ArgumentException(string.Format("sheetName{0} not found", sheetname)); } WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Worksheet ws = wsPart.Worksheet; Columns columns = ws.Descendants <Columns>().FirstOrDefault(); SheetData sheetData = ws.Descendants <SheetData>().FirstOrDefault(); Row firstRow = sheetData.Descendants <Row>().ElementAt(0); // get first row , line 1 firstRow.DyDescent = 0.3D; Row secondRow = sheetData.Descendants <Row>().ElementAt(1); // get second row , line 2 foreach (Cell cel2nd in secondRow) { if (cel2nd != null) { var cellAdd = cel2nd.CellReference; if (cellAdd == "A2") { styleString = cel2nd.StyleIndex.Value; } if (cellAdd == "H2") { styleDate = cel2nd.StyleIndex.Value; } } } secondRow.Remove(); SharedStringTablePart sharedStringPart = wbPart.GetPartsOfType <SharedStringTablePart>().First(); SharedStringTable sharedStringTable = sharedStringPart.SharedStringTable; foreach (SharedStringItem item in sharedStringTable.Elements <SharedStringItem>()) // read shared string and add to Dictionary { if (item.InnerText != null) { sharedStringDic.Add(item.InnerText, sharedStringId); ++sharedStringId; } } lineIndex = 2; string id; foreach (var entry in filteredData) { Row newRow = new Row() { RowIndex = lineIndex, Spans = new ListValue <StringValue>() { InnerText = "1:22" }, Height = 16.5D, CustomHeight = true, DyDescent = 0.3D }; Cell cell1 = new Cell() { CellReference = "A" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.Plant).ToString(); cell1.CellValue = new CellValue(id); Cell cell2 = new Cell() { CellReference = "B" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.Location).ToString(); cell2.CellValue = new CellValue(id); Cell cell3 = new Cell() { CellReference = "C" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.SerialNumber).ToString(); cell3.CellValue = new CellValue(id); Cell cell4 = new Cell() { CellReference = "D" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.Material).ToString(); cell4.CellValue = new CellValue(id); Cell cell5 = new Cell() { CellReference = "E" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.Description).ToString(); cell5.CellValue = new CellValue(id); Cell cell6 = new Cell() { CellReference = "F" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.CalPlace).ToString(); cell6.CellValue = new CellValue(id); Cell cell7 = new Cell() { CellReference = "G" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString }; cell7.CellValue = new CellValue(entry.CalInterval.ToString()); Cell cell8 = new Cell() { CellReference = "H" + lineIndex.ToString(), StyleIndex = styleDate }; cell8.CellValue = ConvertDateToCellValue(entry.RegisteredDate); Cell cell9 = new Cell() { CellReference = "I" + lineIndex.ToString(), StyleIndex = styleDate }; cell9.CellValue = ConvertDateToCellValue(entry.UserShipDate); Cell cell10 = new Cell() { CellReference = "J" + lineIndex.ToString(), StyleIndex = styleDate }; cell10.CellValue = ConvertDateToCellValue(entry.VenReceiveDate); Cell cell11 = new Cell() { CellReference = "K" + lineIndex.ToString(), StyleIndex = styleDate }; cell11.CellValue = ConvertDateToCellValue(entry.CalDate); Cell cell12 = new Cell() { CellReference = "L" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; string result = ""; if (entry.CalResult == true) { result = "GD"; } if (entry.CalResult == false) { result = "NG"; } id = sharedStringDic.AddToSharedString(result).ToString(); cell12.CellValue = new CellValue(id); Cell cell13 = new Cell() { CellReference = "M" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.VenComment).ToString(); cell13.CellValue = new CellValue(id); Cell cell14 = new Cell() { CellReference = "N" + lineIndex.ToString(), StyleIndex = styleDate }; cell14.CellValue = ConvertDateToCellValue(entry.PlanedShipDate); Cell cell15 = new Cell() { CellReference = "O" + lineIndex.ToString(), StyleIndex = styleDate }; cell15.CellValue = ConvertDateToCellValue(entry.VenShipDate); // todo tool.System_status is null, replace field to represent value Cell cell16 = new Cell() { CellReference = "P" + lineIndex.ToString(), StyleIndex = styleDate }; cell16.CellValue = ConvertDateToCellValue(entry.UserReceiveDate); Cell cell17 = new Cell() { CellReference = "Q" + lineIndex.ToString(), StyleIndex = styleDate }; cell17.CellValue = ConvertDateToCellValue(entry.CcReceiveDate); Cell cell18 = new Cell() { CellReference = "R" + lineIndex.ToString(), StyleIndex = styleDate }; cell18.CellValue = ConvertDateToCellValue(entry.CcUploadDate); Cell cell19 = new Cell() { CellReference = "S" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString }; cell19.CellValue = new CellValue(entry.StdTat.ToString()); Cell cell20 = new Cell() { CellReference = "T" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString }; cell20.CellValue = new CellValue(entry.Tat.ToString()); Cell cell21 = new Cell() { CellReference = "U" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.TatStatus).ToString(); cell21.CellValue = new CellValue(id); Cell cell22 = new Cell() { CellReference = "V" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; string finished = ""; if (entry.Finished == true) { finished = "Done"; } id = sharedStringDic.AddToSharedString(finished).ToString(); cell22.CellValue = new CellValue(id); Cell cell23 = new Cell() { CellReference = "W" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; String yearmonth; if (entry.UserShipDate == null) { yearmonth = ""; } else { yearmonth = String.Format("{0:yyyy-MM}", (DateTime)entry.UserShipDate); } id = sharedStringDic.AddToSharedString(yearmonth).ToString(); cell23.CellValue = new CellValue(id); Cell cell24 = new Cell() { CellReference = "X" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.PMaker).ToString(); cell24.CellValue = new CellValue(id); Cell cell25 = new Cell() { CellReference = "Y" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.PModel).ToString(); cell25.CellValue = new CellValue(id); Cell cell26 = new Cell() { CellReference = "Z" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.PName).ToString(); cell26.CellValue = new CellValue(id); Cell cell27 = new Cell() { CellReference = "AA" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(entry.PSN).ToString(); cell27.CellValue = new CellValue(id); newRow.Append(cell1); newRow.Append(cell2); newRow.Append(cell3); newRow.Append(cell4); newRow.Append(cell5); newRow.Append(cell6); newRow.Append(cell7); newRow.Append(cell8); newRow.Append(cell9); newRow.Append(cell10); newRow.Append(cell11); newRow.Append(cell12); newRow.Append(cell13); newRow.Append(cell14); newRow.Append(cell15); newRow.Append(cell16); newRow.Append(cell17); newRow.Append(cell18); newRow.Append(cell19); newRow.Append(cell20); newRow.Append(cell21); newRow.Append(cell22); newRow.Append(cell23); newRow.Append(cell24); newRow.Append(cell25); newRow.Append(cell26); newRow.Append(cell27); sheetData.AppendChild <Row>(newRow); ++lineIndex; } Int32 count = 0; foreach (string key in sharedStringDic.Keys) { if (count >= sharedStringId) { sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(key))); } ++count; } sharedStringTable.Save(); ws.Save(); wbPart.Workbook.Save(); document.Close(); fileinfo = new FileInfo(); fileinfo.FileName = ""; // file name is added at client (Silverlight) fileinfo.Length = ms.Length; fileinfo.byteArray = new byte[fileinfo.Length + 10]; Array.Copy(ms.GetBuffer(), fileinfo.byteArray, fileinfo.Length); //Array.Resize(ref fileinfo.FileByteStream, (int)ms.Length) ; //fileinfo.Length = ms.Length; return(fileinfo); }
public async Task <FileInfo> GetExcelFile() { Dictionary <string, Int32> sharedStringDic = new Dictionary <string, int>(); const string excelTemplate = "Tools_CalDue_Template.xlsx"; const string sheetname = "Sheet1"; const int styleString = 0; // for this template const int styleDate = 3; // for this template string providername = Startup.AppSettings["StorageProvider"]; string folder = Startup.AppSettings["PdfFoldername"]; FileInfo fileinfo; Int32 sharedStringId = 0; UInt32 lineIndex; SysIO.MemoryStream ms = new SysIO.MemoryStream();; if (providername == "localfile") { string filepath = SysIO.Path.Combine(folder, excelTemplate); //fileinfo.FileByteStream = SysIO.File.Open(filepath, SysIO.FileMode.Open); SysIO.FileStream fs; fs = SysIO.File.Open(filepath, System.IO.FileMode.Open); fs.CopyTo(ms); long bytesInStream = ms.Length; fs.Close(); } else if (providername == "AzureBlob") { string connectionstring = Startup.AppSettings["AzureBlob"]; CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionstring); CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient(); CloudBlobContainer container = blobClient.GetContainerReference("templates"); CloudBlockBlob blockBlob = container.GetBlockBlobReference(excelTemplate); SysIO.Stream frs = await blockBlob.OpenReadAsync(); frs.CopyTo(ms); long bytesInStream = ms.Length; bool canread = ms.CanRead; bool canwrite = ms.CanWrite; frs.Close(); } SpreadsheetDocument document = SpreadsheetDocument.Open(ms, true); WorkbookPart wbPart = document.WorkbookPart; Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetname).FirstOrDefault(); if (theSheet == null) { throw new ArgumentException("sheetName"); } WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id)); Worksheet ws = wsPart.Worksheet; Columns columns = ws.Descendants <Columns>().FirstOrDefault(); SheetData sheetData = ws.Descendants <SheetData>().FirstOrDefault(); Row firstRow = sheetData.Descendants <Row>().ElementAt(0); // get first row , line 1 firstRow.DyDescent = 0.3D; Row secondRow = sheetData.Descendants <Row>().ElementAt(1); // get second row , line 2 secondRow.Remove(); SharedStringTablePart sharedStringPart = wbPart.GetPartsOfType <SharedStringTablePart>().First(); SharedStringTable sharedStringTable = sharedStringPart.SharedStringTable; foreach (SharedStringItem item in sharedStringTable.Elements <SharedStringItem>()) // read shared string and add to Dictionary { if (item.InnerText != null) { sharedStringDic.Add(item.InnerText, sharedStringId); ++sharedStringId; } } lineIndex = 2; string id; // StoreLocation が column "B" と "M" の 2箇所にある foreach (var tool in filteredData) { Row newRow = new Row() { RowIndex = lineIndex, Spans = new ListValue <StringValue>() { InnerText = "1:20" }, Height = 16.5D, CustomHeight = true, DyDescent = 0.3D }; Cell cell1 = new Cell() { CellReference = "A" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Plant).ToString(); cell1.CellValue = new CellValue(id); Cell cell2 = new Cell() { CellReference = "B" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.StoreLocation).ToString(); cell2.CellValue = new CellValue(id); Cell cell3 = new Cell() { CellReference = "C" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.ToolkitSloc).ToString(); cell3.CellValue = new CellValue(id); Cell cell4 = new Cell() { CellReference = "D" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.SerialNumber).ToString(); cell4.CellValue = new CellValue(id); Cell cell5 = new Cell() { CellReference = "E" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Material).ToString(); cell5.CellValue = new CellValue(id); Cell cell6 = new Cell() { CellReference = "F" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Description).ToString(); cell6.CellValue = new CellValue(id); Cell cell7 = new Cell() { CellReference = "G" + lineIndex.ToString(), StyleIndex = styleDate }; if (tool.LatestCalDate != null) { cell7.CellValue = new CellValue(((DateTime)tool.LatestCalDate).ToOADate().ToString()); } else { cell7.CellValue = new CellValue(); } Cell cell8 = new Cell() { CellReference = "H" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.CalStatus).ToString(); cell8.CellValue = new CellValue(id); Cell cell9 = new Cell() { CellReference = "I" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Comment).ToString(); cell9.CellValue = new CellValue(id); Cell cell10 = new Cell() { CellReference = "J" + lineIndex.ToString(), StyleIndex = styleDate }; if (tool.CalDue != null) { cell10.CellValue = new CellValue(((DateTime)tool.CalDue).ToOADate().ToString()); } else { cell10.CellValue = new CellValue(); } Cell cell11 = new Cell() { CellReference = "K" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.CalPlace).ToString(); cell11.CellValue = new CellValue(id); Cell cell12 = new Cell() { CellReference = "L" + lineIndex.ToString(), DataType = CellValues.Number, StyleIndex = styleString }; cell12.CellValue = new CellValue(tool.CalInterval.ToString()); Cell cell13 = new Cell() { CellReference = "M" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.StoreLocation).ToString(); cell13.CellValue = new CellValue(id); Cell cell14 = new Cell() { CellReference = "N" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.SystemStatus).ToString(); cell14.CellValue = new CellValue(id); // todo tool.System_status is null, replace field to represent value Cell cell15 = new Cell() { CellReference = "O" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.UserStatus).ToString(); cell15.CellValue = new CellValue(id); Cell cell16 = new Cell() { CellReference = "P" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Room).ToString(); cell16.CellValue = new CellValue(id); Cell cell17 = new Cell() { CellReference = "Q" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.SuperordEquip).ToString(); cell17.CellValue = new CellValue(id); Cell cell18 = new Cell() { CellReference = "R" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.SortField).ToString(); cell18.CellValue = new CellValue(id); Cell cell19 = new Cell() { CellReference = "S" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.Machine).ToString(); cell19.CellValue = new CellValue(id); Cell cell20 = new Cell() { CellReference = "T" + lineIndex.ToString(), DataType = CellValues.SharedString, StyleIndex = styleString }; id = sharedStringDic.AddToSharedString(tool.ToolkitMachine).ToString(); cell20.CellValue = new CellValue(id); newRow.Append(cell1); newRow.Append(cell2); newRow.Append(cell3); newRow.Append(cell4); newRow.Append(cell5); newRow.Append(cell6); newRow.Append(cell7); newRow.Append(cell8); newRow.Append(cell9); newRow.Append(cell10); newRow.Append(cell11); newRow.Append(cell12); newRow.Append(cell13); newRow.Append(cell14); newRow.Append(cell15); newRow.Append(cell16); newRow.Append(cell17); newRow.Append(cell18); newRow.Append(cell19); newRow.Append(cell20); sheetData.AppendChild <Row>(newRow); ++lineIndex; } Int32 count = 0; foreach (string key in sharedStringDic.Keys) { if (count >= sharedStringId) { sharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(key))); } ++count; } sharedStringTable.Save(); ws.Save(); wbPart.Workbook.Save(); document.Close(); fileinfo = new FileInfo(); fileinfo.FileName = ""; // file name is added at client (Silverlight) fileinfo.Length = ms.Length; fileinfo.byteArray = new byte[fileinfo.Length + 10]; Array.Copy(ms.GetBuffer(), fileinfo.byteArray, fileinfo.Length); //Array.Resize(ref fileinfo.FileByteStream, (int)ms.Length) ; //fileinfo.Length = ms.Length; return(fileinfo); }
/// <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); }
/// <summary> /// Sets the value of a cell, optionally converting it to a string first. /// </summary> /// <param name="value">Value to set in the cell</param> /// <param name="cell">Cell where to set the value</param> /// <param name="sharedStringTable">Structure of the spreadsheet that contains the actual string values</param> /// <param name="stylesheet">Style section of the spreadsheet, containing formatting information</param> /// <param name="writeAsString">When true, the value will be converted to a string before writing to the cell</param> private static void SetCellValue(object value, Cell cell, SharedStringTable sharedStringTable, Stylesheet stylesheet, bool writeAsString) { if (writeAsString) { value = Convert.ToString(value, CultureInfo.InvariantCulture); } if (value is string) { // For strings, we put them in the shared string table and reference by index, just like Office would var tuple = sharedStringTable.Select((v, i) => System.Tuple.Create(v, i)).FirstOrDefault(t => t.Item1.InnerText.Equals(value)); int index; if (tuple == null) { // The string is not in the table, so we add it sharedStringTable.AppendChild(new SharedStringItem(new Text((string)value))); index = (int)sharedStringTable.Count.Value; // Yes, you need to update these manually sharedStringTable.Count++; sharedStringTable.UniqueCount++; } else { index = tuple.Item2; } cell.CellValue = new CellValue(index.ToString(CultureInfo.InvariantCulture)); cell.DataType = CellValues.SharedString; } else if (value is bool) { cell.CellValue = new CellValue(Convert.ToInt32((bool)value).ToString(CultureInfo.InvariantCulture)); cell.DataType = CellValues.Boolean; } else if (value is DateTime) { const int DateTimeFormatId = 22; // Search for a style using the DateTime format id var tuple = stylesheet.CellFormats.Select((v, i) => System.Tuple.Create((CellFormat)v, i)).FirstOrDefault(p => p.Item1.NumberFormatId != null && p.Item1.NumberFormatId.Value == DateTimeFormatId); uint index; if (tuple == null) { // No such style exists so we create one stylesheet.CellFormats.AppendChild(new CellFormat() { NumberFormatId = DateTimeFormatId, FormatId = 0 }); index = stylesheet.CellFormats.Count.Value; // We update this for the sake of consistency stylesheet.CellFormats.Count++; } else { index = (uint)tuple.Item2; } cell.StyleIndex = index; cell.CellValue = new CellValue(((DateTime)value).ToOADate()); cell.DataType = null; } else { // This is for long and double. Also acts as the default behavior for things we do not specially handle. cell.CellValue = new CellValue(Convert.ToString(value, CultureInfo.InvariantCulture)); cell.DataType = null; } }
/// <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); } }