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();
}
public void WriteCell(OpenXmlWriter writer, object value)
{
writer.WriteStartElement(new Cell(), CellAttributes);
writer.WriteStartElement(new InlineString());
writer.WriteStartElement(new Text());
writer.WriteString(value.ToString());
writer.WriteEndElement();
writer.WriteEndElement();
writer.WriteEndElement();
}
public void WriteCell(OpenXmlWriter writer, object value)
{
var dateTime = (DateTime) value;
writer.WriteStartElement(new Cell(), new [] { new OpenXmlAttribute("s", null, "1"), });
writer.WriteStartElement(new CellValue());
writer.WriteString(dateTime.ToOADate().ToString(CultureInfo.InvariantCulture));
writer.WriteEndElement();
writer.WriteEndElement();
}
public void WriteCell(OpenXmlWriter writer, object value)
{
writer.WriteStartElement(new Cell(), new [] { new OpenXmlAttribute("t", null, "b"), });
var b = (bool) value;
writer.WriteStartElement(new CellValue());
writer.WriteString(BooleanValue.FromBoolean(b).ToString());
writer.WriteEndElement();
writer.WriteEndElement();
}
private static void AjustaLarguraColunas(DocumentFormat.OpenXml.OpenXmlWriter writer, int startColunaIndex,
int larguraColuna, int ColunsCount)
{
var xmlCols = new List <DocumentFormat.OpenXml.OpenXmlAttribute>();
xmlCols.Add(new DocumentFormat.OpenXml.OpenXmlAttribute("min", null, startColunaIndex.ToString()));
xmlCols.Add(new DocumentFormat.OpenXml.OpenXmlAttribute("max", null,
(startColunaIndex + ColunsCount).ToString()));
xmlCols.Add(new DocumentFormat.OpenXml.OpenXmlAttribute("width", null, larguraColuna.ToString()));
writer.WriteStartElement(new OpenXmlSpread.Column(), xmlCols);
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;
}
}
}
private static void GerarExcelOpXml(System.Data.DataTable dt, string pathExcelFile, string sheetName,
string ColunaDefault, int rowDefault, int larguraColuna)
{
var styleIndexDefault = 0;
var styleIndexDefaultLine = 2;
DocumentFormat.OpenXml.UInt32Value styleIndex;
using (OpenXmlPackaging.SpreadsheetDocument myDoc =
OpenXmlPackaging.SpreadsheetDocument.Open(pathExcelFile, true))
{
OpenXmlPackaging.WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, sheetName);
OpenXmlSpread.Cell cellDefault =
GetCell(worksheetPart.Worksheet, ColunaDefault, Convert.ToUInt32(rowDefault));
styleIndex = cellDefault.StyleIndex;
styleIndexDefault = Convert.ToInt32(styleIndex.Value.ToString());
OpenXmlSpread.Cell cellDefaultline =
GetCell(worksheetPart.Worksheet, ColunaDefault, Convert.ToUInt32(rowDefault + 1));
styleIndex = cellDefaultline.StyleIndex;
styleIndexDefaultLine = Convert.ToInt32(styleIndex.Value.ToString());
}
using (OpenXmlPackaging.SpreadsheetDocument myDoc =
OpenXmlPackaging.SpreadsheetDocument.Open(pathExcelFile, true))
{
OpenXmlPackaging.WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, sheetName);
OpenXmlSpread.Stylesheet stylesheet = myDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
OpenXmlSpread.CellFormat deafultFormat = new OpenXmlSpread.CellFormat()
{
Alignment = new OpenXmlSpread.Alignment()
{
Horizontal = OpenXmlSpread.HorizontalAlignmentValues.Left,
Vertical = OpenXmlSpread.VerticalAlignmentValues.Center
},
ApplyAlignment = true
};
stylesheet.CellFormats.AppendChild(deafultFormat);
DocumentFormat.OpenXml.OpenXmlWriter
writer = DocumentFormat.OpenXml.OpenXmlWriter.Create(worksheetPart);
var indexColumn = ColumnIndex(ColunaDefault);
var startColunaIndex = indexColumn + 1;
writer.WriteStartElement(new OpenXmlSpread.Worksheet());
writer.WriteStartElement(new OpenXmlSpread.Columns());
AjustaLarguraColunas(writer, startColunaIndex, larguraColuna, dt.Columns.Count);
writer.WriteStartElement(new OpenXmlSpread.SheetData());
writer.WriteStartElement(new OpenXmlSpread.Row {
RowIndex = (UInt32)rowDefault
});
CriarColunas(writer, dt, indexColumn, rowDefault, styleIndexDefault);
CriarLinha(writer, dt, indexColumn, rowDefault, styleIndexDefaultLine);
writer.Dispose();
}
}
protected internal override void CreateDocument()
{
Report r = base.Report();
_workbookPart = _spreadSheet.AddWorkbookPart();
var openXmlExportHelper = new OpenXmlWriterHelper();
_styleSheet = openXmlExportHelper.CreateDefaultStylesheet();
_workbookPart.Workbook = _workbook;
var sheets = _workbook.AppendChild<Sheets>(new Sheets());
// create worksheet 1
var worksheetPart =_workbookPart.AddNewPart<WorksheetPart>();
var sheet = new Sheet() { Id = _workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = r.Name };
sheets.Append(sheet);
_workSheetSettings.Add(new WorkSheetSetting(r.Name));
_writer = OpenXmlWriter.Create(worksheetPart);
_writer.WriteStartElement(new Worksheet());
_writer.WriteStartElement(new SheetData());
_currentWorkSheet = _workSheetSettings.Last();
}
public void WriteCell(OpenXmlWriter writer, object value)
{
writer.WriteStartElement(new Cell());
writer.WriteStartElement(new CellValue());
writer.WriteString(string.Format(CultureInfo.InvariantCulture, "{0}", value));
writer.WriteEndElement();
writer.WriteEndElement();
}
