public int GetRowCount(string sheetName)
{
var wp = SpreadsheetReader.GetWorksheetPartByName(_doc, sheetName);
var worksheet = wp.Worksheet;
var sheetData = worksheet.GetFirstChild <SheetData>();
return(sheetData.Elements <Row>().Count());
}
public ExcelWriter()
{
_stream = SpreadsheetReader.Create();
_doc = SpreadsheetDocument.Open(_stream, true);
var worksheetPart = SpreadsheetReader.GetWorksheetPartByName(_doc, "Sheet1");
_writer = new WorksheetWriter(_doc, worksheetPart);
}
public static MemoryStream CalcSpreadsheetDocument(MemoryStream ms, bool DeleteFormula, string OutFile)
{
bool pg = (progressBar != null);
SpreadsheetDocument RecultDoc = SpreadsheetDocument.Open(ms, true);
if (DeleteFormula)
{
RecultDoc.WorkbookPart.DeletePart(RecultDoc.WorkbookPart.CalculationChainPart);
}
foreach (Sheet curSheet in RecultDoc.WorkbookPart.Workbook.Sheets)
{
WorksheetPart RecultWorkSheetPart = SpreadsheetReader.GetWorksheetPartByName(RecultDoc, curSheet.Name.Value);
IEnumerable <Row> ListRowInRange = RecultWorkSheetPart.Worksheet.Elements <SheetData>().First().Elements <Row>().Where(r => r.Elements <Cell>().Where(c => c.CellFormula != null).Count() > 0);
if (pg)
{
progressBar.Maximum = ListRowInRange.Count();
}
int value = 0;
foreach (Row Row in ListRowInRange)
{
IEnumerable <Cell> fCell = Row.Elements <Cell>().Where(c => c.CellFormula != null).Where(c => c.CellFormula.Text.Trim() != "");
foreach (Cell c in fCell)
{
var dd = calcFormule(c, RecultWorkSheetPart, RecultDoc, DeleteFormula);
}
if (pg)
{
progressBar.Value = ++value;
}
}
}
RecultDoc.Close();
if (OutFile != null)
{
if (OutFile.Length > 0)
{
SpreadsheetWriter.StreamToFile(OutFile, ms);
}
}
return(ms);
}
/// <summary>
/// Create the spreadsheet.
/// </summary>
/// <param name="documentName">Excel file name.</param>
/// <param name="excelWorkSheetName">Excel worksheet name: default: sheet1.</param>
/// <param name="rowData">Row data to write.</param>
/// <param name="headerData">Header data.</param>
/// <param name="rowPointers">Row pointers.</param>
/// <param name="styleSheet">Style sheet.</param>
/// <returns>Memory stream.</returns>
private static MemoryStream CreateSpreadSheet(string documentName, string excelWorkSheetName, IEnumerable <T> rowData, string[] headerData, string[] rowPointers, Stylesheet styleSheet)
{
int rowNum = 0;
int colNum = 0;
int maxWidth = 0;
int minCol = 1;
int maxCol = rowPointers == null ? minCol : rowPointers.Length;
maxCol = maxCol == 1 && headerData == null ? 1 : headerData.Length;
MemoryStream xmlStream = SpreadsheetReader.Create();
SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xmlStream, true);
SetSheetName(excelWorkSheetName, spreadSheet);
if (styleSheet == null)
{
SetStyleSheet(spreadSheet);
}
else
{
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet = styleSheet;
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(spreadSheet, excelWorkSheetName);
WriteHeaders(headerData, out rowNum, out colNum, out maxWidth, spreadSheet, worksheetPart);
AddCellWidthStyles(Convert.ToUInt32(minCol), Convert.ToUInt32(maxCol), maxWidth, spreadSheet, worksheetPart);
if (rowPointers == null || rowPointers.Length == 0)
{
WriteRowsFromHeaders(rowData, headerData, rowNum, out maxWidth, spreadSheet, worksheetPart);
}
else
{
WriteRowsFromKeys(rowData, rowPointers, rowNum, out maxWidth, spreadSheet, worksheetPart);
}
// Save to the memory stream
SpreadsheetWriter.Save(spreadSheet);
spreadSheet.Close();
spreadSheet.Dispose();
return(xmlStream);
}
private void ClearSpreadsheetDocument(SpreadsheetDocument Document)
{
Document.WorkbookPart.DeletePart(Document.WorkbookPart.CalculationChainPart);
foreach (Sheet curSheet in Document.WorkbookPart.Workbook.Sheets)
{
WorksheetPart workSheetPart = SpreadsheetReader.GetWorksheetPartByName(Document, curSheet.Name);
SheetData sheetData = workSheetPart.Worksheet.Elements <SheetData>().First();
if (!sheetData.HasChildren)
{
MergeCells p = workSheetPart.Worksheet.GetFirstChild <MergeCells>();
if (p != null)
{
p.Remove();
}
}
}
}
private UInt32 SetValueFromDataTable(DataTable dataTable, SpreadsheetDocument RecultDoc, SpreadsheetDocument TemleytDoc, UInt32 CurOffset)
{
UInt32 newOffset = 0;
string RangeName = dataTable.TableName;
// SpreadsheetDocument TemleytDoc = SpreadsheetDocument.Open(Temleyt, true);
DefinedName DefName = SpreadsheetReader.GetDefinedName(TemleytDoc, RangeName);
if (DefName != null)
{
if (DefName.Text.IndexOf("#REF!") < 0)
{
string definedName = DefName.Text;
string WorkSheetName = definedName.Substring(0, definedName.IndexOf("!")).TrimEnd('\'').TrimStart('\'');
string Range = definedName.Substring(definedName.IndexOf("!") + 1);
UInt32 startRowInRange = FirstRowInRange(Range);
UInt32 endRowInRange = LastRowInRange(Range);
UInt32 serviceRow = 1;
UInt32 CountRangeData = (endRowInRange - startRowInRange + 1) - serviceRow;
WorksheetPart TemleytWorkSheet = SpreadsheetReader.GetWorksheetPartByName(TemleytDoc, WorkSheetName);
List <Row> ListRowInRange = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>()
.Where(r => r.RowIndex >= startRowInRange && r.RowIndex < endRowInRange).ToList();
List <Row> ListRowBottom = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>()
.Where(r => r.RowIndex > endRowInRange).ToList();
List <Row> ServiceRow = TemleytWorkSheet.Worksheet.Elements <SheetData>().First().Elements <Row>()
.Where(r => r.RowIndex == endRowInRange).ToList();
List <MergeCell> ListMCellInRange = new List <MergeCell>();
List <MergeCell> ListMCellBottom = new List <MergeCell>();
MergeCells mergeCells = TemleytWorkSheet.Worksheet.GetFirstChild <MergeCells>();
if (mergeCells != null)
{
ListMCellInRange = TemleytWorkSheet.Worksheet.Elements <MergeCells>().First().Elements <MergeCell>()
.Where(r => SpreadsheetReader.RowFromReference(FirstRefFromRange(r.Reference.Value)) >= startRowInRange && SpreadsheetReader.RowFromReference(LastRefFromRange(r.Reference.Value)) <= endRowInRange).ToList();
ListMCellBottom = TemleytWorkSheet.Worksheet.Elements <MergeCells>().First().Elements <MergeCell>()
.Where(r => SpreadsheetReader.RowFromReference(FirstRefFromRange(r.Reference.Value)) > endRowInRange).ToList();
}
UInt32 NewStartRow = startRowInRange + CurOffset;
WorksheetPart RecultWorkSheetPart = SpreadsheetReader.GetWorksheetPartByName(RecultDoc, WorkSheetName);
WorksheetWriter RecultWriter = new WorksheetWriter(RecultDoc, RecultWorkSheetPart);
DeleteRows(RecultDoc, RecultWriter, NewStartRow, CountRangeData + serviceRow);
if (ListRowBottom.Count() > 0)
{
DeleteRows(RecultDoc, RecultWriter, NewStartRow, ListRowBottom.Last().RowIndex.Value + CurOffset - NewStartRow + 1);
}
MaximumCount = dataTable.Rows.Count;
UInt32 InsideOffset = NewStartRow;
foreach (DataRow dataRow in dataTable.Rows)
{
UInt32 Offset = (InsideOffset - NewStartRow) + CurOffset;
if (dataRow.RowState.ToString() != "Deleted")
{
AppendRows(dataRow, ListRowInRange, ListMCellInRange, RecultWorkSheetPart, Offset);
InsideOffset += CountRangeData;
}
++ProgressValue;
}
newOffset = (InsideOffset - NewStartRow - CountRangeData) + CurOffset;
AppendRows(null, ListRowBottom, ListMCellBottom, RecultWorkSheetPart, newOffset);
DefName = SpreadsheetReader.GetDefinedName(RecultDoc, RangeName);
definedName = DefName.Text;
DefName.Text = definedName.Substring(0, definedName.LastIndexOf('$') + 1) + InsideOffset.ToString();
// DefName.Text = definedName.Substring(0, definedName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(FirstRefFromRange(Range)) + '$' + NewStartRow.ToString() + ":$" + SpreadsheetReader.ColumnFromReference(LastRefFromRange(Range)) + '$' + StartClone.ToString();
//Обновляем ссилки начала и конца рабочих областей DefinedNames
foreach (DefinedName CurdefName in RecultDoc.WorkbookPart.Workbook.DefinedNames)
{
if (CurdefName.Text.IndexOf("#REF!") < 0)
{
// if (CurdefName.Text == definedName)//Текущая облать DefinedName
// {
// CurdefName.Text = definedName.Substring(0, definedName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(FirstRefFromRange(Range)) + '$' + NewStartRow.ToString() + ":$" + SpreadsheetReader.ColumnFromReference(LastRefFromRange(Range)) + '$' + StartOffset.ToString();
// }
// else //Все остальные DefinedName текущего листа Sheet
// {
string strDefName = CurdefName.Text;
string sheetName = strDefName.Substring(0, strDefName.IndexOf("!")).Trim('\'');
string range = strDefName.Substring(strDefName.IndexOf("!") + 1);
if (sheetName == WorkSheetName)
{
string firstRefInRange = FirstRefFromRange(range);
string lastRefInRange = LastRefFromRange(range);
UInt32 firstRow = SpreadsheetReader.RowFromReference(firstRefInRange);
UInt32 lastRow = SpreadsheetReader.RowFromReference(lastRefInRange);
if (firstRow + newOffset > InsideOffset + CurOffset)
{
CurdefName.Text = strDefName.Substring(0, strDefName.IndexOf("!") + 1) + '$' + SpreadsheetReader.ColumnFromReference(firstRefInRange) + '$' + (firstRow + newOffset).ToString() + ":$" + SpreadsheetReader.ColumnFromReference(lastRefInRange) + '$' + (lastRow + newOffset).ToString();
}
}
// }
}
}
}
}
// TemleytDoc.Close();
return(newOffset);
}
public bool SetWorksheet(string sheetName)
{
_worksheet = SpreadsheetReader.GetWorksheetPartByName(_doc, sheetName);
return(_worksheet != null);
}