public void Export_as_Excel()
{
string SHEET_NAME = "OUTPUT";
if (previewCheckBox.Checked)
{
SHEET_NAME = "PREVIEW";
}
// Now, output the data table values to the MS Excel Workbook
try
{
string workbookPath = this.filename;
// Load the Excel file
ExcelFile excelFile = new ExcelFile();
if (this.filename.ToUpper().IndexOf(".XLSX") > 0)
{
excelFile.LoadXlsx(filename, XlsxOptions.None);
}
else
{
excelFile.LoadXls(filename);
}
// If there is more than one worksheet and there is one with the matching SHEET_NAME
// then delete that sheet. This allows for a new OUTPUT or PREVIEW worksheet to
// be added, and delete any existing one
if (excelFile.Worksheets.Count > 1)
{
ExcelWorksheet deleteSheet = null;
foreach (ExcelWorksheet thisSheet in excelFile.Worksheets)
{
if (thisSheet.Name.ToUpper() == SHEET_NAME)
{
//suppress Excel prompts and delete the 'Output' Worksheet
deleteSheet = thisSheet;
break;
}
}
if (deleteSheet != null)
{
deleteSheet.Delete();
}
}
// Add a new worksheet
ExcelWorksheet excelSheet = excelFile.Worksheets.Add(SHEET_NAME);
excelFile.Worksheets.ActiveWorksheet = excelSheet;
// Create the header cell style
CellStyle headerStyle = new CellStyle();
// headerStyle.HorizontalAlignment = HorizontalAlignmentStyle.Right;
headerStyle.FillPattern.SetSolid(Color.Khaki);
headerStyle.Font.Weight = ExcelFont.BoldWeight;
headerStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Create the new BibID/VID header cell style
CellStyle headerStyle2 = new CellStyle();
// headerStyle2.HorizontalAlignment = HorizontalAlignmentStyle.Right;
headerStyle2.FillPattern.SetSolid(Color.Gainsboro);
headerStyle2.Font.Weight = ExcelFont.BoldWeight;
headerStyle2.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Create the new Messages header cell style
CellStyle headerStyle3 = new CellStyle();
// headerStyle3.HorizontalAlignment = HorizontalAlignmentStyle.Right;
headerStyle3.FillPattern.SetSolid(Color.Tomato);
headerStyle3.Font.Weight = ExcelFont.BoldWeight;
headerStyle3.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Create the title cell style
CellStyle titleStyle = new CellStyle();
titleStyle.HorizontalAlignment = HorizontalAlignmentStyle.Left;
titleStyle.FillPattern.SetSolid(Color.LightSkyBlue);
titleStyle.Font.Weight = ExcelFont.BoldWeight;
titleStyle.Font.Size = 14 * 20;
// Set the default style
CellStyle defaultStyle = new CellStyle();
defaultStyle.Borders.SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Thin);
// Add the title
excelSheet.Cells[0, 0].Value = SHEET_NAME;
excelSheet.Cells[0, 0].Style = titleStyle;
// Add the header values
for (int i = 0; i < this.excelDataTbl.Columns.Count; i++)
{
excelSheet.Cells[1, i].Value = excelDataTbl.Columns[i].ColumnName.ToUpper();
int difference = this.excelDataTbl.Columns.Count - i;
if (difference <= 3)
{
if (difference == 1)
{
excelSheet.Cells[1, i].Style = headerStyle3;
}
else
{
excelSheet.Cells[1, i].Style = headerStyle2;
}
}
else
{
excelSheet.Cells[1, i].Style = headerStyle;
}
}
// Add each piece of data
int rowNumber = 2;
foreach (System.Data.DataRow thisRow in this.excelDataTbl.Rows)
{
// Add each cell
for (int i = 0; i < this.excelDataTbl.Columns.Count; i++)
{
if (!thisRow[excelDataTbl.Columns[i]].Equals(DBNull.Value))
{
excelSheet.Cells[rowNumber, i].Value = thisRow[excelDataTbl.Columns[i]].ToString();
}
else
{
excelSheet.Cells[rowNumber, i].Value = "";
}
excelSheet.Cells[rowNumber, i].Style = defaultStyle;
}
// Go to next row
rowNumber++;
}
// Get the final end range for the columns
String endRange = String.Empty;
String bibid_col = String.Empty;
String vidid_col = String.Empty;
if (this.excelDataTbl.Columns.Count < 26)
{
int range = (64 + this.excelDataTbl.Columns.Count);
endRange = Convert.ToString((char)range);
bibid_col = Convert.ToString((char)(range - 2));
vidid_col = Convert.ToString((char)(range - 1));
}
else if (this.excelDataTbl.Columns.Count == 26)
{
int range = (90); // ASCII 'Z' character
endRange = Convert.ToString((char)range);
bibid_col = Convert.ToString((char)(range - 2));
vidid_col = Convert.ToString((char)(range - 1));
}
else if (this.excelDataTbl.Columns.Count > 26)
{
double column_count = (double)excelDataTbl.Columns.Count;
int first_char_ascii = (int)(64 + (Math.Floor(column_count / 26)));
int second_char_ascii = (int)(64 + (column_count % 26));
// set the end range
endRange = Convert.ToString((char)first_char_ascii) + Convert.ToString((char)second_char_ascii);
// format the the column header values
if (second_char_ascii > Convert.ToInt32('B'))
{
bibid_col = Convert.ToString((char)first_char_ascii) + Convert.ToString((char)(second_char_ascii - 2));
vidid_col = Convert.ToString((char)first_char_ascii) + Convert.ToString((char)(second_char_ascii - 1));
}
else if (second_char_ascii == Convert.ToInt32('B'))
{
bibid_col = Convert.ToString((char)(first_char_ascii - 1)) + "Z";
vidid_col = Convert.ToString((char)first_char_ascii) + Convert.ToString((char)(second_char_ascii - 1));
bibid_col = bibid_col.Replace("@", "");
}
else
{
bibid_col = Convert.ToString((char)(first_char_ascii - 1)) + Convert.ToString((char)(Convert.ToInt32('Z') - 1));
vidid_col = Convert.ToString((char)(first_char_ascii - 1)) + "Z";
bibid_col = bibid_col.Replace("@", "");
vidid_col = vidid_col.Replace("@", "");
}
}
// Set some header properties
excelSheet.Cells.GetSubrange("A1", endRange + "1").Merged = true;
excelSheet.Rows[0].Height = 512;
excelSheet.Rows[1].Height = 512;
// Set the width on the last columns
excelSheet.Columns[excelDataTbl.Columns.Count - 3].Width = 18 * 256;
excelSheet.Columns[excelDataTbl.Columns.Count - 2].Width = 14 * 256;
excelSheet.Columns[excelDataTbl.Columns.Count - 1].Width = 60 * 256;
// Set the border
excelSheet.Cells.GetSubrange("A2", endRange + rowNumber.ToString()).SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
excelSheet.Cells.GetSubrange("A2", endRange + "2").SetBorders(MultipleBorders.Outside, Color.Black, LineStyle.Medium);
// Save the file
if (filename.ToUpper().IndexOf(".XLSX") > 0)
{
excelFile.SaveXlsx(filename);
}
else
{
excelFile.SaveXls(filename);
}
}
catch (Exception e)
{
DLC.Tools.Forms.ErrorMessageBox.Show("Error while saving the Excel Worksheet.\n\n" + e.Message, "Excel Error", e);
}
}