void PrepareSharedStringTable()
{
var sslist = _sharedStringTable.Elements <SharedStringItem>().ToList();
for (Int32 i = 0; i < sslist.Count; i++)
{
var ssitem = sslist[i];
String str = ssitem.Text.Text;
if (!str.StartsWith("{"))
{
continue;
}
if (_sharedStringMap == null)
{
_sharedStringMap = new Dictionary <String, SharedStringDef>();
}
if (_sharedStringIndexMap == null)
{
_sharedStringIndexMap = new Dictionary <Int32, SharedStringDef>();
}
var ssd = new SharedStringDef(ssitem, i);
_sharedStringMap.Add(str, ssd);
_sharedStringIndexMap.Add(i, ssd);
}
}
// 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);
}
/// <summary>
/// Returns ALL the cells that contain string value. WARNING: It is super slow...
/// </summary>
/// <param name="worksheet"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <param name="startColumn"></param>
/// <param name="endColumn"></param>
/// <param name="textToSearch"></param>
/// <param name="sharedTable"></param>
/// <returns></returns>
private static List <KeyValuePair <Cell, Row> > FindAllCellsContainsValue(Worksheet worksheet, uint startRow, uint endRow, uint startColumn, uint endColumn, string textToSearch, SharedStringTable sharedTable)
{
List <KeyValuePair <Cell, Row> > CellsContainsValue = new List <KeyValuePair <Cell, Row> >();
for (uint rowIndex = startRow; rowIndex <= endRow; ++rowIndex)
{
for (uint columnIndex = startColumn; columnIndex <= endColumn; ++columnIndex)
{
Cell cell = GetCell(worksheet, GetExcelColumnName(columnIndex), rowIndex);
if (cell != null && cell.CellValue != null)
{
int id = -1;
if (Int32.TryParse(cell.InnerText, out id))
{
SharedStringItem item = sharedTable.Elements <SharedStringItem>().ElementAt(id);
if ((item.Text != null && item.Text.Text.Contains(textToSearch)) ||
(item.InnerText != null && item.InnerText.Contains(textToSearch)) ||
(item.InnerXml != null && item.InnerXml.Contains(textToSearch)))
{
CellsContainsValue.Add(new KeyValuePair <Cell, Row>(cell, GetRow(worksheet, rowIndex)));
}
}
}
}
}
return(CellsContainsValue);
}
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);
}
public static int IndexOfSharedString(SpreadsheetDocument spreadsheet, string stringItem)
{
SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
bool found = false;
int index = 0;
foreach (SharedStringItem sharedString in sharedStringTable.Elements <SharedStringItem>())
{
if (sharedString.InnerText == stringItem)
{
found = true;
break;;
}
index++;
}
return(found ? index : -1);
}
public DocumentSharedStrings(WorkbookPart wpart)
{
this._wpart = wpart;
this._indexLookup = new Dictionary <string, uint>();
this._ssPart = this._wpart.SharedStringTablePart;
if (this._ssPart != null)
{
SharedStringTable sharedStringTable = this._ssPart.SharedStringTable;
uint num = 0;
foreach (SharedStringItem item in sharedStringTable.Elements <SharedStringItem>())
{
string text = item.Text.Text;
this._indexLookup[text] = num;
num++;
}
}
}
public SharedStringLookup(SpreadsheetDocument doc)
{
dict = new Dictionary <int, string>();
SharedStringTablePart sharedStringPart = doc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
if (sharedStringPart != null)
{
SharedStringTable table = sharedStringPart.SharedStringTable;
int index = 0;
foreach (SharedStringItem si in table.Elements <SharedStringItem>())
{
dict.Add(index, si.InnerText);
index++;
}
;
}
}
public DocumentSharedStrings(WorkbookPart wpart)
{
_wpart = wpart;
_indexLookup = new Dictionary <string, uint>();
if ((_ssPart = _wpart.SharedStringTablePart) != null)
{
SharedStringTable ssTable = _ssPart.SharedStringTable;
uint idx = 0;
foreach (var sharedStr in ssTable.Elements <SharedStringItem>())
{
string valueStr = sharedStr.Text.Text;
_indexLookup[valueStr] = idx;
idx++;
}
}
}
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>
/// Извлекает основную информацию о студентах из файла с контингентом
/// </summary>
/// <param name="sharedStringTable">Таблица разделяемых строк</param>
/// <param name="worksheet">Рабочий лист</param>
/// <returns>Набор сущностей, которые описывают каждого студента в контингенте</returns>
public HashSet <Student> Parse(SharedStringTable sharedStringTable, Worksheet worksheet)
{
var students = new HashSet <Student>();
var sharedStringItems = new Dictionary <string, string>();
var sharedItems = sharedStringTable.Elements <SharedStringItem>().ToList();
for (var i = 0; i < sharedItems.Count; ++i)
{
sharedStringItems.Add(i.ToString(), sharedItems[i].InnerText);
}
var sheetData = worksheet.Elements <SheetData>().First();
var rows = sheetData.Elements <Row>().ToList();
for (var i = 1; i < rows.Count() - 1; ++i)
{
var j = 0;
var cells = rows[i].Elements <Cell>().ToList();
var surname = sharedStringItems[cells[j].CellValue.InnerText];
var name = sharedStringItems[cells[j + 1].CellValue.InnerText];
var patronymic = sharedStringItems[cells[j + 2].CellValue.InnerText];
var group = sharedStringItems[cells[j + 3].CellValue.InnerText].Split(' ')[0];
var programmeCode = ParseProgrammeCode(sharedStringItems[cells[j + 4].CellValue.InnerText]);
var specialization = sharedStringItems[cells[j + 5].CellValue.InnerText];
var educationState = sharedStringItems[cells[j + 6].CellValue.InnerText];
var levelOfStudy = sharedStringItems[cells[j + 7].CellValue.InnerText];
var curriculumCode = ParseCurriculumCode(sharedStringItems[cells[j + 8].CellValue.InnerText]);
var educationalPeriod = sharedStringItems[cells[j + 10].CellValue.InnerText];
var course = sharedStringItems[cells[j + 11].CellValue.InnerText];
var status = sharedStringItems[cells[j + 13].CellValue.InnerText];
var averageScore = cells[j + 15].CellValue.InnerText;
var yearOfAdmission = sharedStringItems[cells[j + 18].CellValue.InnerText];
var student = new Student(surname, name, patronymic, levelOfStudy,
programmeCode, curriculumCode, status, specialization, course, group,
yearOfAdmission, averageScore, educationState);
students.Add(student);
}
return(students);
}
//获取指定字符串在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);
}
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);
}
static string ReadExcels(string path)
{
using (SpreadsheetDocument sp = SpreadsheetDocument.Open(path, true))
{
IEnumerable <Sheet> sheets = sp.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
List <JObject> jObjects = new List <JObject>();
if (sheets.Count() == 0)
{
return("");
}
string relationId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)sp.WorkbookPart.GetPartById(relationId);
SharedStringTable sharedStringTable = sp.WorkbookPart.SharedStringTablePart.SharedStringTable;
IEnumerable <Row> rows = worksheetPart.Worksheet.GetFirstChild <SheetData>().Elements <Row>().Where(r => r.RowIndex > 1);
Row row1 = worksheetPart.Worksheet.GetFirstChild <SheetData>().Elements <Row>().Where(r => r.RowIndex == 1).First();
IEnumerable <Cell> cells1 = row1.Elements <Cell>();
int count = 0;
foreach (var row in rows)
{
// Console.WriteLine("行" + int.Parse(row.RowIndex));
IEnumerable <Cell> cells = row.Elements <Cell>();
//var dd = JsonConvert.SerializeObject(cells);
//Console.WriteLine(dd);
if (sharedStringTable == null)
{
return("");
}
JObject jObject = new JObject();
foreach (var item in cells)
{
var cell2 = cells1.Skip(count).Take(1).First();
if (item.DataType != null && item.DataType.Value == CellValues.SharedString)
{
SharedStringItem item1 = sharedStringTable.Elements <SharedStringItem>().ElementAt(int.Parse(item.CellValue.Text));
if (cell2.DataType != null && cell2.DataType.Value == CellValues.SharedString)
{
SharedStringItem item2 = sharedStringTable.Elements <SharedStringItem>().ElementAt(int.Parse(cell2.CellValue.Text));
// Console.WriteLine(item1.Text.Text);
jObject.Add(new JProperty(item2.Text.Text, item1.Text.Text));
}
else
{
jObject.Add(new JProperty(cell2.CellValue.Text, item1.Text.Text));
}
count += 1;
continue;
}
if (cell2.DataType != null && cell2.DataType.Value == CellValues.SharedString)
{
SharedStringItem item2 = sharedStringTable.Elements <SharedStringItem>().ElementAt(int.Parse(cell2.CellValue.Text));
// Console.WriteLine(item1.Text.Text);
jObject.Add(new JProperty(item2.Text.Text, item.CellValue == null ? "" : item.CellValue.Text));
}
else
{
jObject.Add(new JProperty(cell2.CellValue.Text, item.CellValue.Text));
}
// Console.WriteLine(item.CellValue.Text);
//jObjects.Add(new JObject(new JProperty( "gg",item.CellValue.Text)));
count += 1;
}
jObjects.Add(jObject);
count = 0;
}
var dd = "{\"total\":" + jObjects.Count + ",\"rows\":" + JsonConvert.SerializeObject(jObjects) + "}";
return(dd);
}
}
/// <summary>
/// 指定された Excelのテンプレートファイルを読み込む
/// </summary>
/// <remarks>テンプレートは、connectionString [KSCM_Bolb] の "templates" という container から読み込む。</remarks>
/// <param name="filename">"tempates"コンテナの中のテンプレートのファイル名</param>
/// <param name="sheetname">テンプレート中で使用するシート名</param>
public async Task <long> LoadTemplate(string filename, string sheetname)
{
_ms = new SysIO.MemoryStream();
//var ms2 = new SysIO.MemoryStream();
string connectionstring = Startup.AppSettings["AzureBlob"];
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionstring);
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
CloudBlobContainer container = blobClient.GetContainerReference("templates");
CloudBlockBlob blockBlob = container.GetBlockBlobReference(filename);
//bool cw = _ms.CanWrite;
await blockBlob.DownloadToStreamAsync(_ms);
long bytesInStream = _ms.Length;
bool canread = _ms.CanRead;
bool canwrite = _ms.CanWrite;
_document = SpreadsheetDocument.Open(_ms, true);
_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));
_ws = wsPart.Worksheet;
Columns columns = _ws.Descendants <Columns>().FirstOrDefault();
_sheetData = _ws.Descendants <SheetData>().FirstOrDefault();
var rows = _sheetData.Descendants <Row>();
int nrows = rows.Count();
Row firstRow = _sheetData.Descendants <Row>().ElementAt(0); // get first row , line 1
firstRow.DyDescent = 0.3D;
for (int i = nrows - 1; i > 0; --i)
{
Row rowtodelete = _sheetData.Descendants <Row>().ElementAt(i); // get line i
rowtodelete.Remove();
}
_sharedStringDic = new Dictionary <string, int>();
SharedStringTablePart sharedStringPart = _wbPart.GetPartsOfType <SharedStringTablePart>().First();
_sharedStringTable = sharedStringPart.SharedStringTable;
_sharedStringId = 0;
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 = StartLine;
_colid = "A";
_currentRow = new Row()
{
RowIndex = _lineIndex, Spans = new ListValue <StringValue>()
{
InnerText = "1:20"
}, Height = 16.5D, CustomHeight = true, DyDescent = 0.3D
};
StyleDic = new Dictionary <string, uint>();
return(bytesInStream);
}
private static string GetSharedStringItem(SharedStringTable sharedStringTable, int index)
{
var item = sharedStringTable.Elements <SharedStringItem>().ElementAt(index);
return(item.InnerText);
}