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);
}
}
