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); }
private static void CreateExcelStream <T>(IEnumerable <T> entities, IOrderedEnumerable <ExcelColumnProperty> excelColProperties, Stream excelStream, string sheetName) { SetDefaultNumberFormatId(excelColProperties); using (SpreadsheetDocument doc = SpreadsheetDocument.Create(excelStream, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = doc.AddWorkbookPart(); SharedStringTablePart sharedStringPart = workbookPart.AddNewPart <SharedStringTablePart>(); SharedStringTable sharedStrTbl = new SharedStringTable(); sharedStringPart.SharedStringTable = sharedStrTbl; WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>(); stylePart.Stylesheet = CreateStylesheet(excelColProperties); stylePart.Stylesheet.Save(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); Worksheet worksheet = new Worksheet(); worksheetPart.Worksheet = worksheet; SheetData sheetData = new SheetData(); sheetData.Append(CreateHeader(excelColProperties, sharedStrTbl)); sheetData.Append(CreateContent(entities, excelColProperties, sharedStrTbl)); worksheet.Append(sheetData); worksheet.Save(); Workbook workbook = new Workbook(); workbookPart.Workbook = workbook; FileVersion version = new FileVersion { ApplicationName = "Microsoft Office Excel" }; workbook.Append(version); Sheets sheets = new Sheets(); Sheet sheet = new Sheet { Name = sheetName, SheetId = 1, Id = workbookPart.GetIdOfPart(worksheetPart) }; sheets.Append(sheet); workbook.Append(sheets); sharedStrTbl.Save(); workbook.Save(); doc.Close(); } }
/// <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); }
//获取指定字符串在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); }
public void GenerateReport(IDataModel dataModel) { _dataModel = dataModel; String tempFileName = Path.GetTempFileName(); File.Delete(tempFileName); File.Copy(_templateFile, tempFileName); using (var doc = SpreadsheetDocument.Open(tempFileName, isEditable: true)) { var workSheetPart = doc.WorkbookPart.WorksheetParts.First <WorksheetPart>(); _sharedStringTable = doc.WorkbookPart.SharedStringTablePart.SharedStringTable; PrepareSharedStringTable(); var workSheet = workSheetPart.Worksheet; _sheetData = workSheet.GetFirstChild <SheetData>(); var workBook = doc.WorkbookPart.Workbook; var defName = workBook.DefinedNames.FirstChild; while (defName != null) { if (defName is DefinedName dn) { CheckDefinedName(dn); } defName = defName.NextSibling(); } ProcessData(); if (_sharedStringModified) { _sharedStringTable.Save(); } if (_wrkshtModified) { workSheet.Save(); } doc.Close(); } _resultFile = tempFileName; }
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); }
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); }
public void GenerateReport(IDataModel dataModel) { _dataModel = dataModel; String tempFileName = Path.GetTempFileName(); File.Delete(tempFileName); if (_templateStream != null) { using (var br = new FileStream(tempFileName, FileMode.Create)) { _templateStream.CopyTo(br); } } else if (_templateFile != null) { File.Copy(_templateFile, tempFileName); } else { throw new InteropException("Template file or template stream is required"); } SpreadsheetDocument doc = null; try { // Stream is not working ? doc = SpreadsheetDocument.Open(tempFileName, isEditable: true); var workSheetPart = doc.WorkbookPart.WorksheetParts.First <WorksheetPart>(); _sharedStringTable = doc.WorkbookPart.SharedStringTablePart.SharedStringTable; PrepareSharedStringTable(); var workSheet = workSheetPart.Worksheet; _sheetData = workSheet.GetFirstChild <SheetData>(); var workBook = doc.WorkbookPart.Workbook; var defName = workBook.DefinedNames.FirstChild; while (defName != null) { if (defName is DefinedName dn) { CheckDefinedName(dn); } defName = defName.NextSibling(); } ProcessData(); if (_sharedStringModified) { _sharedStringTable.Save(); } if (_wrkshtModified) { workSheet.Save(); } } finally { if (doc != null) { doc.Close(); } } _resultFile = tempFileName; }
/// <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> /// 将对象保存到文件中。 /// </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); } }