private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.UTF8);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
string cellValue = "";
string cellReference = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsIntegerColumn = new bool[numberOfColumns];
bool[] IsFloatColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer);
IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
}
writer.WriteEndElement(); // End of header "Row"
//
// Now, step through each row of data in our DataTable...
//
double cellFloatValue = 0;
CultureInfo ci = new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
// Create cell with data
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
// For numeric cells without any decimal places.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellFloatValue = 0;
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
AppendNumericCell(cellReference, cellValue, writer);
}
}
else if (IsDateColumn[colInx])
{
// This is a date value.
DateTime dateValue;
if (DateTime.TryParse(cellValue, out dateValue))
{
AppendDateCell(cellReference, dateValue, writer);
}
else
{
// This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank.
AppendTextCell(cellReference, cellValue, writer);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(cellReference, cellValue, writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
dt.Clear();
}
public static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
UInt32 inx = 1;
writer.WriteStartElement(new Columns());
foreach (DataColumn dc in dt.Columns)
{
writer.WriteElement(new Column {
Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH
});
inx++;
}
writer.WriteEndElement();
writer.WriteStartElement(new SheetData());
string cellValue = "";
string cellReference = "";
int numberOfColumns = dt.Columns.Count;
bool[] IsIntegerColumn = new bool[numberOfColumns];
bool[] IsFloatColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex, Height = 20, CustomHeight = true
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
writer.AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName);
IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
IsFloatColumn[colInx] = (col.DataType.FullName == typeof(decimal).FullName) || (col.DataType.FullName == typeof(double).FullName) || (col.DataType.FullName == typeof(Single).FullName);
IsDateColumn[colInx] = (col.DataType.FullName == typeof(DateTime).FullName);
}
writer.WriteEndElement(); // End of header "Row"
double cellFloatValue = 0;
CultureInfo ci = Thread.CurrentThread.CurrentCulture; //new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
cellFloatValue = 0;
bool bIncludeDecimalPlaces = IsFloatColumn[colInx];
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
writer.AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces ? NumericFormatDecimaPlaces.Two : NumericFormatDecimaPlaces.Zero);
}
}
else if (IsDateColumn[colInx])
{
DateTime dateValue;
if (DateTime.TryParse(cellValue, out dateValue))
{
writer.AppendDateCell(cellReference, dateValue);
}
else
{
writer.AppendTextCell(cellReference, cellValue);
}
}
else
{
writer.AppendTextCell(cellReference, cellValue);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
string cellValue = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
}
writer.WriteEndElement(); // End of header "Row"
//
// Now, step through each row of data in our DataTable...
//
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
// Create cell with data
if (IsNumericColumn[colInx])
{
// For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
else if (IsDateColumn[colInx])
{
// This is a date value.
DateTime dtValue;
string strValue = "";
if (DateTime.TryParse(cellValue, out dtValue))
{
strValue = dtValue.ToShortDateString();
}
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer);
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
public void WriteRandomValuesSAX(string filename, int numRows, int numCols)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
WorksheetPart replacementPart =
workbookPart.AddNewPart <WorksheetPart>();
string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);
Row r = new Row();
Cell c = new Cell();
CellFormula f = new CellFormula();
f.CalculateCell = true;
f.Text = "RAND()";
c.Append(f);
CellValue v = new CellValue();
c.Append(v);
while (reader.Read())
{
if (reader.ElementType == typeof(SheetData))
{
if (reader.IsEndElement)
{
continue;
}
writer.WriteStartElement(new SheetData());
for (int row = 0; row < numRows; row++)
{
writer.WriteStartElement(r);
for (int col = 0; col < numCols; col++)
{
writer.WriteElement(c);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
}
else
{
if (reader.IsStartElement)
{
writer.WriteStartElement(reader);
}
else if (reader.IsEndElement)
{
writer.WriteEndElement();
}
}
}
reader.Close();
writer.Close();
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>()
.Where(s => s.Id.Value.Equals(origninalSheetId)).First();
sheet.Id.Value = replacementPartId;
workbookPart.DeletePart(worksheetPart);
}
}
public int writeHeader(List <ChannelModel> channels, OperatorModel operatorParam, FacilityModel facilityParam)
{
int retvalueTemp = 0;
if (_doc != null && _workBook != null && _workSheet != null)
{
string originalPartId = _workBook.GetIdOfPart(_workSheet);
WorksheetPart replacementPart = _workBook.AddNewPart <WorksheetPart>();
string replacementPartId = _workBook.GetIdOfPart(replacementPart);
_reader = OpenXmlReader.Create(_workSheet);
_writer = OpenXmlWriter.Create(replacementPart);
while (_reader.Read())
{
if (_reader.ElementType == typeof(Selection))
{
continue;
}
if (_reader.ElementType == typeof(SheetData))
{
if (_reader.IsEndElement)
{
continue;
}
_writer.WriteStartElement(new SheetData()); // beginning of sheetdata
// append section begins
var labelRow = new Row(); // label row
labelRow.RowIndex = 1;
_writer.WriteStartElement(labelRow); // begining of row
var operatorLabelCell = new Cell();
var facilityLabelCell = new Cell();
var creationDateLabelCell = new Cell();
operatorLabelCell.CellValue = new CellValue(" Operatör ");
facilityLabelCell.CellValue = new CellValue(" Tesis ");
creationDateLabelCell.CellValue = new CellValue(" Oluşturulma Tarihi ");
_writer.WriteElement(operatorLabelCell);
_writer.WriteElement(facilityLabelCell);
_writer.WriteElement(creationDateLabelCell);
_writer.WriteEndElement(); // end of label row
var infoRow = new Row(); // info row
infoRow.RowIndex = 2;
_writer.WriteStartElement(infoRow); // begining of row
var operatorInfoCell = new Cell();
var facilityInfoCell = new Cell();
var creationDateInfoCell = new Cell();
operatorInfoCell.CellValue = new CellValue(operatorParam.DisplayName);
facilityInfoCell.CellValue = new CellValue(facilityParam.ListDisplay);
creationDateInfoCell.CellValue = new CellValue(DateTime.Now.ToShortTimeString());
_writer.WriteElement(operatorInfoCell);
_writer.WriteElement(facilityInfoCell);
_writer.WriteElement(creationDateInfoCell);
_writer.WriteEndElement(); // end of info row
var channelListRow = new Row(); // channelListRow row
channelListRow.RowIndex = 3;
_writer.WriteStartElement(channelListRow); // begining of row
for (int k = 0; k < channels.Count; k++)
{
var dataInfoCell = new Cell();
dataInfoCell.CellValue = new CellValue(channels[k].name);
_writer.WriteElement(dataInfoCell);
}
_writer.WriteEndElement(); // end of channel info row
_writer.WriteEndElement(); // end of sheetdata
}
else
{
if (_reader.IsStartElement)
{
_writer.WriteStartElement(_reader);
if (_reader.ElementType == typeof(CellValue))
{
_writer.WriteString(_reader.GetText());
}
}
else if (_reader.IsEndElement)
{
_writer.WriteEndElement();
}
else
{
}
}
}
_writer.Close();
_reader.Close();
Sheet sheet = _workBook.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(originalPartId)).First();
sheet.Id.Value = replacementPartId;
_workBook.DeletePart(_workSheet);
}
else
{
retvalueTemp = -1;
}
return(retvalueTemp);
}
internal virtual void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location)
{
DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>();
writer.WriteStartElement(new Drawing()
{
Id = part.GetIdOfPart(drawingsPart)
});
writer.WriteEndElement();
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart());
// Set chart title
chartContainer.AppendChild(ChartPropertySetter.SetTitle(ChartPropertySetter.ChartProperties.Title));
chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted()
{
Val = false
});
// Create a new clustered column chart.
PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea());
uint chartSeriesCounter = 0;
OpenXmlCompositeElement chart = ChartPropertySetter.CreateChart(plotArea);
foreach (var chartDataSeriesGrouped in ChartData.GroupBy(x => x.Series))
{
// Set chart and series depending on type.
OpenXmlCompositeElement chartSeries = ChartPropertySetter.CreateChartSeries(chartDataSeriesGrouped.Key, chartSeriesCounter, chart);
// Every method from chartPropertySetter can be overriden to customize chart export.
ChartPropertySetter.SetChartShapeProperties(chartSeries);
ChartPropertySetter.SetChartAxis(chartSeries, chartDataSeriesGrouped.ToList());
chartSeriesCounter++;
}
chart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
chart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
// Add the Category Axis (X axis).
ChartPropertySetter.SetLineCategoryAxis(plotArea);
// Add the Value Axis (Y axis).
ChartPropertySetter.SetValueAxis(plotArea);
ChartPropertySetter.SetLegend(chartContainer);
ChartPropertySetter.SetChartLocation(drawingsPart, chartPart, location);
}
public void WriteWorksheetPart(OpenXmlWriter writer)
{
// TODO: final cleanup
// - merge redundant columns
// - remove unused shared strings
// Remove rows without cells
foreach (var rowItem in _cachedCells.ToList())
{
if (rowItem.Value.Count == 0)
{
_cachedCells.Remove(rowItem.Key);
}
}
// Simulate rows for cached rows
foreach (var rowIdx in _cachedRows.Keys)
{
if (!_cachedCells.ContainsKey(rowIdx))
{
_cachedCells[rowIdx] = new SortedList <uint, CellProxy>();
}
}
// Get first and last addresses
uint minRow = uint.MaxValue;
uint minCol = uint.MaxValue;
uint maxRow = 0;
uint maxCol = 0;
foreach (var rowItem in _cachedCells)
{
uint rowIdx = rowItem.Key;
var cells = rowItem.Value;
if (minRow == uint.MaxValue)
{
minRow = rowIdx;
}
maxRow = rowIdx;
if (cells.Count > 0)
{
minCol = Math.Min(minCol, cells.Keys.First());
maxCol = Math.Max(maxCol, cells.Keys.Last());
}
}
string firstAddress = null, lastAddress = null;
if (minRow < uint.MaxValue && minCol < uint.MaxValue)
{
firstAddress = RowColumn.ToAddress(minRow, minCol);
if (minRow != maxRow || minCol != maxCol)
{
lastAddress = RowColumn.ToAddress(maxRow, maxCol);
}
}
else
{
firstAddress = "A1";
}
writer.WriteStartDocument();
writer.WriteStartElement(new Worksheet());
foreach (string childTagName in SchemaInfo.WorksheetChildSequence)
{
if (childTagName == "sheetData")
{
WriteSheetData(writer);
}
else if (childTagName == "dimension")
{
string dimensionRef = firstAddress + (lastAddress != null ? ":" + lastAddress : "");
writer.WriteElement(new SheetDimension()
{
Reference = dimensionRef
});
}
else if (childTagName == "sheetViews")
{
SheetViews svs = GetFirstElement <SheetViews>();
if (svs != null)
{
foreach (SheetView sv in svs.Elements <SheetView>())
{
foreach (Selection sel in sv.Elements <Selection>())
{
if (minRow < uint.MaxValue)
{
sel.ActiveCell = firstAddress;
sel.SequenceOfReferences = new ListValue <StringValue>(new StringValue[] { new StringValue(firstAddress) });
}
else
{
sel.Remove();
}
}
}
writer.WriteElement(svs);
}
}
else
{
foreach (var e in GetElementsByTagName(childTagName))
{
writer.WriteElement(e);
}
}
}
writer.WriteEndElement(); // worksheet
}
private void WriteSheetData(OpenXmlWriter writer)
{
writer.WriteStartElement(new SheetData());
foreach (KeyValuePair <uint, SortedList <uint, CellProxy> > pair in this._cachedCells)
{
uint key = pair.Key;
SortedList <uint, CellProxy> cells = pair.Value;
List <OpenXmlAttribute> attributes = this.EnumRowAttributes(key, cells).ToList <OpenXmlAttribute>();
if ((attributes.Count > 0) || (cells.Count > 0))
{
writer.WriteStartElement(new Row(), attributes);
foreach (KeyValuePair <uint, CellProxy> pair2 in cells)
{
uint col = pair2.Key;
CellProxy cellProxy = pair2.Value;
writer.WriteStartElement(new Cell(), this.EnumCellProxyAttributes(key, col, cellProxy));
if (cellProxy.Formula != null)
{
CellFormula elementObject = new CellFormula(cellProxy.Formula.Text);
if (cellProxy.Formula.R1 != null)
{
elementObject.R1 = cellProxy.Formula.R1;
}
if (cellProxy.Formula.R2 != null)
{
elementObject.R2 = cellProxy.Formula.R2;
}
if (cellProxy.Formula.Reference != null)
{
elementObject.Reference = cellProxy.Formula.Reference;
}
if (cellProxy.Formula.AlwaysCalculateArray.HasValue)
{
bool?alwaysCalculateArray = cellProxy.Formula.AlwaysCalculateArray;
elementObject.AlwaysCalculateArray = alwaysCalculateArray.HasValue ? ((BooleanValue)alwaysCalculateArray.GetValueOrDefault()) : null;
}
if (cellProxy.Formula.Bx.HasValue)
{
bool?bx = cellProxy.Formula.Bx;
elementObject.Bx = bx.HasValue ? ((BooleanValue)bx.GetValueOrDefault()) : null;
}
if (cellProxy.Formula.CalculateCell.HasValue)
{
bool?calculateCell = cellProxy.Formula.CalculateCell;
elementObject.CalculateCell = calculateCell.HasValue ? ((BooleanValue)calculateCell.GetValueOrDefault()) : null;
}
if (cellProxy.Formula.DataTable2D.HasValue)
{
bool?nullable8 = cellProxy.Formula.DataTable2D;
elementObject.DataTable2D = nullable8.HasValue ? ((BooleanValue)nullable8.GetValueOrDefault()) : null;
}
if (cellProxy.Formula.DataTableRow.HasValue)
{
bool?dataTableRow = cellProxy.Formula.DataTableRow;
elementObject.DataTableRow = dataTableRow.HasValue ? ((BooleanValue)dataTableRow.GetValueOrDefault()) : null;
}
if (cellProxy.Formula.FormulaType.HasValue)
{
CellFormulaValues?formulaType = cellProxy.Formula.FormulaType;
elementObject.FormulaType = formulaType.HasValue ? ((EnumValue <CellFormulaValues>)formulaType.GetValueOrDefault()) : null;
}
if (cellProxy.Formula.Input1Deleted.HasValue)
{
bool?nullable14 = cellProxy.Formula.Input1Deleted;
elementObject.Input1Deleted = nullable14.HasValue ? ((BooleanValue)nullable14.GetValueOrDefault()) : null;
}
if (cellProxy.Formula.Input2Deleted.HasValue)
{
bool?nullable16 = cellProxy.Formula.Input2Deleted;
elementObject.Input2Deleted = nullable16.HasValue ? ((BooleanValue)nullable16.GetValueOrDefault()) : null;
}
if (cellProxy.Formula.SharedIndex.HasValue)
{
uint?sharedIndex = cellProxy.Formula.SharedIndex;
elementObject.SharedIndex = sharedIndex.HasValue ? ((UInt32Value)sharedIndex.GetValueOrDefault()) : null;
}
writer.WriteElement(elementObject);
}
if (cellProxy.Value != null)
{
writer.WriteElement(new CellValue(cellProxy.SerializedValue));
}
writer.WriteEndElement();
}
writer.WriteEndElement();
}
}
writer.WriteEndElement();
}
public void WriteWorksheetPart(OpenXmlWriter writer)
{
foreach (KeyValuePair <uint, SortedList <uint, CellProxy> > pair in this._cachedCells.ToList <KeyValuePair <uint, SortedList <uint, CellProxy> > >())
{
if (pair.Value.Count == 0)
{
this._cachedCells.Remove(pair.Key);
}
}
foreach (uint num in this._cachedRows.Keys)
{
if (!this._cachedCells.ContainsKey(num))
{
this._cachedCells[num] = new SortedList <uint, CellProxy>();
}
}
uint maxValue = uint.MaxValue;
uint num3 = uint.MaxValue;
uint row = 0;
uint num5 = 0;
foreach (KeyValuePair <uint, SortedList <uint, CellProxy> > pair2 in this._cachedCells)
{
uint key = pair2.Key;
SortedList <uint, CellProxy> list = pair2.Value;
if (maxValue == uint.MaxValue)
{
maxValue = key;
}
row = key;
if (list.Count > 0)
{
num3 = Math.Min(num3, list.Keys.First <uint>());
num5 = Math.Max(num5, list.Keys.Last <uint>());
}
}
string str = null;
string str2 = null;
if ((maxValue < uint.MaxValue) && (num3 < uint.MaxValue))
{
str = RowColumn.ToAddress(maxValue, num3);
if ((maxValue != row) || (num3 != num5))
{
str2 = RowColumn.ToAddress(row, num5);
}
}
else
{
str = "A1";
}
writer.WriteStartDocument();
writer.WriteStartElement(new Worksheet());
foreach (string str3 in SchemaInfo.WorksheetChildSequence)
{
switch (str3)
{
case "sheetData":
this.WriteSheetData(writer);
break;
case "dimension":
{
string str4 = str + ((str2 != null) ? (":" + str2) : "");
SheetDimension elementObject = new SheetDimension {
Reference = str4
};
writer.WriteElement(elementObject);
break;
}
case "sheetViews":
{
SheetViews firstElement = this.GetFirstElement <SheetViews>();
if (firstElement != null)
{
foreach (SheetView view in firstElement.Elements <SheetView>())
{
foreach (Selection selection in view.Elements <Selection>())
{
if (maxValue < uint.MaxValue)
{
selection.ActiveCell = str;
selection.SequenceOfReferences = new ListValue <StringValue>(new StringValue[] { new StringValue(str) });
}
else
{
selection.Remove();
}
}
}
writer.WriteElement(firstElement);
}
break;
}
default:
foreach (OpenXmlElement element in this.GetElementsByTagName(str3))
{
writer.WriteElement(element);
}
break;
}
}
writer.WriteEndElement();
}
public static Stream ToXLSX(TranslationModule project)
{
MemoryStream xlsStream = new MemoryStream();
var worksheet = new MemoryWorksheet();
Export.ToIWorksheet(project, worksheet);
var ss = new Stylesheet();
ss.CellStyleFormats = new CellStyleFormats();
ss.CellStyleFormats.Count = 1;
ss.CellStyles = new CellStyles();
ss.CellStyles.Count = 1;
ss.DifferentialFormats = new DifferentialFormats();
ss.DifferentialFormats.Count = 0;
ss.TableStyles = new TableStyles();
ss.TableStyles.Count = 0;
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(xlsStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myDoc.AddWorkbookPart();
//workbookPart.Workbook = new Workbook();
WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = ss;
stylesPart.Stylesheet.Save();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
//SheetData sheetData = new SheetData();
//worksheetPart.Worksheet = new Worksheet(sheetData);
/*
* Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
* Sheet sheet = new Sheet()
* {
* Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart),
* SheetId = 1,
* Name = "mySheet"
* };
* sheets.Append(sheet);
*/
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
for (int row = 0; row < worksheet.Rows; row++)
{
var oxa = new List <OpenXmlAttribute>();
// this is the row index
oxa.Add(new OpenXmlAttribute("r", null, row.ToString()));
writer.WriteStartElement(new Row(), oxa);
for (int col = 0; col < worksheet.Columns; col++)
{
oxa = new List <OpenXmlAttribute>();
// this is the data type ("t"), with CellValues.String ("str")
oxa.Add(new OpenXmlAttribute("t", null, "str"));
string val = worksheet[row, col] != null ? worksheet[row, col].ToString() : "";
//var cell = new Cell(new CellValue(val));
writer.WriteStartElement(new Cell(), oxa);
//Cell c = new Cell();
CellValue v = new CellValue(val);
writer.WriteElement(v);
//c.AppendChild(v);
//writer.WriteElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(myDoc.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
// you can use object initialisers like this only when the properties
// are actual properties. SDK classes sometimes have property-like properties
// but are actually classes. For example, the Cell class has the CellValue
// "property" but is actually a child class internally.
// If the properties correspond to actual XML attributes, then you're fine.
writer.WriteElement(new Sheet()
{
Name = "Sheet1",
SheetId = 1,
Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart)
});
// this is for Sheets
writer.WriteEndElement();
// this is for Workbook
writer.WriteEndElement();
writer.Close();
myDoc.Close();
}
return(xlsStream);
}
private void Initialize()
{
_workBookWriter = OpenXmlWriter.Create(_xl.WorkbookPart);
_workBookWriter.WriteStartElement(new Workbook());
_workBookWriter.WriteStartElement(new Sheets());
}
public virtual void Export(string fileName, LayoutList list)
{
this.list = list;
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = xl.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
//add styles
WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
// Add a SharedStringTablePart to the WorkbookPart.
var stringPart = workbookpart.AddNewPart <SharedStringTablePart>();
var stringTable = new StringKeyList();
// Add Sheets to the Workbook.
var sheets = xl.WorkbookPart.Workbook.AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = xl.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "DataSheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
mcells = new List <MergeCell>();
writer = OpenXmlWriter.Create(worksheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetProperties());
writer.WriteElement(new OutlineProperties()
{
SummaryBelow = false, SummaryRight = false
});
writer.WriteEndElement();
mc = 0;
writer.WriteStartElement(new Columns());
WriteMapColumns(list.ListInfo.Columns, 0, 0);
writer.WriteEndElement();
writer.WriteStartElement(new SheetData());
int ind = 1;
var row = new Row()
{
RowIndex = (uint)ind, Height = 25
};
row.AppendChild(GetCell(list.Description, 0, ind, (uint)13, stringTable));
WriteRows(writer, new List <Row>(new Row[] { row }));
mcells.Add(new MergeCell()
{
Reference = new CellRange(0, 1, mc - 1, 1).ToString()
});
WriteMapItem(list.ListInfo.Columns, -1, null, 0, 0, ref ind, stringTable);
if (list.Selection.Count > 1)
{
var items = list.Selection.GetItems <object>();
for (var i = 0; i < items.Count; i++)
{
var item = items[i];
WriteMapItem(list.ListInfo.Columns, i, item, 0, 0, ref ind, stringTable);
}
}
else if (list.NodeInfo != null)
{
var items = list.NodeInfo.Nodes.GetTopLevel().ToList();
for (var i = 0; i < items.Count; i++)
{
var item = items[i] as Node;
WriteMapItem(list.ListInfo.Columns, i, item, 0, 0, ref ind, stringTable);
}
}
else if (list.ListInfo.GroupVisible)
{
foreach (LayoutGroup g in list.Groups)
{
this.group = g;
if (list.ListInfo.GroupHeader)
{
ind++;
var header = new Row()
{
RowIndex = (uint)ind, CustomHeight = true, Height = 20
};
header.AppendChild(GetCell(g.TextValue, 0, ind, 8, stringTable));
mcells.Add(new MergeCell()
{
Reference = new CellRange(0, ind, mc - 1, ind).ToString()
});
WriteRow(writer, header);
}
for (int i = g.IndexStart; i <= g.IndexEnd; i++)
{
WriteMapItem(list.ListInfo.Columns, i, list.ListSource[i], 0, 0, ref ind, stringTable);
}
if (list.ListInfo.CollectingRow)
{
WriteMapItem(list.ListInfo.Columns, -2, null, 0, 0, ref ind, stringTable);
}
//ind++;
}
}
else
{
for (int i = 0; i < list.ListSource.Count; i++)
{
WriteMapItem(list.ListInfo.Columns, i, list.ListSource[i], 0, 0, ref ind, stringTable);
}
if (list.ListInfo.CollectingRow)
{
WriteMapItem(list.ListInfo.Columns, -2, null, 0, 0, ref ind, stringTable);
}
}
writer.WriteEndElement();
if (mcells.Count > 0)
{
writer.WriteStartElement(new MergeCells());
foreach (var cell in mcells)
{
writer.WriteElement(cell);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.Close();
}
}
public ActionResult Search(FormCollection model)
{
string selectedReport = model["cboReports"];
string datePeriod = model["DatePeriod"];
if (selectedReport.HasValue())
{
Guid idReport = new Guid(selectedReport);
Reports report = Reports.Get(idReport);
IEnumerable <dynamic> data = Reports.Run(report, datePeriod);
HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true")
{
Path = "/"
});
var context = HttpContext.Response;
context.Buffer = context.BufferOutput = false;
context.Cache.SetCacheability(HttpCacheability.Private);
context.Cache.SetExpires(DateTime.Now);
//context.ContentType = (new ContentType("text/csv") { CharSet = "utf-8" }).ToString(); // CSV
//context.ContentType = (new ContentType("application/vnd.ms-excel") { CharSet = "utf-8" }).ToString();
context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
CharSet = "utf-8"
}.ToString();
context.AppendHeader("Content-Disposition",
new ContentDisposition
{
DispositionType = DispositionTypeNames.Attachment,
FileName = string.Format(CultureInfo.InvariantCulture, report.FileNamePrefix + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now)
}.ToString()
);
context.AppendHeader("X-Content-Type-Options", "nosniff");
using (MemoryStream mDocument = new MemoryStream())
{
// Using SAX
using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook))
{
List <OpenXmlAttribute> attributes;
document.AddWorkbookPart();
// Stylesheet
WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesheet.Stylesheet = new Stylesheet(new Fonts(
new Font( // 0 = Default
new Color()
{
Rgb = new HexBinaryValue()
{
Value = "000000"
}
}
),
new Font( // 1 = Bold
new Bold()
),
new Font( // 2 = Red
new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
}
)
),
new Fills(
new Fill()
{
}
),
new Borders(new Border()
{
}),
new CellFormats(
new CellFormat()
{
FontId = 0
}, // 0
new CellFormat()
{
FontId = 1, ApplyFont = true
}, // 1
new CellFormat()
{
FontId = 2, ApplyFont = true
} // 2
)
);
stylesheet.Stylesheet.Save();
WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
IDictionary <string, object> firstRow = data.FirstOrDefault();
if (firstRow != null)
{
int row = 1;
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col1 = 1;
foreach (var cols in firstRow.Keys.ToList())
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col1) + row),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(cols));
writer.WriteEndElement();
col1++;
}
writer.WriteEndElement();
row++;
foreach (IDictionary <string, object> row2 in data)
{
attributes =
new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col = 1;
foreach (var key in row2.Keys)
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col) + row)
};
if (row2[key] is decimal)
{
if ((decimal)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
else if (row2[key] is double)
{
if ((double)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : ""));
writer.WriteEndElement();
col++;
}
writer.WriteEndElement();
row++;
}
}
else
{
// Empty row (no data found)
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, "1")
};
writer.WriteStartElement(new Row(), attributes);
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(1) + 1),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(""));
writer.WriteEndElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = "Sheet 1",
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
document.Save();
document.Close();
mDocument.WriteTo(context.OutputStream);
}
}
return(null);
}
return(null);
}
/*
* report.GetReportData()
*/
public ActionResult Export(Reports report)
{
try
{
var reportData = report.GetReportData(true);
HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true")
{
Path = "/"
});
var context = HttpContext.Response;
context.Buffer = context.BufferOutput = false;
context.Cache.SetCacheability(HttpCacheability.Private);
context.Cache.SetExpires(DateTime.Now);
context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
CharSet = "utf-8"
}.ToString();
context.AppendHeader("Content-Disposition",
new ContentDisposition
{
DispositionType = DispositionTypeNames.Attachment,
FileName = string.Format(CultureInfo.InvariantCulture, RemoveInvalidFilePathCharacters(reportData.title) + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now)
}.ToString()
);
context.AppendHeader("X-Content-Type-Options", "nosniff");
using (MemoryStream mDocument = new MemoryStream())
{
// Using SAX
using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook))
{
List <OpenXmlAttribute> attributes;
document.AddWorkbookPart();
// Stylesheet
WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesheet.Stylesheet = new Stylesheet(new Fonts(
new Font(new Color()
{
Rgb = new HexBinaryValue()
{
Value = "000000"
}
}), // 0
new Font(new Bold()), // 1
new Font(new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
}) // 2
),
new Fills(new Fill()
{
}),
new Borders(new Border()
{
}),
new CellFormats(
new CellFormat()
{
FontId = 0
}, // 0
new CellFormat()
{
FontId = 1, ApplyFont = true
}, // 1
new CellFormat()
{
FontId = 2, ApplyFont = true
} // 2
)
);
stylesheet.Stylesheet.Save();
WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
IDictionary <string, object> firstRow = new Dictionary <string, object>()
{
{ Words.Reports_Name, "" },
{ Words.Reports_Value, "" }
};
if (firstRow != null)
{
int row = 1;
AddLine(writer, row, new string[] { reportData.title }); row++;
AddLine(writer, row, new string[] { "" }); row++;
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col1 = 1;
foreach (var cols in firstRow.Keys.ToList())
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col1) + row),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(cols));
writer.WriteEndElement();
col1++;
}
writer.WriteEndElement();
row++;
for (int i = 0; i < reportData.labels.Length; i++)
{
attributes =
new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col = 1;
var row2 = new Dictionary <string, object>()
{
{ "Name", reportData.labels[i] },
{ "Value", reportData.datasets[0].data[i] }
};
foreach (var key in row2.Keys)
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col) + row)
};
if (row2[key] is decimal)
{
if ((decimal)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
else if (row2[key] is double)
{
if ((double)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : ""));
writer.WriteEndElement();
col++;
}
writer.WriteEndElement();
row++;
}
}
else
{
// Empty row (no data found)
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, "1")
};
writer.WriteStartElement(new Row(), attributes);
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(1) + 1),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(""));
writer.WriteEndElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = "Sheet 1",
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
document.Save();
document.Close();
mDocument.WriteTo(context.OutputStream);
}
}
}
catch (Exception ex)
{
ex.Log();
}
return(null);
}
private void WriteReportColumns()
{
try
{
_openXmlWriter.WriteStartElement(new Columns());
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 32.29, Max = 1, Min = 1
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 2, Min = 2
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 3, Min = 3
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 4, Min = 4
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 5, Min = 5
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 6, Min = 6
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 7, Min = 7
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 8, Min = 8
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 9, Min = 9
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 32.29, Max = 10, Min = 10
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 32.29, Max = 11, Min = 11
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 12, Min = 12
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 13, Min = 13
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 14, Min = 14
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 15, Min = 15
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 16, Min = 16
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 24.43, Max = 17, Min = 17
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 32.29, Max = 18, Min = 18
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 32.29, Max = 19, Min = 19
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 32.29, Max = 20, Min = 20
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 32.29, Max = 21, Min = 21
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 32.29, Max = 22, Min = 22
});
_openXmlWriter.WriteElement(new Column()
{
CustomWidth = true, Width = 32.29, Max = 23, Min = 23
});
_openXmlWriter.WriteEndElement();
}
catch (Exception exception)
{
LogWriter.LogError("Unable to generate 'STIG Discrepancies' columns.");
throw exception;
}
}
public void SaveFile(string path)
{
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart wbp = spreadsheet.AddWorkbookPart();
wbp.Workbook = new Workbook();
Sheets sheets = wbp.Workbook.AppendChild <Sheets>(new Sheets());
Parallel.For(0, dict_sheet_name_map.Count, sheet_num =>
{
WorksheetPart wsp;
float tmp_fltvalue;
string tmp_strvalue = "";
Cell tmp_cell;
lock ("addition")
{
wsp = wbp.AddNewPart <WorksheetPart>();
Sheet sheet = new Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(wsp), SheetId = (UInt32)(sheet_num + 1), Name = sheet_names[sheet_num]
};
sheets.Append(sheet);
}
Parallel.For(0, lst_DepositCells[sheet_num].Count, i =>
{
lst_DepositCells[sheet_num][i].Sort((x, y) => x.Length == y.Length ? string.Compare(x, y) : x.Length > y.Length ? 1 : -1);
});
OpenXmlWriter writer = OpenXmlWriter.Create(wsp);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
for (int row = 0; row < lst_DepositCells[sheet_num].Count; row++)
{
writer.WriteStartElement(new Row());
for (int col = 0; col < lst_DepositCells[sheet_num][row].Count; col++)
{
string tmp_address = lst_DepositCells[sheet_num][row][col];
bool is_string = false;
if (dict_FloatResults[sheet_num].TryGetValue(tmp_address, out tmp_fltvalue))
{
is_string = false;
}
else if (dict_StringResults[sheet_num].TryGetValue(tmp_address, out tmp_strvalue))
{
is_string = true;
}
else if (dict_FloatConstants[sheet_num].TryGetValue(tmp_address, out tmp_fltvalue))
{
is_string = false;
}
else if (dict_StringConstants[sheet_num].TryGetValue(tmp_address, out tmp_strvalue))
{
is_string = true;
}
if (is_string)
{
if (insert_formula)
{
tmp_cell = new Cell()
{
CellReference = tmp_address, DataType = CellValues.String, CellFormula = new CellFormula(dict_Formulas[lst_DepositCells[sheet_num][row][col]])
}
}
}
;
else
{
tmp_cell = new Cell()
{
CellReference = tmp_address, DataType = CellValues.String
}
};
private static void WriteDataTableToExcelWorksheet(ReportCondition dtCondition, DataTable dtDetail,
WorksheetPart worksheetPart, WorkbookStylesPart stylesPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.Unicode); //ASCII
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
string cellValue = string.Empty;
uint rowIndexHeader = 1;
// Create the Conditon Report
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "BOC Detailed Utilization", 1, stylesPart.Stylesheet, System.Drawing.Color.White, false, 20, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Date:", 2, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat(
"B"
, string.Format("{0} - {1}", dtCondition.FromDate.ToString("dd.MM.yyyy"), dtCondition.ToDate.AddDays(-1).ToString("dd.MM.yyyy"))
, 2, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Location:", 3, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.LocationPath.ToString(), 3, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Group:", 4, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.GroupName.ToString(), 4, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Resource:", 5, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.ResourceName.ToString(), 5, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Method:", 6, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.Method.ToString(), 6, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Include weekends:", 7, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", SetWeekendTitle(dtCondition), 7, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Upper threshold (%):", 8, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.UpperThreshold.ToString(), 8, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
writer.WriteStartElement(new Row {
RowIndex = rowIndexHeader++
});
AppendTextCellFormat("A", "Lower threshold (%):", 9, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
AppendTextCellFormat("B", dtCondition.LowerThreshold.ToString(), 9, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
writer.WriteEndElement();
// End of Conditon Report
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
int numberOfColumns = dtDetail.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
// Create the Header row in our Excel Worksheet
writer.WriteStartElement(new Row {
RowIndex = 11
});
AppendTextCellFormat("A", "Resource", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer);
AppendTextCellFormat("B", "Hours Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer);
AppendTextCellFormat("C", "% Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer);
AppendTextCellFormat("D", "Flag", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer);
AppendTextCellFormat("E", "Group", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer);
AppendTextCellFormat("F", "Hours Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer);
AppendTextCellFormat("G", "% Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer);
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dtDetail.Columns[colInx];
//AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
}
writer.WriteEndElement();
// End of header row
// Now, step through each row of data in our DataTable...
uint rowIndex = 11;
double cellNumericValue = 0;
foreach (DataRow dr in dtDetail.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
// Create cell with data
if (IsNumericColumn[colInx])
{
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
if (cellNumericValue >= 0)
{
cellValue = ((float)cellNumericValue).ToString("0.00");
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
else
{
cellValue = string.Empty;
//AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, false, 9, ref writer);
}
}
}
else if (IsDateColumn[colInx])
{
DateTime dtValue = DateTime.Parse(cellValue);
string strValue = dtValue.ToShortDateString();
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer);
}
else if (colInx == 0)
{
AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, false, 9, ref writer);
}
else
{
//AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
string cellValue = "";
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
}
writer.WriteEndElement();
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
if (IsNumericColumn[colInx])
{
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
else if (IsDateColumn[colInx])
{
DateTime dtValue;
string strValue = "";
if (DateTime.TryParse(cellValue, out dtValue))
{
strValue = dtValue.ToShortDateString();
}
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer);
}
else
{
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
}
public void StartWriteHead()
{
_writer.WriteStartElement(new Worksheet());
_writer.WriteStartElement(new SheetData());
}
/// <summary>
/// 表格内容
/// </summary>
/// <param name="worksheetPart"></param>
private void GenerateWorksheetPartContent(WorksheetPart worksheetPart)
{
using (OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart))
{
// worksheet 开始
writer.WriteStartElement(new Worksheet());
// 是否有自定义列
bool hasCustomColume = _columnInfos.Any(x => x.Width != null);
if (hasCustomColume)
{
// cols 开始
writer.WriteStartElement(new Columns());
}
int columnCount = _columnInfos.Count;
// 表头
Row headRow = new Row()
{
RowIndex = 1, Spans = new ListValue <StringValue>()
{
InnerText = $"1:{columnCount}"
}
};
Cell[] headCells = new Cell[columnCount];
for (int i = 0; i < columnCount; i++)
{
ColumnInfo citem = _columnInfos[i];
uint cindex = (uint)i + 1;
if (citem.Width != null)
{
var column = new Column()
{
Min = cindex, Max = cindex, Width = citem.Width.Value, BestFit = true, CustomWidth = true
};
// col
writer.WriteElement(column);
}
Cell cell = ConstructHeadCell(citem.Show);
cell.CellReference = CellReferenceUtil.GetCellReference(0, (uint)i);
headCells[i] = cell;
}
headRow.Append(headCells);
if (hasCustomColume)
{
// cols 结束
writer.WriteEndElement();
}
// sheetData 开始
writer.WriteStartElement(new SheetData());
// 写入表头行
writer.WriteElement(headRow);
// 写入数据
uint rowIndex = 2;
foreach (T data in _sourceDatas)
{
Row row = new Row()
{
RowIndex = rowIndex, Spans = new ListValue <StringValue>()
{
InnerText = $"1:{columnCount}"
}
};
// row 开始
writer.WriteStartElement(row);
for (int k = 0; k < columnCount; k++)
{
ColumnInfo citem = _columnInfos[k];
PropertyInfo pi = _propertyDic[citem.PropertyName];
object v = pi.GetValue(data, null);
Cell cell = ConstructCell(v, citem.FormatString, pi.PropertyType);
cell.CellReference = CellReferenceUtil.GetCellReference(rowIndex - 1, (uint)k);
// 写入 c
writer.WriteElement(cell);
}
// row 结束
writer.WriteEndElement();
rowIndex++;
}
// sheetData 结束
writer.WriteEndElement();
// worksheet 结束
writer.WriteEndElement();
writer.Close();
}
}
private void WriteSheetData(OpenXmlWriter writer)
{
writer.WriteStartElement(new SheetData());
foreach (var rowItem in _cachedCells)
{
uint rowIdx = rowItem.Key;
var cells = rowItem.Value;
var rowAttrs = EnumRowAttributes(rowIdx, cells).ToList();
if (rowAttrs.Count > 0 || cells.Count > 0)
{
writer.WriteStartElement(new Row(), rowAttrs);
foreach (var cellItem in cells)
{
uint colIdx = cellItem.Key;
CellProxy cellProxy = cellItem.Value;
writer.WriteStartElement(new Cell(), EnumCellProxyAttributes(rowIdx, colIdx, cellProxy));
if (cellProxy.Formula != null)
{
CellFormula cf = new CellFormula(cellProxy.Formula.Text);
if (cellProxy.Formula.R1 != null)
{
cf.R1 = cellProxy.Formula.R1;
}
if (cellProxy.Formula.R2 != null)
{
cf.R2 = cellProxy.Formula.R2;
}
if (cellProxy.Formula.Reference != null)
{
cf.Reference = cellProxy.Formula.Reference;
}
if (cellProxy.Formula.AlwaysCalculateArray != null)
{
cf.AlwaysCalculateArray = cellProxy.Formula.AlwaysCalculateArray;
}
if (cellProxy.Formula.Bx != null)
{
cf.Bx = cellProxy.Formula.Bx;
}
if (cellProxy.Formula.CalculateCell != null)
{
cf.CalculateCell = cellProxy.Formula.CalculateCell;
}
if (cellProxy.Formula.DataTable2D != null)
{
cf.DataTable2D = cellProxy.Formula.DataTable2D;
}
if (cellProxy.Formula.DataTableRow != null)
{
cf.DataTableRow = cellProxy.Formula.DataTableRow;
}
if (cellProxy.Formula.FormulaType != null)
{
cf.FormulaType = cellProxy.Formula.FormulaType;
}
if (cellProxy.Formula.Input1Deleted != null)
{
cf.Input1Deleted = cellProxy.Formula.Input1Deleted;
}
if (cellProxy.Formula.Input2Deleted != null)
{
cf.Input2Deleted = cellProxy.Formula.Input2Deleted;
}
if (cellProxy.Formula.SharedIndex != null)
{
cf.SharedIndex = cellProxy.Formula.SharedIndex;
}
writer.WriteElement(cf);
}
if (cellProxy.Value != null)
{
writer.WriteElement(new CellValue(cellProxy.SerializedValue));
}
writer.WriteEndElement(); // c
}
writer.WriteEndElement(); // row
}
}
writer.WriteEndElement(); // sheetData
}
internal override void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location)
{
DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>();
writer.WriteStartElement(new Drawing()
{
Id = part.GetIdOfPart(drawingsPart)
});
writer.WriteEndElement();
ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
chartPart.ChartSpace = new ChartSpace();
chartPart.ChartSpace.Append(new EditingLanguage()
{
Val = new StringValue("en-US")
});
Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart());
chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted()
{
Val = false
});
// Create a new clustered column chart.
PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea());
Layout layout1 = plotArea.AppendChild <Layout>(new Layout());
BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart());
barChart.Append(new BarDirection()
{
Val = BarDirectionValues.Bar
});
barChart.Append(new BarGrouping()
{
Val = BarGroupingValues.Stacked
});
barChart.Append(new GapWidth()
{
Val = (UInt16Value)75U
});
barChart.Append(new Overlap()
{
Val = 100
});
GanttTypeChart ganttChart = new GanttTypeChart(UserSettings);
var groupedData = GanttData
.GroupBy(x => x.Name);
List <GanttDataPairedSeries> ganttDataWithSeries = new List <GanttDataPairedSeries>();
for (int i = 0; i < groupedData.Max(x => x.Count()); i++)
{
// For each series create a hidden one for spacing.
BarChartSeries barChartSeriesHidden = barChart.AppendChild <BarChartSeries>(new BarChartSeries(
new Index()
{
Val = new UInt32Value((uint)(i * 2))
},
new Order()
{
Val = new UInt32Value((uint)(i * 2))
},
new SeriesText(new NumericValue()
{
Text = "Not Active"
})));
BarChartSeries barChartSeriesValue = barChart.AppendChild <BarChartSeries>(new BarChartSeries(
new Index()
{
Val = new UInt32Value((uint)(i * 2) + 1)
},
new Order()
{
Val = new UInt32Value((uint)(i * 2) + 1)
},
new SeriesText(new NumericValue()
{
Text = "Time Spent"
})));
ganttChart.SetChartShapeProperties(barChartSeriesHidden, visible: false);
ganttChart.SetChartShapeProperties(barChartSeriesValue, colorPoints: (uint)GanttData.Count);
var ganttData = new List <GanttData>();
foreach (var data in groupedData.Where(x => x.Count() >= i + 1))
{
ganttData.Add(data.ElementAt(i));
}
ganttDataWithSeries.Add(new GanttDataPairedSeries()
{
BarChartSeriesHidden = barChartSeriesHidden,
BarChartSeriesValue = barChartSeriesValue,
Values = ganttData
});
}
ganttChart.SetChartAxis(ganttDataWithSeries, groupedData.ToList());
barChart.Append(new AxisId()
{
Val = new UInt32Value(48650112u)
});
barChart.Append(new AxisId()
{
Val = new UInt32Value(48672768u)
});
// Add the Category Axis (X axis).
ganttChart.SetGanttCategoryAxis(plotArea);
// Add the Value Axis (Y axis).
ganttChart.SetGanttValueAxis(plotArea, GanttData.Min(x => x.Start), GanttData.Max(x => x.End));
chartContainer.Append(new PlotVisibleOnly()
{
Val = new BooleanValue(true)
});
ganttChart.SetChartLocation(drawingsPart, chartPart, location);
}
public int write(List <List <IWritable> > dataList)
{
int retvalueTemp = 0;
if (_doc != null && _workBook != null && _workSheet != null)
{
string originalPartId = _workBook.GetIdOfPart(_workSheet);
WorksheetPart replacementPart = _workBook.AddNewPart <WorksheetPart>();
string replacementPartId = _workBook.GetIdOfPart(replacementPart);
_reader = OpenXmlReader.Create(_workSheet);
_writer = OpenXmlWriter.Create(replacementPart);
while (_reader.Read())
{
if (_reader.ElementType == typeof(Selection))
{
continue;
}
if (_reader.ElementType == typeof(SheetData))
{
if (_reader.IsStartElement)
{
_writer.WriteStartElement(_reader);
continue;
}
// append section begins
//_writer.WriteStartElement(new SheetData()); // beginning of sheetdata
for (int rowIndex = 0; rowIndex < dataList.Count; rowIndex++)
{
Row row = new Row();
_writer.WriteStartElement(row); // begining of row
for (int colIndex = 0; colIndex < dataList.Count; colIndex++)
{
Cell cell = new Cell();
cell.CellValue = new CellValue(dataList[rowIndex][colIndex].ToString());
_writer.WriteElement(cell);
}
_writer.WriteEndElement(); // end of row
}
_writer.WriteEndElement(); // end of sheetdata
}
else
{
if (_reader.IsStartElement)
{
_writer.WriteStartElement(_reader);
if (_reader.ElementType == typeof(CellValue))
{
_writer.WriteString(_reader.GetText());
}
}
else if (_reader.IsEndElement)
{
_writer.WriteEndElement();
}
else
{
}
}
}
_writer.Close();
_reader.Close();
Sheet sheet = _workBook.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(originalPartId)).First();
sheet.Id.Value = replacementPartId;
_workBook.DeletePart(_workSheet);
}
else
{
retvalueTemp = -1;
}
return(retvalueTemp);
}
public static void GetDataResultsAdjust(IList <RecordAdjust> lstRecord, string filePath, string templatePath, string status, string fromdate, string todate)
{
if ((!File.Exists(templatePath)))
{
//If we do not have a template file, we need to create one
CreateSpreadsheetWorkbook(templatePath);
}
//Copy the template file to the output file location
File.Copy(templatePath, filePath, true);
//Open the copied file
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, true))
{
//Navigate to the workbook part
WorkbookPart workbookPart = myDoc.WorkbookPart;
//open the first worksheet
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
//Get the id of this sheet. We need this because we are going to add a new
//worksheet to this workbook, then we are going to delete this worksheet
//This ID will tell us which one to delete
string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
//Add the new worksheet
WorksheetPart replacementPart = workbookPart.AddNewPart <WorksheetPart>();
//This is the ID of the new worksheet
string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
//We are going to read from the original worksheet to get the
//templated items that we added to the worksheet in the traditional way
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
//We are goint to copy the items from the original worksheet by using
//an XML writer, this overcomes the memory limitations of having
//an extremely large dataset.
OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);
//The template does not have any data so we will be creating new rows and cells
//Then writing them using XML.
Row r = new Row();
Cell c = new Cell();
while ((reader.Read()))
{
//This iterates through the sheet data and copies it
if ((object.ReferenceEquals(reader.ElementType, typeof(SheetData))))
{
//Exit the loop if we hit a sheetdata end element
if ((reader.IsEndElement))
{
break; // TODO: might not be correct. Was : Exit While
}
//We create a new sheetdata element (basically this is the reoot container for a sheet)
writer.WriteStartElement(new SheetData());
if (status == "0")
{
int rowIndex2 = 2;
r.RowIndex = (UInt32)rowIndex2;
writer.WriteStartElement(r);
c = CreateCell(5, "BÁO CÁO HÓA ĐƠN THAY THẾ", rowIndex2);
writer.WriteElement(c);
writer.WriteEndElement();
}
else if (status == "1")
{
int rowIndex2 = 2;
r.RowIndex = (UInt32)rowIndex2;
writer.WriteStartElement(r);
c = CreateCell(5, "BÁO CÁO HÓA ĐƠN ĐIỀU CHỈNH", rowIndex2);
writer.WriteElement(c);
writer.WriteEndElement();
}
int rowIndextieudea = 3;
r.RowIndex = (UInt32)rowIndextieudea;
writer.WriteStartElement(r);
c = CreateCell(0, "Từ ngày:" + fromdate, rowIndextieudea);
writer.WriteElement(c);
writer.WriteEndElement();
int rowIndextieudeb = 4;
r.RowIndex = (UInt32)rowIndextieudeb;
writer.WriteStartElement(r);
c = CreateCell(0, "Đến ngày:" + todate, rowIndextieudeb);
writer.WriteElement(c);
writer.WriteEndElement();
int rowIndextieudec = 5;
r.RowIndex = (UInt32)rowIndextieudec;
writer.WriteStartElement(r);
c = CreateCell(0, "Tổng số hóa đơn:" + lstRecord.Count(), rowIndextieudec);
writer.WriteElement(c);
writer.WriteEndElement();
int rowIndex = 6;
r.RowIndex = (UInt32)rowIndex;
//Start the first row.
writer.WriteStartElement(r);
//Iterate through the columns to write a row conaining the column names
c = CreateCell(0, "STT", rowIndex);
writer.WriteElement(c);
c = CreateCell(1, "Ký hiệu mẫu", rowIndex);
writer.WriteElement(c);
c = CreateCell(2, "Ký hiệu hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(3, "Số hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(4, "Khách hàng", rowIndex);
writer.WriteElement(c);
c = CreateCell(5, "Ký hiệu mẫu", rowIndex);
writer.WriteElement(c);
c = CreateCell(6, "Ký hiệu hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(7, "Số hóa đơn", rowIndex);
writer.WriteElement(c);
c = CreateCell(8, "Khách hàng", rowIndex);
writer.WriteElement(c);
c = CreateCell(9, "Người thực hiện", rowIndex);
writer.WriteElement(c);
c = CreateCell(10, "Ngày thực hiện", rowIndex);
writer.WriteElement(c);
c = CreateCell(11, "Trạng thái", rowIndex);
writer.WriteElement(c);
//End first row
writer.WriteEndElement();
foreach (var item in lstRecord)
{
if (item == null)
{
continue;
}
rowIndex = rowIndex + 1;
r.RowIndex = (UInt32)rowIndex;
writer.WriteStartElement(r);
//iterate through the columns to write their data
c = CreateCell(0, item.stt.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(1, item.patternOlder.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(2, item.serialOlder.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(3, string.Format("{0:0000000}", Convert.ToInt32(item.noOlder)), rowIndex); writer.WriteElement(c);
//c = CreateCell(4, item.cusnameOlder.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(5, item.patternNew.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(6, item.serialNew.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(7, string.Format("{0:0000000}", Convert.ToInt32(item.noNew)), rowIndex); writer.WriteElement(c);
c = CreateCell(8, item.cusnameNew.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(9, item.username.ToString(), rowIndex); writer.WriteElement(c);
c = CreateCell(10, String.Format("{0:dd/MM/yyyy}", DateTime.Parse(item.proccessdate)), rowIndex); writer.WriteElement(c);
if (item.status == 1)
{
c = CreateCell(11, "Lập hóa đơn thay thế", rowIndex); writer.WriteElement(c);
}
else if (item.status == 2)
{
c = CreateCell(11, "Lập hóa đơn điều chỉnh", rowIndex); writer.WriteElement(c);
}
//write the end of the row
writer.WriteEndElement();
}
writer.WriteEndElement();
}
else if ((reader.IsStartElement))
{
//Start elements are directly copied
writer.WriteStartElement(reader);
}
else if ((reader.IsEndElement))
{
//End elements are directly copied
writer.WriteEndElement();
}
}
//Close the reader and writer
reader.Close();
writer.Close();
//Get the newly created sheet (same id as the old sheet, but it is the first one)
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First();
//Assign it the new sheet id
sheet.Id.Value = replacementPartId;
//remove the old sheet
workbookPart.DeletePart(worksheetPart);
//Done
myDoc.Close();
//Delete template file
if (File.Exists(templatePath))
{
File.Delete(templatePath);
}
}
}
public static MemoryStream ExportDSToExcel(DataSet ds)
{
MemoryStream StreamSpreadsheet = new MemoryStream();
try
{
using (var spreadsheetDocument = SpreadsheetDocument.Create(StreamSpreadsheet, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
// Instantiate a Workbook in the WorkbookPart\
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
// Declaring Writer to the WorksheetPart on using statement
using OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
// Declaring row and cell objects to reuse in the construction of the Sheet
Row row = null;
Cell cell = null;
// For each table, generate a sheet and fill it with the correspondent data
foreach (DataTable table in ds.Tables)
{
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = table.TableName
};
sheets.Append(sheet);
writer.WriteStartElement(new Worksheet()); // Writes init tag for worksheet
writer.WriteStartElement(new SheetData()); // Writes init tag for sheetData
#region Header Row
row = new Row();
writer.WriteStartElement(row); // Init tag Row(Header)
List <string> columns = new List <string>(); // Used for searching values in the DataTable
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
writer.WriteElement(cell);
}
writer.WriteEndElement(); // End tag Row(Header)
#endregion
#region Common rows
foreach (DataRow dsrow in table.Rows)
{
row = new Row();
writer.WriteStartElement(row); // Init tag Row
foreach (string col in columns)
{
cell = new Cell();
// Checks and sets data type for the cell according to the DataTable data type
if (dsrow[col] is int)
{
cell.DataType = CellValues.Number;
}
else if (dsrow[col] is DateTime)
{
cell.DataType = CellValues.Date;
}
else if (dsrow[col] is bool)
{
cell.DataType = CellValues.Boolean;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow[col].ToString());
writer.WriteElement(cell);
}
writer.WriteEndElement(); // End tag Row
}
#endregion
writer.WriteEndElement(); // End tag SheetData
writer.WriteEndElement(); // End tag Worksheet
workbookpart.Workbook.Save();
writer.Dispose();
spreadsheetDocument.Save();
spreadsheetDocument.Close();
}
}
}
catch (Exception)
{
throw;
}
return(StreamSpreadsheet);
}
/// <summary>
/// CellValues = Boolean -> expects cellValue "True" or "False"
/// CellValues = InlineString -> stores string within sheet
/// CellValues = SharedString -> stores index within sheet. If this is called, please call CreateShareStringPart after creating all sheet data to create the shared string part
/// CellValues = Date -> expects ((DateTime)value).ToOADate().ToString(CultureInfo.InvariantCulture) as cellValue
/// and new OpenXmlAttribute[] { new OpenXmlAttribute("s", null, "1") }.ToList() as attributes so that the correct formatting can be applied
///
/// </summary>
/// <param name="writer"></param>
/// <param name="cellValue"></param>
/// <param name="dataType"></param>
/// <param name="attributes"></param>
public void WriteCellValueSax(OpenXmlWriter writer, string cellValue, CellValues dataType, List <OpenXmlAttribute> attributes = null)
{
switch (dataType)
{
case CellValues.InlineString:
{
if (attributes == null)
{
attributes = new List <OpenXmlAttribute>();
}
attributes.Add(new OpenXmlAttribute("t", null, "inlineStr"));
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new InlineString(new Text(cellValue)));
writer.WriteEndElement();
break;
}
case CellValues.SharedString:
{
if (attributes == null)
{
attributes = new List <OpenXmlAttribute>();
}
attributes.Add(new OpenXmlAttribute("t", null, "s")); //shared string type
writer.WriteStartElement(new Cell(), attributes);
if (!_shareStringDictionary.ContainsKey(cellValue))
{
_shareStringDictionary.Add(cellValue, _shareStringMaxIndex);
_shareStringMaxIndex++;
}
//writing the index as the cell value
writer.WriteElement(new CellValue(_shareStringDictionary[cellValue].ToString()));
writer.WriteEndElement(); //cell
break;
}
case CellValues.Date:
{
if (attributes == null)
{
writer.WriteStartElement(new Cell()
{
DataType = CellValues.Number
});
}
else
{
writer.WriteStartElement(new Cell()
{
DataType = CellValues.Number
}, attributes);
}
writer.WriteElement(new CellValue(cellValue));
writer.WriteEndElement();
break;
}
case CellValues.Boolean:
{
if (attributes == null)
{
attributes = new List <OpenXmlAttribute>();
}
attributes.Add(new OpenXmlAttribute("t", null, "b")); //boolean type
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(cellValue == "True" ? "1" : "0"));
writer.WriteEndElement();
break;
}
default:
{
if (attributes == null)
{
writer.WriteStartElement(new Cell()
{
DataType = dataType
});
}
else
{
writer.WriteStartElement(new Cell()
{
DataType = dataType
}, attributes);
}
writer.WriteElement(new CellValue(cellValue));
writer.WriteEndElement();
break;
}
}
}
private void WriteCommentPart(WorksheetCommentsPart wcp, VmlDrawingPart vdp)
{
List <SLCellPoint> listCommentKeys = slws.Comments.Keys.ToList <SLCellPoint>();
listCommentKeys.Sort(new SLCellReferencePointComparer());
bool bAuthorFound = false;
int iAuthorIndex = 0;
SLComment comm;
int i = 0;
SLRowProperties rp;
SLColumnProperties cp;
SLCellPoint pt;
// just in case
if (slws.Authors.Count == 0)
{
if (this.DocumentProperties.Creator.Length > 0)
{
slws.Authors.Add(this.DocumentProperties.Creator);
}
else
{
slws.Authors.Add(SLConstants.ApplicationName);
}
}
int iDataRange = 1;
// hah! optional... we'll see...
int iOptionalShapeTypeId = 202;
string sShapeTypeId = string.Format("_x0000_t{0}", iOptionalShapeTypeId);
int iShapeIdBase = iDataRange * 1024;
int iRowID = 0;
int iColumnID = 0;
double fRowRemainder = 0;
double fColumnRemainder = 0;
long lRowEMU = 0;
long lColumnEMU = 0;
long lRowRemainder = 0;
long lColumnRemainder = 0;
int iEMU = 0;
double fMargin = 0;
double fFrac = 0;
int iFrac = 0;
string sFrac = string.Empty;
ImagePart imgp;
string sFileName = string.Empty;
// image data in base 64, relationship ID
Dictionary <string, string> dictImageData = new Dictionary <string, string>();
// not supporting existing VML drawings. But if supporting, process the VmlDrawingPart for
// ImageParts.
// Apparently, Excel chokes if a "non-standard" relationship ID is given
// to VML drawings. It seems to only accept the form "rId{num}", and {num} seems
// to have to start from 1. I don't know if Excel will also choke if you jump
// from 1 to 3, but I *do* know you can't even start from rId3.
// Excel VML seems to be particularly strict on this...
// The error originated by having a "non-standard" relationship ID for a
// VML image. Say "R2dk723lgsjg2" or whatever. Then fire up Excel and open
// that spreadsheet. Then save. Then open it again. You'll get an error.
// Apparently, Excel will put "rId1" on the tag o:relid. The error is that
// the original o:relid with "R2dk723lgsjg2" as the value is still there.
// Meaning the o:relid attribute is duplicated, hence the error.
// Why don't I just use the number of vdp.Parts or even vdp.ImageParts to
// get the next valid relationship ID? I don't know. Paranoia?
// The existing relationship IDs *might* be in sequential order, but you never
// know what Excel accepts... If you can get me the Microsoft Excel developer
// who can explain this, I'll gladly change the algorithm...
// So why the dictionary? Apparently, Excel also chokes if there are duplicates of
// the VML image. So even 2 unique relationship IDs that *happens* to have identical
// image data will tie Excel into knots. I am so upset with Excel right now...
// I know it will keep file size down if only unique image data is stored, but still...
StringBuilder sbVml = new StringBuilder();
sbVml.Append("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
sbVml.Append("<o:shapelayout v:ext=\"edit\">");
sbVml.AppendFormat("<o:idmap v:ext=\"edit\" data=\"{0}\"/>", iDataRange);
sbVml.Append("</o:shapelayout>");
sbVml.AppendFormat("<v:shapetype id=\"{0}\" coordsize=\"21600,21600\" o:spt=\"{1}\" path=\"m,l,21600r21600,l21600,xe\">", sShapeTypeId, iOptionalShapeTypeId);
sbVml.Append("<v:stroke joinstyle=\"miter\"/><v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>");
sbVml.Append("</v:shapetype>");
using (OpenXmlWriter oxwComment = OpenXmlWriter.Create(wcp))
{
oxwComment.WriteStartElement(new Comments());
oxwComment.WriteStartElement(new Authors());
for (i = 0; i < slws.Authors.Count; ++i)
{
oxwComment.WriteElement(new Author(slws.Authors[i]));
}
oxwComment.WriteEndElement();
oxwComment.WriteStartElement(new CommentList());
for (i = 0; i < listCommentKeys.Count; ++i)
{
pt = listCommentKeys[i];
comm = slws.Comments[pt];
bAuthorFound = false;
for (iAuthorIndex = 0; iAuthorIndex < slws.Authors.Count; ++iAuthorIndex)
{
if (comm.Author.Equals(slws.Authors[iAuthorIndex]))
{
bAuthorFound = true;
break;
}
}
if (!bAuthorFound)
{
iAuthorIndex = 0;
}
oxwComment.WriteStartElement(new Comment()
{
Reference = SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex),
AuthorId = (uint)iAuthorIndex
});
oxwComment.WriteElement(comm.rst.ToCommentText());
oxwComment.WriteEndElement();
sbVml.AppendFormat("<v:shape id=\"_x0000_s{0}\" type=\"#{1}\"", iShapeIdBase + i + 1, sShapeTypeId);
sbVml.Append(" style='position:absolute;");
if (!comm.HasSetPosition)
{
comm.Top = pt.RowIndex - 1 + SLConstants.DefaultCommentTopOffset;
comm.Left = pt.ColumnIndex + SLConstants.DefaultCommentLeftOffset;
if (comm.Top < 0)
{
comm.Top = 0;
}
if (comm.Left < 0)
{
comm.Left = 0;
}
}
if (comm.UsePositionMargin)
{
sbVml.AppendFormat("margin-left:{0}pt;", comm.LeftMargin.ToString("0.##", CultureInfo.InvariantCulture));
sbVml.AppendFormat("margin-top:{0}pt;", comm.TopMargin.ToString("0.##", CultureInfo.InvariantCulture));
}
else
{
iRowID = (int)Math.Floor(comm.Top);
fRowRemainder = comm.Top - iRowID;
iColumnID = (int)Math.Floor(comm.Left);
fColumnRemainder = comm.Left - iColumnID;
lRowEMU = 0;
lColumnEMU = 0;
for (iEMU = 1; iEMU <= iRowID; ++iEMU)
{
if (slws.RowProperties.ContainsKey(iEMU))
{
rp = slws.RowProperties[iEMU];
lRowEMU += rp.HeightInEMU;
}
else
{
lRowEMU += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
if (slws.RowProperties.ContainsKey(iRowID + 1))
{
rp = slws.RowProperties[iRowID + 1];
lRowRemainder = Convert.ToInt64(fRowRemainder * rp.HeightInEMU);
lRowEMU += lRowRemainder;
}
else
{
lRowRemainder = Convert.ToInt64(fRowRemainder * slws.SheetFormatProperties.DefaultRowHeightInEMU);
lRowEMU += lRowRemainder;
}
for (iEMU = 1; iEMU <= iColumnID; ++iEMU)
{
if (slws.ColumnBreaks.ContainsKey(iEMU))
{
cp = slws.ColumnProperties[iEMU];
lColumnEMU += cp.WidthInEMU;
}
else
{
lColumnEMU += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
if (slws.ColumnProperties.ContainsKey(iColumnID + 1))
{
cp = slws.ColumnProperties[iColumnID + 1];
lColumnRemainder = Convert.ToInt64(fColumnRemainder * cp.WidthInEMU);
lColumnEMU += lColumnRemainder;
}
else
{
lColumnRemainder = Convert.ToInt64(fColumnRemainder * slws.SheetFormatProperties.DefaultColumnWidthInEMU);
lColumnEMU += lColumnRemainder;
}
fMargin = (double)lColumnEMU / (double)SLConstants.PointToEMU;
sbVml.AppendFormat("margin-left:{0}pt;", fMargin.ToString("0.##", CultureInfo.InvariantCulture));
fMargin = (double)lRowEMU / (double)SLConstants.PointToEMU;
sbVml.AppendFormat("margin-top:{0}pt;", fMargin.ToString("0.##", CultureInfo.InvariantCulture));
}
if (comm.AutoSize)
{
sbVml.Append("width:auto;height:auto;");
}
else
{
sbVml.AppendFormat("width:{0}pt;", comm.Width.ToString("0.##", CultureInfo.InvariantCulture));
sbVml.AppendFormat("height:{0}pt;", comm.Height.ToString("0.##", CultureInfo.InvariantCulture));
}
sbVml.AppendFormat("z-index:{0};", i + 1);
sbVml.AppendFormat("visibility:{0}'", comm.Visible ? "visible" : "hidden");
if (!comm.Fill.HasFill)
{
// use #ffffff ?
sbVml.Append(" fillcolor=\"window [65]\"");
}
else if (comm.Fill.Type == SLA.SLFillType.NoFill)
{
sbVml.Append(" filled=\"f\"");
sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"",
comm.Fill.SolidColor.DisplayColor.R.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.G.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.B.ToString("x2"));
}
else if (comm.Fill.Type == SLA.SLFillType.SolidFill)
{
sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"",
comm.Fill.SolidColor.DisplayColor.R.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.G.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.B.ToString("x2"));
}
else if (comm.Fill.Type == SLA.SLFillType.GradientFill)
{
if (comm.Fill.GradientColor.GradientStops.Count > 0)
{
sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"",
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.R.ToString("x2"),
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.G.ToString("x2"),
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.B.ToString("x2"));
}
}
else if (comm.Fill.Type == SLA.SLFillType.BlipFill)
{
// don't have to do anything
}
else if (comm.Fill.Type == SLA.SLFillType.PatternFill)
{
sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"",
comm.Fill.PatternForegroundColor.DisplayColor.R.ToString("x2"),
comm.Fill.PatternForegroundColor.DisplayColor.G.ToString("x2"),
comm.Fill.PatternForegroundColor.DisplayColor.B.ToString("x2"));
}
if (comm.LineColor != null)
{
sbVml.AppendFormat(" strokecolor=\"#{0}{1}{2}\"",
comm.LineColor.Value.R.ToString("x2"),
comm.LineColor.Value.G.ToString("x2"),
comm.LineColor.Value.B.ToString("x2"));
}
if (comm.fLineWeight != null)
{
sbVml.AppendFormat(" strokeweight=\"{0}pt\"", comm.fLineWeight.Value.ToString("0.##", CultureInfo.InvariantCulture));
}
sbVml.Append(" o:insetmode=\"auto\">");
sbVml.Append("<v:fill");
if (comm.Fill.Type == SLA.SLFillType.SolidFill || comm.Fill.Type == SLA.SLFillType.GradientFill)
{
if (comm.Fill.Type == SLA.SLFillType.SolidFill)
{
fFrac = 100.0 - (double)comm.Fill.SolidColor.Transparency;
}
else
{
fFrac = 100.0 - comm.bFromTransparency;
}
iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0);
if (iFrac <= 0)
{
sFrac = "0";
}
else if (iFrac >= 65536)
{
sFrac = "1";
}
else
{
sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture));
}
// default is 1
if (!sFrac.Equals("1"))
{
sbVml.AppendFormat(" opacity=\"{0}\"", sFrac);
}
}
if (comm.Fill.Type == SLA.SLFillType.SolidFill)
{
sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"",
comm.Fill.SolidColor.DisplayColor.R.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.G.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.B.ToString("x2"));
}
else if (comm.Fill.Type == SLA.SLFillType.GradientFill)
{
if (comm.Fill.GradientColor.GradientStops.Count > 0)
{
sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"",
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.R.ToString("x2"),
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.G.ToString("x2"),
comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.B.ToString("x2"));
}
else
{
// shouldn't happen, but you know, in case...
sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"",
comm.Fill.SolidColor.DisplayColor.R.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.G.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.B.ToString("x2"));
}
fFrac = 100.0 - comm.bToTransparency;
iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0);
if (iFrac <= 0)
{
sFrac = "0";
}
else if (iFrac >= 65536)
{
sFrac = "1";
}
else
{
sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture));
}
// default is 1
if (!sFrac.Equals("1"))
{
sbVml.AppendFormat(" o:opacity=\"{0}\"", sFrac);
}
sbVml.Append(" rotate=\"t\"");
if (comm.Fill.GradientColor.GradientStops.Count > 0)
{
sbVml.Append(" colors=\"");
for (int iGradient = 0; iGradient < comm.Fill.GradientColor.GradientStops.Count; ++iGradient)
{
// you take the position/gradient value straight
fFrac = (double)comm.Fill.GradientColor.GradientStops[iGradient].Position;
iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0);
if (iFrac <= 0)
{
sFrac = "0";
}
else if (iFrac >= 65536)
{
sFrac = "1";
}
else
{
sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture));
}
if (iGradient > 0)
{
sbVml.Append(";");
}
sbVml.AppendFormat("{0} #{1}{2}{3}", sFrac,
comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.R.ToString("x2"),
comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.G.ToString("x2"),
comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.B.ToString("x2"));
}
sbVml.Append("\"");
}
if (comm.Fill.GradientColor.IsLinear)
{
// use temporarily
// VML increases angles in counter-clockwise direction,
// otherwise we'd just use the angle straight from the property
//...fFrac = 360.0 - (double)comm.Fill.GradientColor.Angle;
fFrac = (double)comm.Fill.GradientColor.Angle;
sbVml.AppendFormat(" angle=\"{0}\"", fFrac.ToString("0.##", CultureInfo.InvariantCulture));
sbVml.Append(" focus=\"100%\" type=\"gradient\"");
}
else
{
switch (comm.Fill.GradientColor.PathType)
{
case A.PathShadeValues.Shape:
sbVml.Append(" focusposition=\"50%,50%\" focus=\"100%\" type=\"gradientradial\"");
break;
case A.PathShadeValues.Rectangle:
case A.PathShadeValues.Circle:
// because there's no way to do a circular gradient with VML...
switch (comm.Fill.GradientColor.Direction)
{
case SLA.SLGradientDirectionValues.Center:
sbVml.Append(" focusposition=\"50%,50%\"");
break;
case SLA.SLGradientDirectionValues.CenterToBottomLeftCorner:
// so the "centre" is at the top-right
sbVml.Append(" focusposition=\"100%,0%\"");
break;
case SLA.SLGradientDirectionValues.CenterToBottomRightCorner:
// so the "centre" is at the top-left
sbVml.Append(" focusposition=\"0%,0%\"");
break;
case SLA.SLGradientDirectionValues.CenterToTopLeftCorner:
// so the "centre" is at the bottom-right
sbVml.Append(" focusposition=\"100%,100%\"");
break;
case SLA.SLGradientDirectionValues.CenterToTopRightCorner:
// so the "centre" is at the bottom-left
sbVml.Append(" focusposition=\"0%,100%\"");
break;
}
sbVml.Append(" focus=\"100%\" type=\"gradientradial\"");
break;
}
}
}
else if (comm.Fill.Type == SLA.SLFillType.BlipFill)
{
string sRelId = "rId1";
using (FileStream fs = new FileStream(comm.Fill.BlipFileName, FileMode.Open))
{
byte[] ba = new byte[fs.Length];
fs.Read(ba, 0, ba.Length);
string sImageData = Convert.ToBase64String(ba);
if (dictImageData.ContainsKey(sImageData))
{
sRelId = dictImageData[sImageData];
comm.Fill.BlipRelationshipID = sRelId;
}
else
{
// if we haven't found a viable relationship ID by 10 million iterations,
// then we have serious issues...
for (int iIDNum = 1; iIDNum <= SLConstants.VmlTenMillionIterations; ++iIDNum)
{
sRelId = string.Format("rId{0}", iIDNum.ToString(CultureInfo.InvariantCulture));
// we could use a hashset to store the relationship IDs so we
// don't use the ContainsValue() because ContainsValue() is supposedly
// slow... I'm not gonna care because if this algorithm slows enough
// that ContainsValue() is inefficient, that means there are enough VML
// drawings to choke a modestly sized art museum.
if (!dictImageData.ContainsValue(sRelId))
{
break;
}
}
imgp = vdp.AddImagePart(SLA.SLDrawingTool.GetImagePartType(comm.Fill.BlipFileName), sRelId);
fs.Position = 0;
imgp.FeedData(fs);
comm.Fill.BlipRelationshipID = vdp.GetIdOfPart(imgp);
dictImageData[sImageData] = sRelId;
}
}
sbVml.AppendFormat(" o:relid=\"{0}\"", comm.Fill.BlipRelationshipID);
// all this to get from "myawesomepicture.jpg" to "myawesomepicture"
sFileName = comm.Fill.BlipFileName;
// use temporarily
iFrac = sFileName.LastIndexOfAny("\\/".ToCharArray());
sFileName = sFileName.Substring(iFrac + 1);
iFrac = sFileName.LastIndexOf(".");
sFileName = sFileName.Substring(0, iFrac);
sbVml.AppendFormat(" o:title=\"{0}\"", sFileName);
sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"",
comm.Fill.SolidColor.DisplayColor.R.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.G.ToString("x2"),
comm.Fill.SolidColor.DisplayColor.B.ToString("x2"));
sbVml.Append(" recolor=\"t\" rotate=\"t\"");
fFrac = 100.0 - (double)comm.Fill.BlipTransparency;
iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0);
if (iFrac <= 0)
{
sFrac = "0";
}
else if (iFrac >= 65536)
{
sFrac = "1";
}
else
{
sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture));
}
// default is 1
if (!sFrac.Equals("1"))
{
sbVml.AppendFormat(" o:opacity=\"{0}\"", sFrac);
}
if (comm.Fill.BlipTile)
{
sbVml.Append(" type=\"tile\"");
sbVml.AppendFormat(" size=\"{0}%,{1}%\"",
comm.Fill.BlipScaleX.ToString("0.##", CultureInfo.InvariantCulture),
comm.Fill.BlipScaleY.ToString("0.##", CultureInfo.InvariantCulture));
}
else
{
sbVml.Append(" type=\"frame\"");
// use temporarily
//fFrac = (50.0 - (double)comm.Fill.BlipLeftOffset) + (50.0 - (double)comm.Fill.BlipRightOffset);
fFrac = 100.0 - (double)comm.Fill.BlipLeftOffset - (double)comm.Fill.BlipRightOffset;
sbVml.AppendFormat(" size=\"{0}%,", fFrac.ToString("0.##", CultureInfo.InvariantCulture));
fFrac = 100.0 - (double)comm.Fill.BlipTopOffset - (double)comm.Fill.BlipBottomOffset;
sbVml.AppendFormat("{0}%\"", fFrac.ToString("0.##", CultureInfo.InvariantCulture));
}
}
else if (comm.Fill.Type == SLA.SLFillType.PatternFill)
{
string sRelId = "rId1";
using (MemoryStream ms = new MemoryStream())
{
using (System.Drawing.Bitmap bm = SLA.SLDrawingTool.GetVmlPatternFill(comm.Fill.PatternPreset))
{
bm.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
}
byte[] ba = new byte[ms.Length];
ms.Read(ba, 0, ba.Length);
string sImageData = Convert.ToBase64String(ba);
if (dictImageData.ContainsKey(sImageData))
{
sRelId = dictImageData[sImageData];
comm.Fill.BlipRelationshipID = sRelId;
}
else
{
// go check the "normal" image part additions for comments...
for (int iIDNum = 1; iIDNum <= SLConstants.VmlTenMillionIterations; ++iIDNum)
{
sRelId = string.Format("rId{0}", iIDNum.ToString(CultureInfo.InvariantCulture));
if (!dictImageData.ContainsValue(sRelId))
{
break;
}
}
imgp = vdp.AddImagePart(ImagePartType.Png, sRelId);
ms.Position = 0;
imgp.FeedData(ms);
comm.Fill.BlipRelationshipID = vdp.GetIdOfPart(imgp);
dictImageData[sImageData] = sRelId;
}
}
sbVml.AppendFormat(" o:relid=\"{0}\"", comm.Fill.BlipRelationshipID);
sbVml.AppendFormat(" o:title=\"{0}\"", SLA.SLDrawingTool.ConvertToVmlTitle(comm.Fill.PatternPreset));
sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"",
comm.Fill.PatternBackgroundColor.DisplayColor.R.ToString("x2"),
comm.Fill.PatternBackgroundColor.DisplayColor.G.ToString("x2"),
comm.Fill.PatternBackgroundColor.DisplayColor.B.ToString("x2"));
sbVml.Append(" recolor=\"t\" type=\"pattern\"");
}
sbVml.Append("/>");
if (comm.LineStyle != StrokeLineStyleValues.Single || comm.vLineDashStyle != null)
{
sbVml.Append("<v:stroke");
switch (comm.LineStyle)
{
case StrokeLineStyleValues.Single:
// don't have to do anything
break;
case StrokeLineStyleValues.ThickBetweenThin:
sbVml.Append(" linestyle=\"thickBetweenThin\"/>");
break;
case StrokeLineStyleValues.ThickThin:
sbVml.Append(" linestyle=\"thickThin\"/>");
break;
case StrokeLineStyleValues.ThinThick:
sbVml.Append(" linestyle=\"thinThick\"/>");
break;
case StrokeLineStyleValues.ThinThin:
sbVml.Append(" linestyle=\"thinThin\"/>");
break;
}
if (comm.vLineDashStyle != null)
{
switch (comm.vLineDashStyle.Value)
{
case SLDashStyleValues.Solid:
sbVml.Append(" dashstyle=\"solid\"/>");
break;
case SLDashStyleValues.ShortDash:
sbVml.Append(" dashstyle=\"shortdash\"/>");
break;
case SLDashStyleValues.ShortDot:
sbVml.Append(" dashstyle=\"shortdot\"/>");
break;
case SLDashStyleValues.ShortDashDot:
sbVml.Append(" dashstyle=\"shortdashdot\"/>");
break;
case SLDashStyleValues.ShortDashDotDot:
sbVml.Append(" dashstyle=\"shortdashdotdot\"/>");
break;
case SLDashStyleValues.Dot:
sbVml.Append(" dashstyle=\"dot\"/>");
break;
case SLDashStyleValues.Dash:
sbVml.Append(" dashstyle=\"dash\"/>");
break;
case SLDashStyleValues.LongDash:
sbVml.Append(" dashstyle=\"longdash\"/>");
break;
case SLDashStyleValues.DashDot:
sbVml.Append(" dashstyle=\"dashdot\"/>");
break;
case SLDashStyleValues.LongDashDot:
sbVml.Append(" dashstyle=\"longdashdot\"/>");
break;
case SLDashStyleValues.LongDashDotDot:
sbVml.Append(" dashstyle=\"longdashdotdot\"/>");
break;
}
}
if (comm.vEndCap != null)
{
switch (comm.vEndCap.Value)
{
case StrokeEndCapValues.Flat:
sbVml.Append(" endcap=\"flat\"/>");
break;
case StrokeEndCapValues.Round:
sbVml.Append(" endcap=\"round\"/>");
break;
case StrokeEndCapValues.Square:
sbVml.Append(" endcap=\"square\"/>");
break;
}
}
sbVml.Append("/>");
}
if (comm.HasShadow)
{
sbVml.AppendFormat("<v:shadow on=\"t\" color=\"#{0}{1}{2}\" obscured=\"t\"/>",
comm.ShadowColor.R.ToString("x2"),
comm.ShadowColor.G.ToString("x2"),
comm.ShadowColor.B.ToString("x2"));
}
sbVml.Append("<v:path o:connecttype=\"none\"/>");
sbVml.Append("<v:textbox style='mso-direction-alt:auto;");
switch (comm.Orientation)
{
case SLCommentOrientationValues.Horizontal:
// don't have to do anything
break;
case SLCommentOrientationValues.TopDown:
sbVml.Append("layout-flow:vertical;mso-layout-flow-alt:top-to-bottom;");
break;
case SLCommentOrientationValues.Rotated270Degrees:
sbVml.Append("layout-flow:vertical;mso-layout-flow-alt:bottom-to-top;");
break;
case SLCommentOrientationValues.Rotated90Degrees:
sbVml.Append("layout-flow:vertical;");
break;
}
if (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft)
{
sbVml.Append("direction:RTL;");
}
// no else because don't have to do anything
if (comm.AutoSize)
{
sbVml.Append("mso-fit-shape-to-text:t;");
}
sbVml.Append("'><div");
if (comm.HorizontalTextAlignment != SLHorizontalTextAlignmentValues.Distributed ||
comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft)
{
sbVml.Append(" style='");
switch (comm.HorizontalTextAlignment)
{
case SLHorizontalTextAlignmentValues.Left:
sbVml.Append("text-align:left;");
break;
case SLHorizontalTextAlignmentValues.Justify:
sbVml.Append("text-align:justify;");
break;
case SLHorizontalTextAlignmentValues.Center:
sbVml.Append("text-align:center;");
break;
case SLHorizontalTextAlignmentValues.Right:
sbVml.Append("text-align:right;");
break;
case SLHorizontalTextAlignmentValues.Distributed:
// don't have to do anything
break;
}
if (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft)
{
sbVml.Append("direction:rtl;");
}
sbVml.Append("'");
}
sbVml.Append("></div>");
sbVml.Append("</v:textbox>");
sbVml.Append("<x:ClientData ObjectType=\"Note\">");
sbVml.Append("<x:MoveWithCells/>");
sbVml.Append("<x:SizeWithCells/>");
// anchors are bloody hindering awkward inconvenient to calculate...
//sbVml.Append("<x:Anchor>");
//sbVml.Append("2, 15, 2, 14, 4, 23, 6, 19");
//sbVml.Append("</x:Anchor>");
sbVml.Append("<x:AutoFill>False</x:AutoFill>");
switch (comm.HorizontalTextAlignment)
{
case SLHorizontalTextAlignmentValues.Left:
// don't have to do anything
break;
case SLHorizontalTextAlignmentValues.Justify:
sbVml.Append("<x:TextHAlign>Justify</x:TextHAlign>");
break;
case SLHorizontalTextAlignmentValues.Center:
sbVml.Append("<x:TextHAlign>Center</x:TextHAlign>");
break;
case SLHorizontalTextAlignmentValues.Right:
sbVml.Append("<x:TextHAlign>Right</x:TextHAlign>");
break;
case SLHorizontalTextAlignmentValues.Distributed:
sbVml.Append("<x:TextHAlign>Distributed</x:TextHAlign>");
break;
}
switch (comm.VerticalTextAlignment)
{
case SLVerticalTextAlignmentValues.Top:
// don't have to do anything
break;
case SLVerticalTextAlignmentValues.Justify:
sbVml.Append("<x:TextVAlign>Justify</x:TextVAlign>");
break;
case SLVerticalTextAlignmentValues.Center:
sbVml.Append("<x:TextVAlign>Center</x:TextVAlign>");
break;
case SLVerticalTextAlignmentValues.Bottom:
sbVml.Append("<x:TextVAlign>Bottom</x:TextVAlign>");
break;
case SLVerticalTextAlignmentValues.Distributed:
sbVml.Append("<x:TextVAlign>Distributed</x:TextVAlign>");
break;
}
sbVml.AppendFormat("<x:Row>{0}</x:Row>", pt.RowIndex - 1);
sbVml.AppendFormat("<x:Column>{0}</x:Column>", pt.ColumnIndex - 1);
if (comm.Visible)
{
sbVml.Append("<x:Visible/>");
}
sbVml.Append("</x:ClientData>");
sbVml.Append("</v:shape>");
}
oxwComment.WriteEndElement();
// this is for Comments
oxwComment.WriteEndElement();
}
sbVml.Append("</xml>");
using (MemoryStream mem = new MemoryStream(Encoding.ASCII.GetBytes(sbVml.ToString())))
{
vdp.FeedData(mem);
}
}
private void writeHyperlinks(OpenXmlWriter writer, WorksheetPart woorksheetPart, SpreadsheetHyperlinkManager hyperlinkManager)
{
var hyperlinks = hyperlinkManager.GetHyperlinks();
if (!hyperlinks.Any())
{
return;
}
var hyperlinkTargetRelationshipIds = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
var clickableHyperLinks = new HashSet <KeyValuePair <SpreadsheetLocation, SpreadsheetHyperLink> >();
// we must add external URLs as relationships and reference them in the hyperlinks
// the more unique URLs there are the more performance problems there are
// because of this, a limit of INT_MaxUniqueHyperlinks was put in place
// without it the export can take 10x longer and it only gets worse the more unique links there are
// why is that?
// when adding a new relationship there is a uniqueness check. The check is performed sequentialy so the actual impact i O(n^2)
// also this part does not seem streamable so all of the openxml element would need to be stored in the memory
foreach (var hyperlink in hyperlinks.Where(x => !string.IsNullOrEmpty(x.Value.Target)))
{
var target = hyperlink.Value.Target;
if (hyperlinkTargetRelationshipIds.Count > MaxUniqueHyperlinks)
{
break;
}
clickableHyperLinks.Add(hyperlink);
if (!hyperlinkTargetRelationshipIds.ContainsKey(target))
{
var uri = Utilities.SafelyCreateUri(target);
if (uri == null)
{
hyperlinkTargetRelationshipIds[target] = "";
continue;
}
var relId = woorksheetPart.AddHyperlinkRelationship(uri, true).Id;
hyperlinkTargetRelationshipIds[target] = relId;
}
}
if (clickableHyperLinks.Count == 0)
{
return;
}
writer.WriteStartElement(new Hyperlinks());
foreach (var link in clickableHyperLinks)
{
var attributes = new List <OpenXmlAttribute>();
attributes.Add(new OpenXmlAttribute("ref", null, string.Format("{0}{1}", SpreadsheetHelper.ExcelColumnFromNumber(link.Key.ColumnIndex), link.Key.RowIndex)));
string id;
if (hyperlinkTargetRelationshipIds.TryGetValue(link.Value.Target, out id) && !string.IsNullOrEmpty(id))
{
var idAtt = new OpenXmlAttribute("r", "id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships", hyperlinkTargetRelationshipIds[link.Value.Target]);
attributes.Add(idAtt);
}
else
{
attributes.Add(new OpenXmlAttribute("location", null, link.Value.Target));
attributes.Add(new OpenXmlAttribute("display", null, link.Value.DisplayValue));
}
writer.WriteStartElement(new Hyperlink(), attributes);
writer.WriteEndElement();
}
writer.WriteEndElement();
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
// To demonstrate how to set column-widths in Excel, here's how to set the width of all columns to our default of "25":
UInt32 inx = 1;
writer.WriteStartElement(new Columns());
foreach (DataColumn dc in dt.Columns)
{
writer.WriteElement(new Column {
Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH
});
inx++;
}
writer.WriteEndElement();
writer.WriteStartElement(new SheetData());
string cellValue = "";
string cellReference = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsIntegerColumn = new bool[numberOfColumns];
bool[] IsFloatColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
// Create the Header row in our Excel Worksheet
// We'll set the row-height to 20px, and (using the "AppendHeaderTextCell" function) apply some formatting to the cells.
//
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex, Height = 20, CustomHeight = true
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer);
IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
// Uncomment the following lines, for an example of how to create some Named Ranges in your Excel file
#if FALSE
// For each column of data in this worksheet, let's create a Named Range, showing where there are values in this column
// eg "NamedRange_UserID" = "Drivers!$A2:$A6"
// "NamedRange_Surname" = "Drivers!$B2:$B6"
string columnHeader = col.ColumnName.Replace(" ", "_");
string NamedRange = string.Format("{0}!${1}2:${2}{3}", worksheetName, excelColumnNames[colInx], excelColumnNames[colInx], dt.Rows.Count + 1);
DefinedName definedName = new DefinedName()
{
Name = "NamedRange_" + columnHeader,
Text = NamedRange
};
definedNamesCol.Append(definedName);
#endif
}
writer.WriteEndElement(); // End of header "Row"
//
// Now, step through each row of data in our DataTable...
//
double cellFloatValue = 0;
CultureInfo ci = new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
// Create cell with data
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
// For numeric cells without any decimal places.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellFloatValue = 0;
bool bIncludeDecimalPlaces = IsFloatColumn[colInx];
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces, writer);
}
}
else if (IsDateColumn[colInx])
{
// For date values, we save the value to Excel as a number, but need to set the cell's style to format
// it as either a date or a date-time.
DateTime dateValue;
if (DateTime.TryParse(cellValue, out dateValue))
{
AppendDateCell(cellReference, dateValue, writer);
}
else
{
// This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank.
AppendTextCell(cellReference, cellValue, writer);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(cellReference, cellValue, writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
/// <summary>
/// OpenWriter Style, for Multiple DataTable into Multiple Worksheets in a single Workbook. A real f*****g pain.
/// </summary>
/// <param name="filePath">Where your file will be.</param>
/// <param name="listDataTables">List of dataTables. Can contain just 1, doesn't matter.</param>
/// <param name="yesHeader">You want headers?</param>
/// <param name="yesZero">You want zero instead of null?</param>
public void LargeExportOneWorkbook(
string filePath,
IEnumerable <DataTable> listDataTables,
bool yesHeader = false,
bool yesZero = false)
{
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(
filePath,
SpreadsheetDocumentType.Workbook))
{
document.AddWorkbookPart();
OpenXmlWriter writerXb = OpenXmlWriter.Create(document.WorkbookPart);
writerXb.WriteStartElement(new Workbook());
writerXb.WriteStartElement(new Sheets());
var count = 0;
foreach (DataTable dt in listDataTables)
{
var dicColName = new Dictionary <int, string>();
for (var colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
int dividend = colIndex + 1;
string columnName = string.Empty;
while (dividend > 0)
{
int modifier = (dividend - 1) % 26;
columnName =
$"{Convert.ToChar(65 + modifier).ToString(CultureInfo.InvariantCulture)}{columnName}";
dividend = (dividend - modifier) / 26;
}
dicColName.Add(colIndex + 1, columnName);
}
// var dicType = new Dictionary<Type, CellValues>(4)
// {
// { typeof(DateTime), CellValues.Date },
// { typeof(string), CellValues.InlineString },
// { typeof(double), CellValues.Number },
// { typeof(int), CellValues.Number },
// { typeof(bool), CellValues.Boolean }
// };
var dicType = new Dictionary <Type, string>(4)
{
{ typeof(DateTime), "d" },
{ typeof(string), "s" },
{ typeof(double), "n" },
{ typeof(int), "n" },
{ typeof(bool), "b" }
};
// this list of attributes will be used when writing a start element
List <OpenXmlAttribute> attributes;
var workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
if (yesHeader)
{
// create a new list of attributes
attributes = new List <OpenXmlAttribute>
{
// add the row index attribute to the list
new OpenXmlAttribute("r", null, 1.ToString())
};
// write the row start element with the row index attribute
writer.WriteStartElement(new Row(), attributes);
for (var columnNum = 1; columnNum <= dt.Columns.Count; ++columnNum)
{
// reset the list of attributes
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", string.Empty, $"{dicColName[columnNum]}1")
};
// add data type attribute - in this case inline string (you might want to look at the shared strings table)
// add the cell reference attribute
// write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
// write the cell value
writer.WriteElement(new CellValue(dt.Columns[columnNum - 1].ColumnName));
// writer.WriteElement(new CellValue(string.Format("This is Row {0}, Cell {1}", rowNum, columnNum)));
// write the end cell element
writer.WriteEndElement();
}
// write the end row element
writer.WriteEndElement();
}
for (var rowNum = 1; rowNum <= dt.Rows.Count; rowNum++)
{
// create a new list of attributes
attributes = new List <OpenXmlAttribute> {
new OpenXmlAttribute("r", null, (yesHeader ? rowNum + 1 : rowNum).ToString())
};
// add the row index attribute to the list
// write the row start element with the row index attribute
writer.WriteStartElement(new Row(), attributes);
DataRow dr = dt.Rows[rowNum - 1];
for (var columnNum = 1; columnNum <= dt.Columns.Count; columnNum++)
{
Type type = dt.Columns[columnNum - 1].DataType;
// reset the list of attributes
attributes = new List <OpenXmlAttribute>
{
// Add data type attribute - in this case inline string (you might want to look at the shared strings table)
new OpenXmlAttribute("t", null, type == typeof(string) ? "str" : dicType[type]),
// Add the cell reference attribute
new OpenXmlAttribute("r", string.Empty, $"{dicColName[columnNum]}{(yesHeader ? rowNum + 1 : rowNum).ToString(CultureInfo.InvariantCulture)}")
};
// write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), attributes);
// write the cell value
if (yesZero | (dr[columnNum - 1].ToString() != "0"))
{
writer.WriteElement(new CellValue(dr[columnNum - 1].ToString()));
}
// write the end cell element
writer.WriteEndElement();
}
// write the end row element
writer.WriteEndElement();
}
// write the end SheetData element
writer.WriteEndElement();
// write the end Worksheet element
writer.WriteEndElement();
writer.Close();
writerXb.WriteElement(
new Sheet
{
Name = dt.TableName,
SheetId = Convert.ToUInt32(count + 1),
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
count++;
}
// End Sheets
writerXb.WriteEndElement();
// End Workbook
writerXb.WriteEndElement();
writerXb.Close();
document.WorkbookPart.Workbook.Save();
document.Close();
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
}
