private static RelatedColumnPairs FindRelatedColumns(DataTable dataTable)
{
var result = new RelatedColumnPairs();
var colNameInfos = new List <ColInfo>();
for (var i = 0; i < dataTable.Columns.Count; i++)
{
colNameInfos.Add(new ColInfo(dataTable.Columns[i].ColumnName, i));
}
for (var i = 0; i < colNameInfos.Count; i++)
{
var thisRelatedPart = colNameInfos[i].RelatedPart;
if (colNameInfos.Count(cni => cni.RelatedPart == thisRelatedPart) != 2)
{
continue;
}
result.Add(new RelatedColumnPair(new[] { i, colNameInfos.Single(cni => cni.ColIndex != i && cni.RelatedPart == thisRelatedPart).ColIndex }));
}
return(result);
}
public void DataSetToExcel(DataSet dataSet, string excelFileName, bool suppressColumnNames, int?startTable = 0,
int?numTables = null, bool allOnSameSheet = false)
{
var hasData = false;
var workbook = new WorkbookWithStyles();
string worksheetName = null;
var relativeTableNum = 0;
var relativeRowNum = 0;
var iNumTables = numTables ?? dataSet.Tables.Count;
var iStartTable = startTable ?? 0;
var lastTableNum = iStartTable + iNumTables;
//var isFirstTable = true;
ISheet ws = null;
//var gotCustomWorksheetName = false;
for (var tableNum = iStartTable; tableNum < lastTableNum; tableNum++)
{
var dataTable = dataSet.Tables[tableNum];
worksheetName = worksheetName ?? dataTable.TableName ?? string.Format("Sheet{0}", ++relativeTableNum);
if (!allOnSameSheet)
{
if (dataTable.Rows.Count == 1 && dataTable.Columns.Count == 1) //&& !gotCustomWorksheetName)
{
worksheetName = dataTable.Rows[0][0].ToString();
//gotCustomWorksheetName = true;
continue;
}
ws = workbook.CreateWorkSheet(worksheetName);
worksheetName = null;
relativeRowNum = 0;
}
if (ws == null)
{
ws = workbook.CreateWorkSheet(worksheetName);
}
if (allOnSameSheet)
{
relativeRowNum++;
}
IRow row;
if (!suppressColumnNames)
{
if (ws == null)
{
throw new Exception("Worksheet is null!!");
}
row = ws.CreateRow(relativeRowNum++);
for (var columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
{
var cell = row.CreateCell(columnIndex);
workbook.SetHeaderValueAndFormat(cell, dataTable.Columns[columnIndex].ColumnName);
}
}
RelatedColumnPairs relatedColumns = null;
if (ShowChanges)
{
relatedColumns = FindRelatedColumns(dataTable);
}
//var longCols = new List<int>();
for (var rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
{
hasData = true;
if (ws == null)
{
throw new Exception("Worksheet is null!!");
}
var dataRow = dataTable.Rows[rowIndex];
if (ShowChanges)
{
relatedColumns.DoCompare(dataRow);
}
row = ws.CreateRow(relativeRowNum++);
for (var columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
{
var colDataType = dataTable.Columns[columnIndex].DataType;
var obj = dataRow[columnIndex];
var value = obj == DBNull.Value ? string.Empty : obj.ToString();
var condFormatting = ConditionalStyle.None;
if (ShowChanges && relatedColumns.IsDifferent(columnIndex))
{
condFormatting = ConditionalStyle.Note;
}
var cell = row.CreateCell(columnIndex);
if (colDataType == typeof(decimal) || colDataType == typeof(double))
{
workbook.SetValueAndFormat(cell, SafeConvertToDouble(value), condFormatting);
}
else if (colDataType == typeof(DateTime))
{
workbook.SetValueAndFormat(cell, SafeConvertToDateTime(value), condFormatting);
}
else if (colDataType == typeof(int))
{
workbook.SetValueAndFormat(cell, SafeConvertToInt(value), condFormatting);
}
else
{
workbook.SetValueAndFormat(cell, value, condFormatting);
}
}
}
if (ShowChanges)
{
ws.CreateFreezePane(0, 1, 0, 1);
ws.SetAutoFilter(new CellRangeAddress(0, 0, 0, dataTable.Columns.Count - 1));
}
for (var columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
{
ws.AutoSizeColumn(columnIndex);
}
}
if (!hasData)
{
return;
}
var tempFile = Path.GetTempFileName();
using (var fileData = new FileStream(tempFile, FileMode.Create))
{
workbook.Write(fileData);
}
try
{
File.Delete(excelFileName);
File.Move(tempFile, excelFileName);
} catch (Exception ex)
{
throw new Exception(string.Format("Error moving {0}: {1}", tempFile, ex.Message));
}
}
