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