public static int GetOrInsertItem(this SharedStringTablePart shareStringPart, string content, bool isXml)
{
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements <SharedStringItem>())
{
if ((!isXml && item.InnerText == content) || (isXml && item.OuterXml == content))
{
return(i);
}
i++;
}
if (isXml)
{
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(content));
}
else
{
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(content)));
}
shareStringPart.SharedStringTable.Save();
return(i);
}
// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements <SharedStringItem>())
{
if (item.InnerText == text)
{
return(i);
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text)));
shareStringPart.SharedStringTable.Save();
return(i);
}
private static int InsertSharedStringItem(string text, SharedStringTablePart sharedStringTablePart)
{
if (sharedStringTablePart.SharedStringTable == null)
{
sharedStringTablePart.SharedStringTable = new SharedStringTable();
sharedStringTablePart.SharedStringTable.Count = 1;
sharedStringTablePart.SharedStringTable.UniqueCount = 1;
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in sharedStringTablePart.SharedStringTable.Elements <SharedStringItem>())
{
if (item.InnerText == text)
{
return(i);
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
sharedStringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
sharedStringTablePart.SharedStringTable.Save();
return(i);
}
public ExcelReader(Stream stream)
{
_provider = new CSharpCodeProvider();
_stream = stream;
_document = SpreadsheetDocument.Open(_stream, false);
_wbPart = _document.WorkbookPart;
var sheets = _wbPart
.Workbook
.Descendants <DocumentFormat.OpenXml.Spreadsheet.Sheet>()
.ToList();
if (sheets.Count != 1)
{
throw new Exception("Multiple sheets not supported.");
}
_sheet = sheets.First();
_wsPart = _wbPart.GetPartById(_sheet.Id) as WorksheetPart;
if (_wsPart == null)
{
throw new Exception("Excel error: WorksheetPart not found.");
}
_cells = _wsPart
.Worksheet
.Descendants <Cell>()
.ToArray();
_stringTable = _wbPart
.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
}
private static void ReadExcelRows(List <Row> rows, SharedStringTablePart sharedStringTable, System.Data.DataTable dt)
{
for (var i = 0; i < rows.Where(x => x.RowIndex.Value > 3).GetRowsCount(); i++)
{
var row = dt.NewRow();
int rowIndex = 4 + i;
var cells = rows.GetCells(rowIndex);
row[A] = cells.GetCellValue(A + rowIndex, sharedStringTable);
row[B] = cells.GetCellValue(B + rowIndex, sharedStringTable);
row[C] = cells.GetCellValue(C + rowIndex, sharedStringTable);
row[D] = cells.GetCellValue(D + rowIndex, sharedStringTable);
row[F] = cells.GetCellValue(F + rowIndex, sharedStringTable);
row[G] = cells.GetCellValue(G + rowIndex, sharedStringTable);
var eVal = cells.GetCellValue(E + rowIndex, sharedStringTable);
DateTime timeVal;
double doubleVal;
DateTime.TryParse(eVal, out timeVal);
double.TryParse(eVal, out doubleVal);
if (timeVal > DateTime.MinValue)
{
row[E] = timeVal;
}
else if (doubleVal > 0)
{
row[E] = excelStartTime.AddDays(doubleVal);
}
else
{
row[E] = "时间格式不正确";
}
dt.Rows.Add(row);
}
}
public IEnumerable <Cell> Cells()
{
XNamespace s = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
SpreadsheetDocument doc = (SpreadsheetDocument)Parent.OpenXmlPackage;
SharedStringTablePart sharedStringTable = doc.WorkbookPart.SharedStringTablePart;
return
(from cell in this.RowElement.Elements(s + "c")
let cellType = (string)cell.Attribute("t")
let sharedString = cellType == "s" ?
sharedStringTable
.GetXDocument()
.Root
.Elements(s + "si")
.Skip((int)cell.Element(s + "v"))
.First()
.Descendants(s + "t")
.StringConcatenate(e => (string)e)
: null
let column = (string)cell.Attribute("r")
select new Cell(this)
{
CellElement = cell,
Row = (string)RowElement.Attribute("r"),
Column = column,
ColumnId = column.Split('0', '1', '2', '3', '4', '5', '6', '7', '8', '9').First(),
Type = (string)cell.Attribute("t"),
Formula = (string)cell.Element(s + "f"),
Value = (string)cell.Element(s + "v"),
SharedString = sharedString
});
}
// Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text
// and inserts it into the SharedStringTablePart. If the item already exists, returns its index.
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
// If the part does not contain a SharedStringTable, create it.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements <SharedStringItem>())
{
if (item.InnerText == text)
{
// The text already exists in the part. Return its index.
return(i);
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return(i);
}
private Row CreateTotalForRow <T>(SharedStringTablePart shareStringPart, T reportsGridView)
{
var totalForRow = new Row();
// Row TOTAL FOR Header Name.
var propByDefaultGrouping = PropsGroupByAndTotalTimes.FirstOrDefault(x => !x.PropertyType.GetTypeInfo().IsGenericType&& IsPropByDefaultGrouping(x.Name));
var valueSingleFromProp = GetValueSingleFromProp(propByDefaultGrouping, reportsGridView);
valueSingleFromProp = UpdateDateFormatForValue(valueSingleFromProp, propByDefaultGrouping).ToUpper();
totalForRow = CreateRowTotalFor(totalForRow, valueSingleFromProp);
//totalForRow = CreateEmptyCellsAfterTotalTime(totalForRow);
AddEmptyCellsBeforeTotalForCell(totalForRow);
// Row TOTAL FOR Header Values:
var listOfPropsTotalActTime = PropsGroupByAndTotalTimes.Where(x => !x.PropertyType.GetTypeInfo().IsGenericType&& IsPropTotalOrActualTime(x.Name));
foreach (var propTimeFor in listOfPropsTotalActTime)
{
var valueSingleFromTotalActTime = GetValueSingleFromProp(propTimeFor, reportsGridView);
valueSingleFromTotalActTime = UpdateTimeFormatForValue(propTimeFor, valueSingleFromTotalActTime);
var cellTotalFor = new Cell();
cellTotalFor = GetCellValue(shareStringPart, valueSingleFromTotalActTime, cellTotalFor);
//SetCellStyleForHeaders(propTimeFor, cellTotalFor);
cellTotalFor.StyleIndex = 3;
totalForRow.Append(cellTotalFor);
}
return(totalForRow);
}
public OpenXMLExcel(Stream stream, bool bCreate)
{
if (bCreate == true)
{
spreadSheet = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);
if (spreadSheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadSheet.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
shareStringPart.SharedStringTable = new SharedStringTable();
shareStringPart.SharedStringTable.Count = 1;
shareStringPart.SharedStringTable.UniqueCount = 1;
CurrentWorksheetPart = worksheetPart;
}
else
{
spreadSheet = SpreadsheetDocument.Open(stream, true);
shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
CurrentWorksheetPart = spreadSheet.WorkbookPart.WorksheetParts.First();
}
}
/// <summary>
/// Запись строк в ячейки.
/// </summary>
/// <param name="listImportData">Список импортируемых записей.</param>
public void InsertText(List <ArrayList> listImportData)
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(DocPath, true))
{
SharedStringTablePart shareStringPart;
if (doc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = doc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = doc.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
WorksheetPart worksheetPart = GetWorksheetPartByName(doc, WorksheetName);
Worksheet sheet = worksheetPart.Worksheet;
foreach (var importItems in listImportData)
{
int index = InsertSharedStringItem(Convert.ToString(importItems[0]), shareStringPart);
Cell cell = InsertCellInWorksheet(Convert.ToString(importItems[1]), Convert.ToUInt32(importItems[2]), worksheetPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
worksheetPart.Worksheet.Save();
}
}
/// <summary>
/// Créé une nouvelle instance de ExcelTemplateBuilder.
/// </summary>
/// <param name="templateStream">Stream du fichier édité.</param>
/// <param name="sheetIndex">Index de la feuille du classeur à ouvrir, en base 1.</param>
private ExcelTemplateBuilder(Stream templateStream, int sheetIndex)
{
/* Copie le stream du template dans un stream en mémoire. */
_documentStream = new MemoryStream();
templateStream.Seek(0, SeekOrigin.Begin);
templateStream.CopyTo(_documentStream);
_documentStream.Seek(0, SeekOrigin.Begin);
/* Ouvre le stream dans un ExcelDocument en édition. */
_excelDocument = SpreadsheetDocument.Open(_documentStream, true);
/* Sélectionne les parts. */
var worksheetParts = _excelDocument.WorkbookPart.WorksheetParts;
var internalSheetIndex = worksheetParts.Count() - sheetIndex; // Les feuilles sont stockées dans l'ordre inverse d'affichage.
_worksheetPart = worksheetParts.ElementAt(internalSheetIndex);
_worksheet = _worksheetPart.Worksheet;
_shareStringPart = _excelDocument.WorkbookPart.SharedStringTablePart;
/* Lit les cellules fusionnées. */
ReadMergeCells();
/* Focus sur la première cellule */
this.SetFocus("A1");
}
/// <summary>
/// Получить структуры данных из файла Excel.
/// </summary>
/// <returns>Матрица полей.</returns>
public IEnumerable <List <string> > GetDataFromExcel()
{
var result = new List <List <string> >();
try
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(DocPath, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
WorksheetPart worksheetPart = null;
try
{
worksheetPart = GetWorksheetPartByName(doc, WorksheetName);
}
catch (Exception ex)
{
throw new Exception(string.Format("Не удалось обработать файл xlsx. Не найдена страница с наименованием \"{0}\". Подробности: {1}", WorksheetName, ex.Message), ex);
}
Worksheet sheet = worksheetPart.Worksheet;
var cells = sheet.Descendants <Cell>();
var rows = sheet.Descendants <Row>();
var maxElements = rows.First().Elements <Cell>().Count();
var rowNum = 0;
foreach (Row row in rows)
{
rowNum++;
// HACK OpenText пропускает пустые ячейки. Будем импровизировать. С помощью функции CellReferenceToIndex будем определять "правильный" порядковый номер ячейки и заполнять массив.
var rowData = new string[maxElements];
for (var i = 0; i < maxElements; i++)
{
rowData[i] = string.Empty;
}
foreach (Cell c in row.Elements <Cell>())
{
if (c.CellValue != null)
{
var innerText = c.DataType != null && c.DataType == CellValues.SharedString ?
sst.ChildElements[int.Parse(c.CellValue.Text)].InnerText :
c.CellValue.InnerText;
rowData[CellReferenceToIndex(c)] = innerText;
}
}
// Если максимальное значение в наборе данных - пустое значение, значит строка не содержит данных.
if (!string.IsNullOrWhiteSpace(rowData.Max()))
{
result.Add(rowData.ToList());
}
}
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Не удалось обработать файл xlsx. Обратитесь к администратору системы. Подробности: {0}", ex.Message), ex);
}
return(result);
}
public static string GetCellValue(this Worksheet worksheet, Cell cell, SharedStringTablePart sharedTablePart)
{
if (cell == null)
{
throw new NullReferenceException("Please confirm Cell object is null");
}
//当单元格数值为空时返回string.Empty
string cellValue = string.Empty;
if (cell.ChildElements.Count <= 0)
{
return(cellValue);
}
//获取Cell对象的InnerText
string cellInnerText = cell.CellValue.InnerText;
cellValue = cellInnerText;
if (sharedTablePart == null)
{
return(cellInnerText);
}
//获取SharedStringTable对象
var sharedTable = sharedTablePart.SharedStringTable;
try
{ //数据类型处理
EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues> cellType = cell.DataType;
if (cellType != null)
{
switch (cellType.Value)
{
case DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString:
int cellIndex = int.Parse(cellInnerText);
cellValue = sharedTable.ChildElements[cellIndex].InnerText;
break;
case DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean:
cellValue = (cellInnerText == "1") ? "TRUE" : "FALSE";
break;
case DocumentFormat.OpenXml.Spreadsheet.CellValues.Date:
cellValue = Convert.ToDateTime(cellInnerText).ToString();;
break;
case DocumentFormat.OpenXml.Spreadsheet.CellValues.Number:
cellValue = Convert.ToDecimal(cellInnerText).ToString();;
break;
default:
cellValue = cellInnerText;
break;
}
}
}
catch
{
cellValue = "N/A";
}
return(cellValue);
}
private static string GetCellValue(Cell cell, SharedStringTablePart stringTable)
{
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.Boolean:
return(cell.InnerText == "0" ? "FALSE" : "TRUE");
case CellValues.SharedString:
return(stringTable.SharedStringTable.ElementAt(int.Parse(cell.InnerText)).InnerText);
case CellValues.Date:
return(DateTime.FromOADate(Convert.ToDouble(cell.InnerText)).ToShortDateString());
case CellValues.Number:
case CellValues.Error:
case CellValues.InlineString:
case CellValues.String:
default:
return(cell.InnerText);
}
}
return(cell.InnerText);
}
/// <summary>
/// Generates content of sharedStringTablePart
/// </summary>
/// <param name="sharedStringTablePart">SharedStringTablePart Object</param>
/// <param name="table">DataTable Object</param>
private void CreateSharedStringTablePart(SharedStringTablePart sharedStringTablePart, DataTable table)
{
UInt32Value stringCount = Convert.ToUInt32(table.Rows.Count) + Convert.ToUInt32(table.Columns.Count);
// Initialize an instance of SharedString Table
SharedStringTable sharedStringTable = new SharedStringTable()
{
Count = stringCount,
UniqueCount = stringCount
};
// Add columns of DataTable to sharedString iteam
for (int columnIndex = 0; columnIndex < table.Columns.Count; columnIndex++)
{
SharedStringItem sharedStringItem = new SharedStringItem();
Text text = new Text();
text.Text = table.Columns[columnIndex].ColumnName;
sharedStringItem.Append(text);
// Add sharedstring item to sharedstring Table
sharedStringTable.Append(sharedStringItem);
}
// Add rows of DataTable to sharedString iteam
for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
{
SharedStringItem sharedStringItem = new SharedStringItem();
Text text = new Text();
text.Text = table.Rows[rowIndex][0].ToString();
sharedStringItem.Append(text);
sharedStringTable.Append(sharedStringItem);
}
sharedStringTablePart.SharedStringTable = sharedStringTable;
}
/// <summary>
/// Saves the spreadsheet and all related document parts.
/// </summary>
public static void Save(SpreadsheetDocument spreadsheet)
{
//Save all worksheets
foreach (WorksheetPart worksheetPart in spreadsheet.WorkbookPart.WorksheetParts)
{
SetRowSpans(worksheetPart);
SetWorksheetDimension(worksheetPart);
worksheetPart.Worksheet.Save();
}
//Save the style information
WorkbookStylesPart styles = SpreadsheetReader.GetWorkbookStyles(spreadsheet);
styles.Stylesheet.Save();
//Save the shared string table part
if (spreadsheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
SharedStringTablePart shareStringPart = spreadsheet.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
shareStringPart.SharedStringTable.Save();
}
//Save the workbook
spreadsheet.WorkbookPart.Workbook.Save();
}
/// <summary>
/// Create SpreadSheet Document and Fill datas
/// </summary>
/// <param name="spreadsheetdoc">SpreadSheet Document</param>
/// <param name="table">DataTable Object</param>
private void CreateExcelFile(SpreadsheetDocument spreadsheetdoc, DataTable table)
{
// Initialize an instance of WorkbookPart
WorkbookPart workBookPart = spreadsheetdoc.AddWorkbookPart();
// Create WorkBook
CreateWorkBookPart(workBookPart);
// Add WorkSheetPart into WorkBook
WorksheetPart worksheetPart1 = workBookPart.AddNewPart <WorksheetPart>("rId1");
CreateWorkSheetPart(worksheetPart1, table);
// Add SharedStringTable Part into WorkBook
SharedStringTablePart sharedStringTablePart = workBookPart.AddNewPart <SharedStringTablePart>("rId2");
CreateSharedStringTablePart(sharedStringTablePart, table);
// Add WorkbookStyles Part into Workbook
WorkbookStylesPart workbookStylesPart = workBookPart.AddNewPart <WorkbookStylesPart>("rId3");
CreateWorkBookStylesPart(workbookStylesPart);
// Save workbook
workBookPart.Workbook.Save();
}
private string GetStringValue(Cell c, SpreadsheetDocument document)
{
// If the content of the first cell is stored as a shared string, get the text of the first cell
// from the SharedStringTablePart and return it. Otherwise, return the string value of the cell.
if (c.DataType != null && c.DataType.Value ==
CellValues.SharedString)
{
SharedStringTablePart shareStringPart = document.WorkbookPart.
GetPartsOfType <SharedStringTablePart>().First();
SharedStringItem[] items = shareStringPart.
SharedStringTable.Elements <SharedStringItem>().ToArray();
return(items[int.Parse(c.CellValue.Text)].InnerText);
}
else
{
if (c.CellValue != null)
{
return(c.CellValue.Text);
}
else
{
return(c.InnerText);
}
}
}
// Add a new cell
//public static bool AddCell(SharedStringTablePart stringTablePart, Worksheet ws, WorkbookPart wbPart, string addressName, string value, UInt32Value styleIndex, bool isString)
public static bool AddCell(SheetData sheetData, SharedStringTablePart stringTablePart, Worksheet ws, WorkbookPart wbPart, Row theRow, int col, string value, UInt32Value styleIndex, bool isString)
{
// Add a new cell to theRow
Cell _cell = new Cell();
_cell.CellReference = theRow.RowIndex.ToString() + IntToLetters(col);
theRow.InsertBefore(_cell, null);
if (isString)
{
// Either retrieve the index of an existing string,
// or insert the string into the shared string table
// and get the index of the new item.
int stringIndex = InsertSharedStringItem(stringTablePart.SharedStringTable, wbPart, value);
_cell.CellValue = new CellValue(stringIndex.ToString());
_cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
else
{
_cell.CellValue = new CellValue(value);
_cell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
_cell.StyleIndex = styleIndex;
return(true);
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument document, List <ProjectFile> projectFiles)
{
ExtendedFilePropertiesPart extendedFileProperties = document.AddNewPart <ExtendedFilePropertiesPart>("rId3");
GenerateExtendedFilePropertiesContent(extendedFileProperties);
WorkbookPart workbook = document.AddWorkbookPart();
GenerateWorkbookContent(workbook);
WorkbookStylesPart workbookStyles = workbook.AddNewPart <WorkbookStylesPart>("rId2");
GenerateWorkbookStylesContent(workbookStyles);
//from this point is the content
WorksheetPart worksheet = workbook.AddNewPart <WorksheetPart>("rId1");
GenerateWorksheetContent(worksheet, projectFiles);
SharedStringTablePart sharedStringTablePart = workbook.AddNewPart <SharedStringTablePart>("rId4");
GenerateSharedStringTablePartContent(sharedStringTablePart);
SetPackageProperties(document);
}
/// <summary>
/// Gets the worksheet columns
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public List <string> GetWorksheetColumns(string sheetName)
{
List <string> columns = new List <string>();
List <string> exclusionColumns = new List <string>()
{
"Attachment", "GUID", "Path", "Version"
};
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(this.fileStream, false))
{
Worksheet sheet = GetWorkSheet(spreadsheet.WorkbookPart, sheetName);
SheetData sd = (SheetData)sheet.Where(x => x.LocalName == "sheetData").First();
SharedStringTablePart sstp = spreadsheet.WorkbookPart.SharedStringTablePart;
//columns!
Row header = sd.Elements <Row>().First();
foreach (Cell c in header.Elements <Cell>())
{
string cellValue = GetValue(c, sstp).Trim();
if (!exclusionColumns.Contains(cellValue))
{
columns.Add(cellValue);
}
}
}
return(columns);
}
protected static uint FindRowIndexByMarker(SpreadsheetDocument document, string marker)
{
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
Sheets sheets = workbookPart.Workbook.Sheets;
Sheet sheet = sheets.GetFirstChild <Sheet>();
SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().FirstOrDefault();
SharedStringTablePart sharedStringPart = workbookPart.SharedStringTablePart;
uint firstRow = 1;
int startMarkerId = ExcelUtils.FindStringId(sharedStringPart, marker);
foreach (Row rr in sheetData.Elements <Row>())
{
Cell cc = rr.Elements <Cell>().FirstOrDefault();
if (cc != null)
{
if ((cc.DataType == "s") && (Convert.ToInt32(cc.CellValue.Text) == startMarkerId))
{
break;
}
}
firstRow++;
}
return(firstRow);
}
static string GetCellValue(SpreadsheetDocument document, Cell cell)
{
string strErrorMsg = string.Empty;
string value = string.Empty;
try
{
if (cell != null)
{
SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
if (cell.CellValue != null)
{
value = cell.CellValue.InnerXml;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
if (stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)] != null)
{
return(stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText);
}
else
{
return(value);
}
}
}
}
return(string.Empty);
}
catch (Exception e)
{
strErrorMsg = e.Message;
LogClass.RecordException(e);
}
return(value);
}
public override List <Dataset> Parse(SpreadsheetDocument document)
{
List <Dataset> datasets = null;
try
{
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();
Sheets sheets = workbookPart.Workbook.Sheets;
Sheet sheet = sheets.GetFirstChild <Sheet>();
SheetData sheetData = worksheetPart.Worksheet.Descendants <SheetData>().FirstOrDefault();
SharedStringTablePart sharedStringPart = workbookPart.SharedStringTablePart;
string startMarker = "Счет";
uint firstRow = 4;
int lastRow = sheetData.Elements <Row>().Count();
datasets = new List <Dataset>();
for (uint i = firstRow; i < lastRow + 1; i += 1)
{
Console.WriteLine("Строка {0}", i);
Dataset ds = new Dataset();
Row r = ExcelUtils.GetRow(sheetData, i);
var q = r.Elements <Cell>().Where(c => c.CellValue != null).ToList();
ds.Invoice = ExcelUtils.FindStringValue(sharedStringPart, Convert.ToInt32(ExcelUtils.GetCellText(r, "A")));
ds.Name = ExcelUtils.FindStringValue(sharedStringPart, Convert.ToInt32(ExcelUtils.GetCellText(r, "B")));
ds.InventoryNumber = ExcelUtils.GetCell(r, "C").DataType == "s" ?
ExcelUtils.FindStringValue(sharedStringPart, Convert.ToInt32(ExcelUtils.GetCellText(r, "C"))).Trim(' ') :
ExcelUtils.GetCellText(r, "C").Trim(' ');
ds.KFO = ExcelUtils.GetCellText(r, "D").Trim(' ');
ds.startPeriodBalance.debit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "E").Replace('.', ','));
ds.startPeriodBalance.debit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "F").Replace('.', ','));
ds.startPeriodBalance.credit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "G").Replace('.', ','));
ds.startPeriodBalance.credit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "H").Replace('.', ','));
ds.turnover.debit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "I").Replace('.', ','));
ds.turnover.debit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "J").Replace('.', ','));
ds.turnover.credit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "K").Replace('.', ','));
ds.turnover.credit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "L").Replace('.', ','));
ds.endPeriodBalance.debit.sum = Convert.ToDouble(ExcelUtils.GetCellText(r, "M").Replace('.', ','));
ds.endPeriodBalance.debit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "N").Replace('.', ','));
ds.endPeriodBalance.credit.sum = Convert.ToInt32(ExcelUtils.GetCellText(r, "O").Replace('.', ','));
ds.endPeriodBalance.credit.amount = Convert.ToInt32(ExcelUtils.GetCellText(r, "P").Replace('.', ','));
datasets.Add(ds);
}
return(datasets);
}
catch (Exception e)
{
Debug.Print(e.Message);
Debug.Print(e.StackTrace);
return(null);
}
finally
{
GC.Collect();
}
}
/// <summary>
/// Creates the shared string table part.
/// </summary>
/// <param name="sharedStringTablePart">The shared string table part.</param>
/// <param name="sheetData">The sheet data.</param>
private void CreateSharedStringTablePart(SharedStringTablePart sharedStringTablePart, DataTable sheetData)
{
UInt32Value stringCount = Convert.ToUInt32(sheetData.Rows.Count) + Convert.ToUInt32(sheetData.Columns.Count);
SharedStringTable sharedStringTable = new SharedStringTable()
{
Count = stringCount,
UniqueCount = stringCount
};
for (int columnIndex = 0; columnIndex < sheetData.Columns.Count; columnIndex++)
{
SharedStringItem sharedStringItem = new SharedStringItem();
Text text = new Text();
text.Text = sheetData.Columns[columnIndex].ColumnName;
sharedStringItem.Append(text);
sharedStringTable.Append(sharedStringItem);
}
for (int rowIndex = 0; rowIndex < sheetData.Rows.Count; rowIndex++)
{
SharedStringItem sharedStringItem = new SharedStringItem();
Text text = new Text();
text.Text = sheetData.Rows[rowIndex][0].ToString();
sharedStringItem.Append(text);
sharedStringTable.Append(sharedStringItem);
}
sharedStringTablePart.SharedStringTable = sharedStringTable;
}
private async Task <IEnumerable <DataRow> > ExtractDataFromDocumentAsync(SpreadsheetDocument document, IEnumerable <FilterValue> filterValues, string sheetName = "")
{
WorkbookPart workbookPart = document.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType <SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
var worksheetPart = GetWorksheetPartByName(document, sheetName);
if (worksheetPart == null)
{
worksheetPart = workbookPart.WorksheetParts.Last();
}
Worksheet sheet = worksheetPart.Worksheet;
var rows = sheet
.Descendants <Row>()
.ToList();
// найти заголовок согласно фильтру
var rowMap = GetHeaderMap(sst, rows, filterValues);
if (!rowMap.CellMaps.Any())
{
return(new List <DataRow>());
}
var bodyRows = await ExtractBodyRowsAsync(sst, rowMap, rows);
return(bodyRows);
}
private static string ToString(Cell c, SharedStringTablePart stringTable)
{
if (c.DataType == null)
{
return(c.CellValue?.Text);
}
switch (c.DataType.Value)
{
case CellValues.SharedString:
{
if (stringTable != null)
{
int index = int.Parse(c.InnerText);
var value = stringTable.SharedStringTable.ElementAt(index).InnerText;
return(value);
}
}
break;
case CellValues.Boolean:
{
return(c.InnerText switch
{
"0" => "FALSE",
_ => "TRUE",
});
}
/// <summary>
/// 共享字符串
/// </summary>
/// <param name="sharedStringTablePart"></param>
private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart)
{
using (OpenXmlWriter writer = OpenXmlWriter.Create(sharedStringTablePart))
{
// SharedStringTable 开始
writer.WriteStartElement(new SharedStringTable()
{
Count = totalCount, UniqueCount = (uint)stringIndexDic.Keys.Count
});
foreach (var str in stringIndexDic.Keys)
{
// SharedStringItem 开始
writer.WriteStartElement(new SharedStringItem());
// 写入 Text
writer.WriteElement(new Text(str));
// SharedStringItem 结束
writer.WriteEndElement();
}
// SharedStringTable 结束
writer.WriteEndElement();
writer.Close();
}
}
private static void SetCellText(Cell cell, string text, SharedStringTablePart shareStringPart)
{
var index = InsertSharedStringItem(text, shareStringPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
/// <summary>
/// 获取单元格的值
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="shareStringPart"></param>
/// <returns></returns>
public static string GetValue(this Cell cell, SharedStringTablePart shareStringPart)
{
if (cell == null)
{
return(null);
}
string cellvalue = cell.InnerText;
if (cell.DataType != null)
{
if (cell.DataType == CellValues.SharedString)
{
int id = -1;
if (Int32.TryParse(cellvalue, out id))
{
SharedStringItem item = GetItem(shareStringPart, id);
if (item.Text != null)
{
//code to take the string value
cellvalue = item.Text.Text;
}
else if (item.InnerText != null)
{
cellvalue = item.InnerText;
}
else if (item.InnerXml != null)
{
cellvalue = item.InnerXml;
}
}
}
}
return(cellvalue);
}
