private void ExportDataTableToExcel(DataTable dtExcel, string fileName)
{
//Add a workbook
string _fileName = fileName;
CarlosAg.ExcelXmlWriter.Workbook book = new CarlosAg.ExcelXmlWriter.Workbook();
// Specify which Sheet should be opened and the size of window by default
book.ExcelWorkbook.ActiveSheetIndex = 1;
book.ExcelWorkbook.WindowTopX = 100;
book.ExcelWorkbook.WindowTopY = 200;
book.ExcelWorkbook.WindowHeight = 7000;
book.ExcelWorkbook.WindowWidth = 8000;
// Some optional properties of the Document
book.Properties.Author = Environment.UserName;
book.Properties.Title = "Excel Export";
book.Properties.Created = DateTime.Now;
//Add styles to the workbook
WorksheetStyle s31 = book.Styles.Add("s31");
s31.Font.FontName = "Tahoma";
s31.Font.Size = 8;
s31.Font.Color = "#000000";
s31.Alignment.Horizontal = StyleHorizontalAlignment.Automatic;
s31.Alignment.Vertical = StyleVerticalAlignment.Center;
s31.Alignment.WrapText = true;
s31.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
s31.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
s31.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
s31.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
s31.NumberFormat = "@";
// Add styles for header of the Workbook
WorksheetStyle style = book.Styles.Add("HeaderStyle");
style.Font.FontName = "Tahoma";
style.Font.Size = 12;
style.Font.Color = "#CCCCFF";
style.Font.Bold = true;
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Alignment.Vertical = StyleVerticalAlignment.Center;
style.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1);
style.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1);
style.Interior.Pattern = StyleInteriorPattern.ThinDiagCross;
style.Interior.Color = "#5252FF";
// Add a Worksheet
Worksheet sheet = book.Worksheets.Add("Datenexport " + sIsinCode);
// Add 8 columns with defined widths
sheet.Table.Columns.Add(new WorksheetColumn(70)); // ISINCOD
sheet.Table.Columns.Add(new WorksheetColumn(90)); // QSTAMP
sheet.Table.Columns.Add(new WorksheetColumn(50)); // BID
sheet.Table.Columns.Add(new WorksheetColumn(50)); // ASK
sheet.Table.Columns.Add(new WorksheetColumn(70)); // BIDSIZE
sheet.Table.Columns.Add(new WorksheetColumn(70)); // ASKSIZE
sheet.Table.Columns.Add(new WorksheetColumn(70)); // ERRCODE
sheet.Table.Columns.Add(new WorksheetColumn(400)); // DESCRIPTION
//Add row with some properties
WorksheetRow row = sheet.Table.Rows.Add();
row.Index = 0;
row.Height = 20;
row.AutoFitHeight = false;
//Add header text for the columns
foreach (DataColumn col in dtExcel.Columns)
{
WorksheetCell wcHeader = new WorksheetCell(col.ColumnName, "HeaderStyle");
row.Cells.Add(wcHeader);
}
foreach (DataRow dtRow in dtExcel.Rows)
{
//Add row to the excel sheet
row = sheet.Table.Rows.Add();
//row.Height = 30;
row.AutoFitHeight = true;
//Loop through each column
foreach (DataColumn col in dtExcel.Columns)
{
WorksheetCell wc = new WorksheetCell();
wc.Data.Type = DataType.String;
wc.StyleID = "s31";
wc.Data.Text = dtRow[col.ColumnName].ToString().Replace("\r\n", "\n");
row.Cells.Add(wc);
}
}
//Save the work book
book.Save(_fileName);
}
).
