private DataMap SetupForTableBuild(String TableName, Headers headers)
{
DataTable table = new DataTable();
table.TableName = TableName;
DataMap map = new DataMap()
{
Name = TableName,
Table = table,
Headers = headers
};
IDataHandling cb = new DataHandling();
// Create Columns
foreach (Header heading in headers.HeaderList)
{
table.Columns.Add(cb.BuildTableColumn(heading.DisplayName));
}
return(map);
}
private void WriteDataMapToExcelWorksheet(DataMap map, 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 = map.Headers.Count;
DataTable dt = map.Table;
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", map.Headers[colInx].DisplayName, 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();
}
