private static void CriarLinha(DocumentFormat.OpenXml.OpenXmlWriter writer, System.Data.DataTable dt,
int indexColumn, int rowDefault, int styleSheetIndex)
{
foreach (DataRow dr in dt.Rows)
{
rowDefault++;
writer.WriteStartElement(new OpenXmlSpread.Row {
RowIndex = (UInt32)(rowDefault)
});
for (int col = 0; col < dt.Columns.Count; col++)
{
var reference = (GetExcelColumnName(indexColumn + col) + rowDefault.ToString());
writer.WriteElement(new OpenXmlSpread.Cell
{
CellReference = reference,
CellValue = new OpenXmlSpread.CellValue(dr.ItemArray[col].ToString()),
DataType = new EnumValue <OpenXmlSpread.CellValues>(OpenXmlSpread.CellValues.Date),
StyleIndex = (UInt32)styleSheetIndex
});
}
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
}
private static void CriarColunas(DocumentFormat.OpenXml.OpenXmlWriter writer, System.Data.DataTable dt,
int indexColumn, int rowDefault, int styleIndexDefault)
{
for (int col = 0; col < dt.Columns.Count; col++)
{
var reference = (GetExcelColumnName(indexColumn + col) + rowDefault.ToString());
DataColumn cols = dt.Columns[col];
writer.WriteElement(new OpenXmlSpread.Cell
{
CellReference = reference,
CellValue = new OpenXmlSpread.CellValue(cols.ColumnName.ToUpper()),
StyleIndex = (UInt32)styleIndexDefault,
DataType = OpenXmlSpread.CellValues.String
});
}
writer.WriteEndElement();
}
private static void AppendTextCell(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.String });
}
/// <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;
}
}
}