public void WriteMapColumns(LayoutColumn map, int sc, int sr)
{
mc++;
if (sc == 0)
{
writer.WriteElement(new Column()
{
Min = (uint)mc, Max = (uint)mc, Width = 8, CustomWidth = true
});
}
sc++;
foreach (var column in map)
{
if (column.Visible)
{
map.GetVisibleIndex(column, out int c, out int r);
c += sc; r += sr;
if (column.Count > 0)
{
WriteMapColumns(column, c, r);
}
else if (c >= mc)
{
mc++;
writer.WriteElement(new Column()
{
Min = (uint)mc, Max = (uint)mc, Width = column.Width / 6, CustomWidth = true
});
}
}
}
}
public void WriteCellValue(OpenXmlWriter openXmlWriter, string cellValue, int styleIndex, ref int sharedStringMaxIndex, Dictionary <string, int> sharedStringDictionary)
{
try
{
List <OpenXmlAttribute> openXmlAttributes = new List <OpenXmlAttribute>();
openXmlAttributes.Add(new OpenXmlAttribute("s", null, styleIndex.ToString()));
int parseResult;
if (int.TryParse(cellValue, out parseResult))
{
openXmlWriter.WriteStartElement(new Cell(), openXmlAttributes);
openXmlWriter.WriteElement(new CellValue(cellValue));
openXmlWriter.WriteEndElement();
}
else
{
openXmlAttributes.Add(new OpenXmlAttribute("t", null, "s"));
openXmlWriter.WriteStartElement(new Cell(), openXmlAttributes);
if (!sharedStringDictionary.ContainsKey(cellValue))
{
sharedStringDictionary.Add(cellValue, sharedStringMaxIndex);
sharedStringMaxIndex += 1;
}
openXmlWriter.WriteElement(new CellValue(sharedStringDictionary[cellValue].ToString()));
openXmlWriter.WriteEndElement();
}
}
catch (Exception exception)
{
LogWriter.LogError("Unable to write cell value to Excel report.");
throw exception;
}
}
private static Cell WriteCell(OpenXmlWriter writer, object value, int rowIndex)
{
var cell = new Cell();
//cell.CellReference = "";
var attributeListCell = new OpenXmlAttribute[] { };
if (default(object) == value)
{
cell.DataType = CellValues.String;
}
else if (typeof(bool).IsInstanceOfType(value))
{
cell.DataType = CellValues.Boolean;
}
else if (typeof(DateTime).IsInstanceOfType(value))
{
cell.DataType = CellValues.Date;
}
else if (typeof(double).IsAssignableFrom(value.GetType()))
{
cell.DataType = CellValues.Number;
}
else
{
cell.DataType = CellValues.String;
}
writer.WriteStartElement(cell);
if (default(object) == value)
{
var cellValue = new CellValue("");
writer.WriteElement(cellValue);
}
else if (typeof(CellReference).IsAssignableFrom(value.GetType()))
{
var cellRef = (CellReference)value;
var formula = new DocumentFormat.OpenXml.Spreadsheet.CellFormula(cellRef.formula);
writer.WriteElement(formula);
var cellValue = new CellValue(cellRef.value);
writer.WriteElement(cellValue);
// <c r="H2" t="str">
// <f>Manufacturer!A15</f>
// <v>30</v>
//</c>
}
else
{
var cellValue = new CellValue(value.ToString());
writer.WriteElement(cellValue);
}
writer.WriteEndElement();
return(cell);
}
public void AddHeaderRow(params string[] labels)
{
var row = new Row(labels.Select(x => new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(x)
}));
_writer.WriteElement(row);
}
private void FinishBuilding()
{
_writer.WriteEndElement(); // end SheetData
if (_excelImages.Count > 0)
{
_writer.WriteElement(GetDrawing());
}
_writer.WriteEndElement(); // end Worksheet
_writer.Close();
_buildingIsFinished = true;
}
public void Initialize()
{
_writer.WriteStartElement(new SharedStringTable());
// Write initial empty string shared string
_sharedStringIdx.Add(string.Empty, 0);
_writer.WriteStartElement(new SharedStringItem());
{
_writer.WriteElement(new Text {
Text = string.Empty
});
}
_writer.WriteEndElement();
}
static void WriteRandomValuesSAX(string filename, int numRows, int numCols)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
Row r = new Row();
Cell c = new Cell();
CellValue v = new CellValue("Test");
c.AppendChild(v);
writer.WriteStartElement(new Worksheet());
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();
writer.WriteEndElement();
writer.Close();
}
}
public static void AddLine(OpenXmlWriter writer, int row, string[] values)
{
List <OpenXmlAttribute> attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col1 = 1;
foreach (var cols in values)
{
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();
}
/// <summary>
/// 通过SAX创建工作表
/// </summary>
/// <param name="worksheetPart"></param>
private void CreateSheetBySAX(WorksheetPart worksheetPart, DrawingsPart drawingsPart)
{
using (_writer = OpenXmlWriter.Create(worksheetPart))
{
_rowIndex = 1;
//S: Worksheet
_writer.WriteStartElement(new Worksheet());
if (drawingsPart != null)
{
_writer.WriteElement(new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
}
InitSheetViews();
InitColumns();
MergeCells();
FillData();
//E: Worksheet
_writer.WriteEndElement();
_writer.Close();
}
}
public void CreateSharedStringPart(WorkbookPart workbookPart, int sharedStringMaxIndex, Dictionary <string, int> sharedStringDictionary)
{
try
{
if (sharedStringMaxIndex > 0)
{
SharedStringTablePart sharedStringTablePart = workbookPart.AddNewPart <SharedStringTablePart>();
using (OpenXmlWriter openXmlWriter = OpenXmlWriter.Create(sharedStringTablePart))
{
openXmlWriter.WriteStartElement(new SharedStringTable());
foreach (var item in sharedStringDictionary)
{
openXmlWriter.WriteStartElement(new SharedStringItem());
openXmlWriter.WriteElement(new Text(item.Key));
openXmlWriter.WriteEndElement();
}
openXmlWriter.WriteEndElement();
}
}
}
catch (Exception exception)
{
LogWriter.LogError("Unable to create 'SharedStringPart' in Excel report.");
throw exception;
}
}
private void CreateHeaderRow(IEnumerable <ApplicationFieldDefinition> applicationFields, OpenXmlWriter writer, int rowIdx, string applicationName, string schemaId)
{
var xmlAttributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, rowIdx.ToString(CultureInfo.InvariantCulture))
};
writer.WriteStartElement(new Row(), xmlAttributes);
foreach (var applicationField in applicationFields.Where(ShouldShowField()))
{
var headerStyleId = "8";
if (applicationField.Attribute.StartsWith("#old") && ApplicationConfiguration.ClientName == "hapag" && "r0042ExportExcel".Equals(schemaId))
{
headerStyleId = "2";
}
//Exporting to Excel, even if field is hidden
xmlAttributes = new List <OpenXmlAttribute> {
// add new datatype for cell
new OpenXmlAttribute("t", null, "str"),
// add header style
new OpenXmlAttribute("s", null, headerStyleId)
};
writer.WriteStartElement(new Cell(), xmlAttributes);
writer.WriteElement(new CellValue(GetI18NLabel(applicationField, applicationName)));
// this is for Cell
writer.WriteEndElement();
}
// end Row
writer.WriteEndElement();
}
private static void AppendNumericCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
{
// Add a new Excel Cell to our Row
writer.WriteElement(new Cell {
CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.Number
});
}
private void writeMergedCells(OpenXmlWriter writer)
{
var mergedCellRanges = new Dictionary <SpreadsheetLocation, System.Drawing.Size>();
foreach (var row in _rows)
{
for (var i = 0; i < row.Value.RowCells.Count; i++)
{
var cell = row.Value.RowCells[i];
if (cell.MergedCellsRange != null)
{
mergedCellRanges[new SpreadsheetLocation(row.Key.RowIndex, row.Key.ColumnIndex + i)] = cell.MergedCellsRange.Value;
}
}
}
if (mergedCellRanges.Any())
{
writer.WriteStartElement(new MergeCells());
foreach (var cellRange in mergedCellRanges)
{
var cell1Name = SpreadsheetHelper.ExcelColumnFromNumber(cellRange.Key.ColumnIndex) + cellRange.Key.RowIndex;
var cell2Name = SpreadsheetHelper.ExcelColumnFromNumber(cellRange.Key.ColumnIndex + cellRange.Value.Width) + (cellRange.Key.RowIndex + cellRange.Value.Height - 1);
var range = cell1Name + ":" + cell2Name;
var mergeCell = new MergeCell()
{
Reference = new StringValue(range)
};
writer.WriteElement(mergeCell);
}
writer.WriteEndElement();
}
}
public void CreateSheets(string filename, string sheetname, int sheetid)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
if (sheetid == 1)
{
workbookPart.Workbook = new Workbook();
}
Workbook workbook = myDoc.WorkbookPart.Workbook;
//WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
OpenXmlWriter writer = OpenXmlWriter.Create(workbookPart);
writer.WriteStartElement(workbook);
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = sheetname,
SheetId = (uint)sheetid
});
// this is for Sheets
writer.WriteEndElement();
// this is for Workbook
writer.WriteEndElement();
writer.Close();
myDoc.Close();
}
}
private void StartReport(WorkbookPart workbookPart, Sheets sheets)
{
try
{
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetIndex, Name = "STIG Discrepancies"
};
sheetIndex++;
sheets.Append(sheet);
_openXmlWriter = OpenXmlWriter.Create(worksheetPart);
_openXmlWriter.WriteStartElement(new Worksheet());
WriteReportColumns();
_openXmlWriter.WriteStartElement(new SheetData());
_openXmlWriter.WriteElement(new Row()
{
Hidden = true
});
WriteReportHeaderRow();
}
catch (Exception exception)
{
LogWriter.LogError("Unable to initialize 'STIG Discrepancies' tab.");
throw exception;
}
}
/// <summary>
/// 共享字符串
/// </summary>
/// <param name="sharedStringTablePart"></param>
private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart)
{
using (OpenXmlWriter writer = OpenXmlWriter.Create(sharedStringTablePart))
{
// SharedStringTable 开始
writer.WriteStartElement(new SharedStringTable()
{
Count = totalCount, UniqueCount = (uint)stringIndexDic.Keys.Count
});
foreach (var str in stringIndexDic.Keys)
{
// SharedStringItem 开始
writer.WriteStartElement(new SharedStringItem());
// 写入 Text
writer.WriteElement(new Text(str));
// SharedStringItem 结束
writer.WriteEndElement();
}
// SharedStringTable 结束
writer.WriteEndElement();
writer.Close();
}
}
private void InsertData(DataTable data)
{
foreach (DataRow dataRow in data.Rows)
{
Row r = new Row();
_writer.WriteStartElement(r);
foreach (DataColumn col in data.Columns)
{
Cell c = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(dataRow[col].ToString())
};
CellFormat format = new CellFormat();
format.BorderId = InsertBorder(GenerateBorder());
c.StyleIndex = InsertCellFormat(_workbookPart, format);
_writer.WriteElement(c);
}
_writer.WriteEndElement();
}
//_workbookPart.Workbook.Save();
}
private static void Finish(OpenXmlWriter writer, SpreadsheetDocument xl, WorksheetPart worksheetpart)
{
// end worksheet
writer.WriteEndElement();
writer.Close();
// write root element
writer = OpenXmlWriter.Create(xl.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet {
Name = "Sheet1",
SheetId = 1,
Id = xl.WorkbookPart.GetIdOfPart(worksheetpart)
});
// end Sheets
writer.WriteEndElement();
// end Workbook
writer.WriteEndElement();
writer.Close();
xl.Close();
}
/// <summary>
/// Starts writing sheet element
/// </summary>
/// <param name="sheetName"> Name of the Sheet. Empty or null sheet name will result in default sheet name.</param>
/// <param name="sheetProperties"></param>
/// <param name="sheetViewProperties"></param>
public void WriteStartSheet(string sheetName = default, OpenExcelSheetProperties sheetProperties = default, OpenExcelSheetViewProperties sheetViewProperties = default, OpenExcelSheetFormatProperties sheetFormatProperties = default)
{
_colCharacterLengths.Clear();
_rowIdx = _rowIdxReset;
var wsPart = _xl.WorkbookPart.AddNewPart <WorksheetPart>();
uint newSheetId = _newSheetId;
_workBookWriter.WriteElement(new Sheet()
{
Name = (string.IsNullOrWhiteSpace(sheetName)) ? $"Sheet{newSheetId}" : sheetName,
SheetId = newSheetId,
Id = _xl.WorkbookPart.GetIdOfPart(wsPart)
});
_workSheetWriter = OpenXmlWriter.Create(wsPart);
_workSheetWriter.WriteStartElement(new Worksheet());
WriteSheetProperties(sheetProperties);
WriteSheetViewProperties(sheetViewProperties);
WriteSheetFormatProperties(sheetFormatProperties);
_workSheetWriter.WriteStartElement(new SheetData());
}
/// <summary>
/// Writes data to a cell
/// </summary>
/// <param name="writer">this OpenXmlWriter writer instance</param>
/// <param name="cellAddress">The cell address</param>
/// <param name="cellValue">The cell value</param>
/// <param name="dataType">The data type for the cell</param>
public static void WriteCell(this OpenXmlWriter writer, string cellAddress, string cellValue, CellValues dataType)
{
// default to a standard cell value
OpenXmlElement value = new CellValue(cellValue);
bool testBool;
long testLong;
// fix up some values
switch (dataType)
{
// we are handling all strings as inline for performance reasons
case CellValues.SharedString:
case CellValues.InlineString:
dataType = CellValues.InlineString;
value = new InlineString(new Text(cellValue));
break;
case CellValues.Date:
// write the value as a string to the sheet
dataType = CellValues.String;
break;
case CellValues.Number:
// this is a safety check as we sometimes get bad values such as N/A in columns
if (!long.TryParse(cellValue, out testLong))
{
// we default to writing it as a string to be safe
dataType = CellValues.InlineString;
value = new InlineString(new Text(cellValue));
}
break;
case CellValues.Boolean:
// this is a safety check as we sometimes get bad values such as N/A in columns
if (bool.TryParse(cellValue, out testBool))
{
value = new CellValue(testBool ? "1" : "0");
}
else
{
// we default to writing it as a string to be safe
dataType = CellValues.InlineString;
value = new InlineString(new Text(cellValue));
}
break;
}
// write cell xml to the writer
writer.WriteStartElement(new Cell()
{
DataType = dataType, CellReference = cellAddress
});
writer.WriteElement(value);
writer.WriteEndElement();
}
private void WriteSharedString(XlsxSharedStringCell cell)
{
var style = !string.IsNullOrEmpty(cell.FormatName) ? Style.CellFormats[cell.FormatName] : "0";
var attributes = new OpenXmlAttribute[] { new OpenXmlAttribute("s", null, style) }.ToList();
attributes.Add(new OpenXmlAttribute("t", null, "s"));//shared string type
Writer.WriteStartElement(new Cell(), attributes);
if (!ShareStringDictionary.ContainsKey(cell.Value))
{
ShareStringDictionary.Add(cell.Value, ShareStringDictionary.Keys.Count());
}
//writing the index as the cell value
Writer.WriteElement(new CellValue(ShareStringDictionary[cell.Value].ToString()));
Writer.WriteEndElement();//cell
}
public void WriteRandomValuesSAX(string filename, int numRows, int numCols, int sheetid, int countRows)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
Row r = new Row();
Cell c = new Cell();
CellValue v = new CellValue("Test");
c.AppendChild(v);
var s = new SheetData();
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(s);
//writer.WriteStartElement(new Sheet());
for (int row = countRows; row < numRows; row++)
{
writer.WriteStartElement(r);
for (int col = 0; col < numCols; col++)
{
writer.WriteElement(c);
}
writer.WriteEndElement();
}
//writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
//writer = OpenXmlWriter.Create(myDoc.WorkbookPart);
//writer.WriteStartElement(new Workbook());
//writer.WriteStartElement(new Sheets());
//writer.WriteElement(new Sheet()
//{
// Name = $"Sheet21{sheetid}",
// SheetId = (uint)sheetid,
// Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart)
//});
//// this is for Sheets
//writer.WriteEndElement();
//// this is for Workbook
//writer.WriteEndElement();
//writer.Close();
myDoc.Close();
}
}
public void Save()
{
if (!_changed)
{
return;
}
if (_ssPart != null)
{
string originalSSPartId = _wpart.GetIdOfPart(_ssPart);
_wpart.DeletePart(originalSSPartId);
}
SharedStringTablePart newSSPart = _wpart.AddNewPart <SharedStringTablePart>();
using (OpenXmlWriter writer = OpenXmlWriter.Create(newSSPart))
{
writer.WriteStartElement(new SharedStringTable());
if (_stringCache == null)
{
string[] outputList = new string[_indexLookup.Count];
foreach (var i in _indexLookup)
{
outputList[i.Value] = i.Key;
}
for (uint idx = 0; idx < outputList.Length; idx++)
{
writer.WriteStartElement(new SharedStringItem());
writer.WriteElement(new Text(outputList[idx]));
writer.WriteEndElement();
}
}
else
{
foreach (var i in _stringCache)
{
writer.WriteStartElement(new SharedStringItem());
writer.WriteElement(new Text(i.Value));
writer.WriteEndElement();
}
}
writer.WriteEndElement();
}
}
private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
{
writer.WriteElement(new Cell
{
CellValue = new CellValue(cellStringValue),
CellReference = cellReference,
DataType = CellValues.String
});
}
public void WriteRow(OpenXmlWriter writer, Row row)
{
writer.WriteStartElement(row);
foreach (Cell cell in row)
{
writer.WriteElement(cell);
}
writer.WriteEndElement();
}
public static void AppendTextCell(this OpenXmlWriter writer, string cellReference, string cellStringValue)
{
writer.WriteElement(new Cell
{
CellValue = new CellValue(cellStringValue),
CellReference = cellReference,
DataType = CellValues.String
});
}
private static void WriteRowCells(OpenXmlWriter oxw, string item)
{
List <OpenXmlAttribute> oxa = new List <OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("t", null, "str"));
oxw.WriteStartElement(new Cell(), oxa);
oxw.WriteElement(new CellValue(item));
oxw.WriteEndElement();
}
public static void AppendFormulaCell(this OpenXmlWriter writer, string cellReference, string cellStringValue)
{
writer.WriteElement(new Cell
{
CellFormula = new CellFormula(cellStringValue),
CellReference = cellReference,
DataType = CellValues.Number
});
}
private static void WriteAutoFilter(OpenXmlWriter writer, int columnCount)
{
var autoFilterRange = "A1:" + GetColumnName(columnCount - 1) + "1";
writer.WriteElement(new AutoFilter()
{
Reference = autoFilterRange
});
}
private static void AppendFormulaCell(string cellReference, string cellStringValue, OpenXmlWriter writer)
{
// Add a new "formula" Excel Cell to our Row
writer.WriteElement(new Cell
{
CellFormula = new CellFormula(cellStringValue),
CellReference = cellReference,
DataType = CellValues.Number
});
}
