protected void iBtnExcelExport_Click(object sender, ImageClickEventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
try
{
for (int i = 1; i < 6; i++)
{
sheet.Range[1, i, 2, i].Merge();
sheet.Range[1, i, 2, i].Text = ugrdResultTotal.Columns[i - 1].Header.Caption;
sheet.Range[2, i, 2, i].AddComment().Text = ugrdResultTotal.Columns[i - 1].Key;
sheet.Range[1, i, 2, i].AutofitColumns();
sheet.Range[1, i, 2, i].CellStyle.ColorIndex = ExcelKnownColors.Blue_grey;
sheet.Range[1, i, 2, i].CellStyle.Font.Color = ExcelKnownColors.White;
sheet.Range[1, i, 2, i].CellStyle.Font.Bold = true;
sheet.Range[1, i, 2, i].ColumnWidth = 8;
sheet.Range[1, 1, 2, i].VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet.Range[1, 1, 2, i].HorizontalAlignment = ExcelHAlign.HAlignCenter;
}
for (int i = 16; i < 20; i++)
{
sheet.Range[1, i, 2, i].Merge();
sheet.Range[1, i, 2, i].Text = ugrdResultTotal.Columns[i + 3].Header.Caption;
sheet.Range[2, i, 2, i].AddComment().Text = ugrdResultTotal.Columns[i + 3].Key;
sheet.Range[1, i, 2, i].AutofitColumns();
sheet.Range[1, i, 2, i].CellStyle.ColorIndex = ExcelKnownColors.Blue_grey;
sheet.Range[1, i, 2, i].CellStyle.Font.Color = ExcelKnownColors.White;
sheet.Range[1, i, 2, i].CellStyle.Font.Bold = true;
sheet.Range[1, i, 2, i].ColumnWidth = 0;
sheet.Range[1, 1, 2, i].VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet.Range[1, 1, 2, i].HorizontalAlignment = ExcelHAlign.HAlignCenter;
}
for (int i = 6; i < 16; i++)
{
if (i == 6 || i == 8 || i == 10 || i == 12 || i == 14)
{
sheet.Range[1, i, 1, i + 1].Merge();
if (i == 6)
{
sheet.Range[1, i, 1, i + 1].Text = "조직";
}
else if (i == 8)
{
sheet.Range[1, i, 1, i + 1].Text = "개인";
}
else if (i == 10)
{
sheet.Range[1, i, 1, i + 1].Text = "목표1";
}
else if (i == 12)
{
sheet.Range[1, i, 1, i + 1].Text = "목표2";
}
else if (i == 14)
{
sheet.Range[1, i, 1, i + 1].Text = "목표3";
}
sheet.Range[1, i, 1, i + 1].AutofitColumns();
sheet.Range[1, i, 1, i + 1].CellStyle.ColorIndex = ExcelKnownColors.Blue_grey;
sheet.Range[1, i, 1, i + 1].CellStyle.Font.Color = ExcelKnownColors.White;
sheet.Range[1, i, 1, i + 1].CellStyle.Font.Bold = true;
sheet.Range[1, i, 1, i + 1].VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet.Range[1, i, 1, i + 1].HorizontalAlignment = ExcelHAlign.HAlignCenter;
}
sheet.Range[2, i, 2, i].Text = ugrdResultTotal.Columns[i - 1].Header.Caption;
sheet.Range[2, i, 2, i].AddComment().Text = ugrdResultTotal.Columns[i - 1].Key;
sheet.Range[2, i, 2, i].AutofitColumns();
sheet.Range[2, i, 2, i].CellStyle.ColorIndex = ExcelKnownColors.Blue_grey;
sheet.Range[2, i, 2, i].CellStyle.Font.Color = ExcelKnownColors.White;
sheet.Range[2, i, 2, i].CellStyle.Font.Bold = true;
sheet.Range[2, i, 2, i].VerticalAlignment = ExcelVAlign.VAlignCenter;
sheet.Range[2, i, 2, i].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range[2, i, 2, i].ColumnWidth = 9;
}
DataTable dataTable = new DataTable();
DataRow dataRow = null;
dataTable.Columns.Add("DEPT_NAME", typeof(string));
dataTable.Columns.Add("POS_CLS_NAME", typeof(string));
dataTable.Columns.Add("POS_GRP_NAME", typeof(string));
dataTable.Columns.Add("EMP_CODE", typeof(string));
dataTable.Columns.Add("EMP_NAME", typeof(string));
dataTable.Columns.Add("ORGANIZATION_POINT", typeof(double));
dataTable.Columns.Add("ORGANIZATION_WEIGHT", typeof(int));
dataTable.Columns.Add("APPRAISAL_POINT", typeof(double));
dataTable.Columns.Add("APPRAISAL_WEIGHT", typeof(int));
dataTable.Columns.Add("OTHERS1_POINT", typeof(double));
dataTable.Columns.Add("OTHERS1_WEIGHT", typeof(int));
dataTable.Columns.Add("OTHERS2_POINT", typeof(double));
dataTable.Columns.Add("OTHERS2_WEIGHT", typeof(int));
dataTable.Columns.Add("OTHERS3_POINT", typeof(double));
dataTable.Columns.Add("OTHERS3_WEIGHT", typeof(int));
dataTable.Columns.Add("EMP_REF_ID", typeof(int));
dataTable.Columns.Add("EST_DEPT_REF_ID", typeof(string));
dataTable.Columns.Add("POS_CLS_ID", typeof(string));
dataTable.Columns.Add("POS_GRP_ID", typeof(string));
UltraGridRow row;
for (int i = 0; i < ugrdResultTotal.Rows.Count; i++)
{
row = ugrdResultTotal.Rows[i];
dataRow = dataTable.NewRow();
dataRow["EMP_REF_ID"] = row.Cells.FromKey("EMP_REF_ID").Text;
dataRow["EST_DEPT_REF_ID"] = row.Cells.FromKey("EST_DEPT_REF_ID").Text;
dataRow["DEPT_NAME"] = row.Cells.FromKey("DEPT_NAME").Text;
dataRow["POS_CLS_ID"] = row.Cells.FromKey("POS_CLS_ID").Text;
dataRow["POS_CLS_NAME"] = row.Cells.FromKey("POS_CLS_NAME").Text;
dataRow["POS_GRP_ID"] = row.Cells.FromKey("POS_GRP_ID").Text;
dataRow["POS_GRP_NAME"] = row.Cells.FromKey("POS_GRP_NAME").Text;
dataRow["EMP_CODE"] = row.Cells.FromKey("EMP_CODE").Text;
dataRow["EMP_NAME"] = row.Cells.FromKey("EMP_NAME").Text;
dataRow["ORGANIZATION_POINT"] = row.Cells.FromKey("ORGANIZATION_POINT").Value;
dataRow["ORGANIZATION_WEIGHT"] = row.Cells.FromKey("ORGANIZATION_WEIGHT").Value;
dataRow["APPRAISAL_POINT"] = row.Cells.FromKey("APPRAISAL_POINT").Value;
dataRow["APPRAISAL_WEIGHT"] = row.Cells.FromKey("APPRAISAL_WEIGHT").Value;
dataRow["OTHERS1_POINT"] = row.Cells.FromKey("OTHERS1_POINT").Value;
dataRow["OTHERS1_WEIGHT"] = row.Cells.FromKey("OTHERS1_WEIGHT").Value;
dataRow["OTHERS2_POINT"] = row.Cells.FromKey("OTHERS2_POINT").Value;
dataRow["OTHERS2_WEIGHT"] = row.Cells.FromKey("OTHERS2_WEIGHT").Value;
dataRow["OTHERS3_POINT"] = row.Cells.FromKey("OTHERS3_POINT").Value;
dataRow["OTHERS3_WEIGHT"] = row.Cells.FromKey("OTHERS3_WEIGHT").Value;
dataTable.Rows.Add(dataRow);
}
sheet.ImportDataTable(dataTable, false, 3, 1);
sheet = SetGridtoExcelFormat(sheet);
string _filename = "BSC_Person_Evaluation_" + ddlEstTermInfo.SelectedItem.Value + "_" + ddlEstTermMonth.SelectedItem.Value + ".xls";
workbook.SaveAs(_filename, ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog);
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
}
catch (Exception ex)
{
ltrScript.Text = JSHelper.GetAlertScript("다운로드 중 오류가 발생하였습니다.", false);
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
}
}
public ActionResult ImportExportDataTable(string saveOption, string button, string importOption)
{
string basePath = _hostingEnvironment.WebRootPath;
ViewBag.exportButtonState = "disabled=\"disabled\"";
///SaveOption Null
if (saveOption == null || button == null)
{
ViewBag.DataSource = null;
return(View());
}
//Start Business Object Functions
if (button == "Input Template")
{
Stream ms = new FileStream(basePath + @"/XlsIO/NorthwindDataTemplate.xls", FileMode.Open, FileAccess.Read);
string ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string fileName = "NorthwindDataTemplate.xls";
return(File(ms, ContentType, fileName));
}
else if (button == "Import From Excel")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
Stream sampleFile = new FileStream(basePath + @"/XlsIO/NorthwindDataTemplate.xls", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(sampleFile);
IWorksheet sheet = workbook.Worksheets[0];
if (importOption == "Skip")
{
sheet.ExportDataTableEvent += Sheet_ExportDataTableEventSkip;
ViewBag.importOptionSkip = "value=" + importOption + " checked = \"checked\"";
}
else if (importOption == "Replace")
{
sheet.ExportDataTableEvent += Sheet_ExportDataTableEventReplace;
ViewBag.importOptionReplace = "value=" + importOption + " checked = \"checked\"";
}
else
{
sheet.ExportDataTableEvent += Sheet_ExportDataTableEventStop;
ViewBag.importOptionStop = "value=" + importOption + " checked = \"checked\"";
}
dataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
ViewBag.DataSource = dataTable;
ViewBag.exportButtonState = "";
return(View());
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (saveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook;
workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Import data table to worksheet
sheet.ImportDataTable(dataTable, true, 1, 1, true);
sheet.UsedRange.AutofitColumns();
try
{
//Saving the workbook to disk. This spreadsheet is the result of opening and modifying
//an existing spreadsheet and then saving the result to a new workbook.
string ContentType = null;
string fileName = null;
if (saveOption == "Xlsx")
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ExportDataTable.xlsx";
}
else
{
ContentType = "Application/vnd.ms-excel";
fileName = "ExportDataTable.xls";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
public ActionResult ImportExportDataTable(string saveOption, string button, string importOption)
{
string fileName = "NorthwindDataTemplate.xls";
ViewBag.exportButtonState = "disabled=\"disabled\"";
///SaveOption Null
if (saveOption == null || button == null)
{
ViewBag.DataSource = null;
return(View());
}
//Start Business Object Functions
if (button == "Input Template")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(fileName));
return(excelEngine.SaveAsActionResult(workbook, fileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else if (button == "Import From Excel")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(fileName));
IWorksheet sheet = workbook.Worksheets[0];
if (importOption == "Skip")
{
sheet.ExportDataTableEvent += Sheet_ExportDataTableEventSkip;
ViewBag.importOptionSkip = "value=" + importOption + " checked = \"checked\"";
}
else if (importOption == "Replace")
{
sheet.ExportDataTableEvent += Sheet_ExportDataTableEventReplace;
ViewBag.importOptionReplace = "value=" + importOption + " checked = \"checked\"";
}
else
{
sheet.ExportDataTableEvent += Sheet_ExportDataTableEventStop;
ViewBag.importOptionStop = "value=" + importOption + " checked = \"checked\"";
}
dataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
ViewBag.DataSource = dataTable;
ViewBag.exportButtonState = "";
return(View());
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (saveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook;
workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Import data table to worksheet
sheet.ImportDataTable(dataTable, true, 1, 1, true);
sheet.UsedRange.AutofitColumns();
try
{
//Saving the workbook to disk. This spreadsheet is the result of opening and modifying
//an existing spreadsheet and then saving the result to a new workbook.
if (saveOption == "Xlsx")
{
return(excelEngine.SaveAsActionResult(workbook, "ExportDataTable.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "ExportDataTable.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
public void GenerateXls(int id)
{
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
string inputPath;
if (this.excel2003RadioBtn.Checked)
{
//Indicates the Path of the Input File
inputPath = GetFullTemplatePath("Invoice.xls");
workbook = application.Workbooks.Open(inputPath);
}
else
{//Indicates the Path of the Input File
inputPath = GetFullTemplatePath("Invoice.xlsx");
workbook = application.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
}
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Insert data
worksheet.Range["A5"].Text = "One Portals Way";
worksheet.Range["A6"].Text = "Twin Points WA 98156";
worksheet.Range["A7"].Text = "Phone: 1-206-555-1417 ";
worksheet.Range["A8"].Text = "Fax: 1-206-555-5938";
worksheet.Range["D5"].Text = "INVOICE NO:";
worksheet.Range["D6"].Text = "DATE:";
worksheet.Range["D7"].Text = "CUSTOMER ID :";
worksheet.Range["E6"].Formula = "TODAY()";
worksheet.Range["E5"].Number = id;
//Set values for Ship To.
GetShipDetails(id);
worksheet.Range["E7"].Text = shipName;
worksheet.Range["E10"].Text = shipName;
worksheet.Range["E11"].Text = address;
worksheet.Range["E12"].Text = shipCity;
worksheet.Range["E13"].Text = shipCountry;
//Set values for Bill To.
worksheet.Range["B10"].Text = shipName;
worksheet.Range["B11"].Text = address;
worksheet.Range["B12"].Text = shipCity;
worksheet.Range["B13"].Text = shipCountry;
//Calculates sub total
worksheet.Range["E27"].Formula = "SUM(E20:E26)";
//Set the number format
worksheet.Range["E20:E29"].NumberFormat = "$#,##0.00";
worksheet.Range["E28"].Value = freight.ToString();
//Calculates final total
worksheet.Range["E29"].Formula = "E27+E28";
//Import the data tables.
worksheet.ImportDataTable(GetOrder(id), false, 17, 1);
worksheet.ImportDataTable(GetProductDetails(id), false, 20, 1);
//Calculates price from discount and unit price.
for (int i = 20; i <= 26; i++)
{
if (worksheet.Range["A" + i.ToString()].Value == "")
{
break;
}
else
{
worksheet.Range["E" + i.ToString()].Formula = "(B" + i.ToString() + "*C" + i.ToString() + ")- (D" + i.ToString() + "/100)";
}
}
#endregion
#region Save the Workbook
//Set the Workbook version as Excel 97to2003
if (this.excel2003RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel97to2003;
fileName = "InvoiceOutput.xls";
}
//Set the Workbook version as Excel 2007
else if (this.excel2007RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel2007;
fileName = "InvoiceOutput.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.excel2010RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel2010;
fileName = "InvoiceOutput.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.excel2013RadioBtn.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
fileName = "InvoiceOutput.xlsx";
}
//Save the workbook to disk.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
if (!(int.TryParse(numRowCount.Text, out rowCount) && int.TryParse(numColCount.Text, out colCount)))
{
MessageBox.Show("Enter Numerical Value");
return;
}
if (rowCount <= 0)
{
MessageBox.Show("Invalid row count");
return;
}
if (colCount <= 0)
{
MessageBox.Show("Invalid column count");
return;
}
if (rdbExcel97.Checked)
{
if (colCount > 256)
{
MessageBox.Show("Column count must be less than or equal to 256 for Excel 2003 format.");
return;
}
if (rowCount > 65536)
{
MessageBox.Show("Row count must be less than or equal to 65,536 for Excel 2003 format.");
return;
}
}
if (rdbExcel2007.Checked || rdbExcel2010.Checked || rdbExcel2013.Checked)
{
if (rowCount > 100001)
{
MessageBox.Show("Row count must be less than or equal to 100,000.");
return;
}
if (colCount > 151)
{
MessageBox.Show("Column count must be less than or equal to 151.");
return;
}
}
#region Starttime
//Start Time
#endregion
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Default version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "PerformanceChecking.xls";
}
//Set the Default version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "PerformanceChecking.xlsx";
}
//Set the Default version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "PerformanceChecking.xlsx";
}
//Set the Default version as Excel 2013
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "PerformanceChecking.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(3);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
startTime = DateTime.Now;
workbook.DetectDateTimeInValue = false;
#endregion
if (chbColumnStyle.Checked)
{
//Body Style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));
bodyStyle.Color = Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
bodyStyle.EndUpdate();
worksheet.SetDefaultColumnStyle(1, colCount, bodyStyle);
}
if (this.chkImportOnSave.Checked)
{
DataTable dataTable = new DataTable();
for (int column = 1; column <= colCount; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rowCount; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= colCount; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
startTime = DateTime.Now;
worksheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
#region Apply Style
//Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));
headerStyle.Color = Color.FromArgb(255, 174, 33);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
#endregion
IMigrantRange migrantRange = worksheet.MigrantRange;
for (int column = 1; column <= colCount; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.Text = "Column: " + column.ToString();
migrantRange.CellStyle = headerStyle;
}
#region Insert Data
//Writing Data using normal interface
for (int row = 2; row <= rowCount; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= colCount; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.Number = row * column;
}
}
}
#endregion
#region Workook Save
workbook.SaveAs(fileName);
#endregion
#region Workbook Save and Dispose
//Close the workbook
workbook.Close();
//Dispose the Excel Engine
excelEngine.Dispose();
#endregion
#region Set EndTime and get LogDetails
//End Time
endTime = DateTime.Now - startTime;
LogDetails(endTime);
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
}
#endregion
}
private void generarreporte(int selected)
{
dtConsulta = getventas(selected);
if (dtConsulta.Rows.Count > 0)
{
int sucursales = radios();
if (sucursales == 1)
{
string strFechaInicial = Convert.ToDateTime(dtinicial.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
string strFechaFinal = Convert.ToDateTime(dtfinal.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MM yyyy");
string closure = date.ToString("dd MM yyyy HH mm");
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
DataTable tabla = dtConsulta;
int osos = tabla.Rows.Count;
worksheet.Name = m_nombreexterno;
worksheet.ImportDataTable(tabla, true, 2, 1, true);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:AG2"];
worksheet.Range["A1"].Text = m_nombreempresa + " - VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
#region
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113));
//headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113);
headerStyle.Color = System.Drawing.Color.Gray;
headerStyle.Font.Bold = true;
headerStyle.Font.Color = ExcelKnownColors.White;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
//workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
//pStyle.Color = colorss(m_select);
pStyle.Color = System.Drawing.Color.DodgerBlue;
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
#endregion
#region //ancho y alineacion columnas
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 10);
worksheet.SetColumnWidth(2, 10);
worksheet.SetColumnWidth(3, 10);
worksheet.SetColumnWidth(4, 10);
worksheet.SetColumnWidth(5, 12);//Estado
worksheet.SetColumnWidth(6, 7);
worksheet.SetColumnWidth(7, 10);
worksheet.SetColumnWidth(8, 10);
worksheet.SetColumnWidth(9, 45);
worksheet.SetColumnWidth(10, 10);//Linea
worksheet.SetColumnWidth(11, 12);
worksheet.SetColumnWidth(12, 7);
worksheet.SetColumnWidth(13, 10);
worksheet.SetColumnWidth(14, 5);
worksheet.SetColumnWidth(15, 15);
worksheet.SetColumnWidth(16, 5);
worksheet.SetColumnWidth(17, 4);
worksheet.SetColumnWidth(18, 4);
worksheet.SetColumnWidth(19, 4);
worksheet.SetColumnWidth(20, 4);
worksheet.SetColumnWidth(21, 40);
worksheet.SetColumnWidth(22, 20);
worksheet.SetColumnWidth(23, 5);
worksheet.SetColumnWidth(24, 20);
worksheet.SetColumnWidth(26, 12);
IStyle pStyles = workbook.Styles.Add("pStyles");
pStyles.BeginUpdate();
worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[8].HorizontalAlignment = ExcelHAlign.HAlignLeft;
pStyles.EndUpdate();
#endregion
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "O2,O" + cossacks + "";
string russevel = "O" + rusia + "";
string ftotal = "O" + rusia + "";
string qty = "N" + rusia + "";
string fpl = "AA" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
string nrusia = "=SUBTOTAL(9,O2:O" + cossacks + ")";
worksheet.Range[russevel].Formula = nrusia;
worksheet.Range[russevel].CellStyle = pStyle;
worksheet.Range["AA2:" + fpl].NumberFormat = "#,##0.00";
worksheet.Range["O2:" + ftotal].NumberFormat = "#,##0.00";
string sumqty = "=SUBTOTAL(9,N2:N" + cossacks + ")";
worksheet.Range[qty].Formula = sumqty;
worksheet.Range[qty].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = @"C:\BIG\LRG\Excel\REP. VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal + "( " + closure + " )" + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
//workbook.Close();
//excelEngine.Dispose();
string argument = @"/select, " + fileName;
System.Diagnostics.Process.Start("explorer.exe", argument);
}
}
else //base VPN
{
string strFechaInicial = Convert.ToDateTime(dtinicial.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
string strFechaFinal = Convert.ToDateTime(dtfinal.SelectedDate).ToString("dd-MMM-yyyy").ToUpper();
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MM yyyy");
string closure = date.ToString("dd MM yyyy HH mm");
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
DataTable tabla = dtConsulta;
int osos = tabla.Rows.Count;
worksheet.Name = m_nombreexterno;
worksheet.ImportDataTable(tabla, true, 2, 1, true);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:AG2"];
worksheet.Range["A1"].Text = m_nombreempresa + " - VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
#region
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
headerStyle.Color = System.Drawing.Color.Gray;
headerStyle.Font.Bold = true;
headerStyle.Font.Color = ExcelKnownColors.White;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
pStyle.Font.Color = ExcelKnownColors.White;
//workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
if (m_select == 2)
{
pStyle.Color = System.Drawing.Color.Orange;
}
else if (m_select == 3 || m_select == 4)
{
pStyle.Color = System.Drawing.Color.Red;
}
else
{
pStyle.Color = System.Drawing.Color.Gray;
}
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
#endregion
#region //ancho y alineacion columnas
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 10);
worksheet.SetColumnWidth(2, 10);
worksheet.SetColumnWidth(3, 10);
worksheet.SetColumnWidth(4, 10);
worksheet.SetColumnWidth(5, 12);//Estado
worksheet.SetColumnWidth(6, 7);
worksheet.SetColumnWidth(7, 10);
worksheet.SetColumnWidth(8, 10);
worksheet.SetColumnWidth(9, 45);
worksheet.SetColumnWidth(10, 10);//Linea
worksheet.SetColumnWidth(11, 12);
worksheet.SetColumnWidth(12, 7);
worksheet.SetColumnWidth(13, 10);
worksheet.SetColumnWidth(14, 5);
worksheet.SetColumnWidth(15, 15);
worksheet.SetColumnWidth(16, 5);
worksheet.SetColumnWidth(17, 4);
worksheet.SetColumnWidth(18, 40);
worksheet.SetColumnWidth(19, 20);
worksheet.SetColumnWidth(20, 4);
worksheet.SetColumnWidth(21, 17);
worksheet.SetColumnWidth(22, 7);
worksheet.SetColumnWidth(23, 15);
worksheet.SetColumnWidth(24, 15);
worksheet.SetColumnWidth(25, 15);
worksheet.SetColumnWidth(26, 7);
IStyle pStyles = workbook.Styles.Add("pStyles");
pStyles.BeginUpdate();
worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[8].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[7].HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Columns[21].HorizontalAlignment = ExcelHAlign.HAlignRight;
pStyles.EndUpdate();
#endregion
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "O2,O" + cossacks + "";
string russevel = "O" + rusia + "";
string ftotal = "O" + rusia + "";
string ffecha = "B" + rusia + "";
string qty = "N" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
string nrusia = "=SUBTOTAL(9,O2:O" + cossacks + ")";
worksheet.Range[russevel].Formula = nrusia;
worksheet.Range[russevel].CellStyle = pStyle;
worksheet.Range["O2:" + ftotal].NumberFormat = "#,##0.00";
string sumqty = "=SUBTOTAL(9,N2:N" + cossacks + ")";
worksheet.Range[qty].Formula = sumqty;
worksheet.Range[qty].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = @"C:\BIG\LRG\Excel\REP. VENTAS GRAL " + m_nombreexterno + strFechaInicial + " Al " + strFechaFinal + "( " + closure + " )" + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
//workbook.Close();
//excelEngine.Dispose();
string argument = @"/select, " + fileName;
System.Diagnostics.Process.Start("explorer.exe", argument);
}
}
}
else
{
RadWindow radWindow = new RadWindow();
RadWindow.Alert(new DialogParameters()
{
Content = "No hay registros para mostrar.",
Header = "BIG",
DialogStartupLocation = WindowStartupLocation.CenterOwner
// IconContent = "";
});
}
}
//For Session
//public HttpSessionStateBase Session { get; }
public ActionResult DataTable(string saveOption, string button)
{
string basePath = _hostingEnvironment.WebRootPath;
ViewBag.exportButtonState = "disabled=\"disabled\"";
///SaveOption Null
if (saveOption == null || button == null)
{
return(View());
}
//Start Data Table Functions
if (button == "Input Template")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportSales.xlsx", FileMode.Open, FileAccess.Read);
// Opening the Existing Worksheet from a Workbook.
IWorkbook workbook = application.Workbooks.Open(inputStream);
try
{
string ContentType = null;
string fileName = null;
if (saveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "ExportSales.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ExportSales.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
}
else if (button == "Import From Excel")
{
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream(basePath + @"/XlsIO/ExportSales.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet sheet = workbook.Worksheets[0];
//Export Data Table
DataTable customersTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
//Add Customer Unique ID
DataColumn column = customersTable.Columns.Add("ID");
column.SetOrdinal(0);
for (int i = 0; i < customersTable.Rows.Count; i++)
{
customersTable.Rows[i][0] = i + 1;
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
//Set the grid value to the Session
_sales = customersTable;
ViewBag.DataSource = _sales;
ViewBag.exportButtonState = "";
return(View());
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (saveOption == "Xls")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
else
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook;
workbook = excelEngine.Excel.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Import DataTable to worksheet
sheet.ImportDataTable(_sales, false, 5, 1);
sheet.Range["E4"].Text = "";
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = Color.FromArgb(0, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.White;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = Color.FromArgb(0, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:E1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A2:E2"].Merge();
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A3:A4"].Merge();
sheet["B3:B4"].Merge();
sheet["E3:E4"].Merge();
sheet["C3:D3"].Merge();
sheet["C3"].Text = "Sales";
sheet["A3:E4"].CellStyle = tableHeader;
sheet["A3"].Text = "S.ID";
sheet["B3"].Text = "Sales Person";
sheet["C4"].Text = "January - June";
sheet["D4"].Text = "July - December";
sheet["E3"].Text = "Change(%)";
sheet.UsedRange.AutofitColumns();
sheet.Columns[0].ColumnWidth = 10;
sheet.Columns[1].ColumnWidth = 24;
sheet.Columns[2].ColumnWidth = 21;
sheet.Columns[3].ColumnWidth = 21;
sheet.Columns[4].ColumnWidth = 16;
#endregion
try
{
string ContentType = null;
string fileName = null;
if (saveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "ExportDataTable.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ExportDataTable.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
}
return(View());
}
private void btnExport_Click(object sender, System.EventArgs e)
{
//Exports the DataTable to a spreadsheet.
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Workbook version as Excel 97to2003
if (this.rdbExcel97.Checked)
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "ExportToExcel.xls";
}
//Set the Workbook version as Excel 2007
else if (this.rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "ExportToExcel.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
fileName = "ExportToExcel.xlsx";
}
//Set the Workbook version as Excel 2010
else if (this.rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
fileName = "ExportToExcel.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Export DataTable to Excel
//Export DataTable.
if (this.dataGridView1.DataSource != null)
{
worksheet.ImportDataTable((DataTable)this.dataGridView1.DataSource, true, 3, 1, -1, -1);
}
else
{
MessageBox.Show("There is no datatable to export, Please import a datatable first", "Error");
//Close the workbook.
workbook.Close();
return;
}
#endregion
#region Formatting the Report
//Formatting the Report
#region Applying Body Stlye
//Body Style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(9, Color.FromArgb(239, 242, 247));
bodyStyle.Color = Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.UsedRange.CellStyleName = "BodyStyle";
bodyStyle.EndUpdate();
#endregion
#region Applying Header Style
//Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(8, Color.FromArgb(182, 189, 218));
headerStyle.Color = Color.FromArgb(182, 189, 218);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.Range["A1:K3"].CellStyleName = "HeaderStyle";
headerStyle.EndUpdate();
#endregion
//Remove grid lines in the worksheet.
worksheet.IsGridLinesVisible = false;
//Autofit Rows and Columns
worksheet.UsedRange.AutofitRows();
worksheet.UsedRange.AutofitColumns();
//Adjust Row Height.
worksheet.Rows[1].RowHeight = 25;
//Freeze header row.
worksheet.Range["A4"].FreezePanes();
worksheet.Range["C2"].Text = "Customer Details";
worksheet.Range["C2:D2"].Merge();
worksheet.Range["C2"].CellStyle.Font.Size = 14;
worksheet.Range["C2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
#endregion
#region Workbook Save and Close
//Saving the workbook to disk.
workbook.SaveAs(fileName);
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
public static void CreateSurveyExcelSyncfusion3(ExcelVersion version, bool withFilter, DataTable table, string dir)
{
ArrayList messages = new ArrayList();
string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("Syncfusion_{0}", version)));
DateTime begin = DateTime.Now;
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = version;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Header
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));
headerStyle.Color = Color.FromArgb(239, 243, 247);
//headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
//headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Font.Bold = true;
headerStyle.Font.Size = 12;
headerStyle.Color = Color.FromArgb(192, 192, 192);
headerStyle.Locked = true;
headerStyle.Font.FontName = "Arial";
headerStyle.Font.Color = ExcelKnownColors.Black;
headerStyle.Borders.LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
headerStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
headerStyle.EndUpdate();
worksheet.SetDefaultRowStyle(1, headerStyle);
IRanges header = worksheet.CreateRangesCollection();
header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]);
header.HorizontalAlignment = ExcelHAlign.HAlignCenter;
header.RowHeight = 36.75;
header.VerticalAlignment = ExcelVAlign.VAlignCenter;
//Body
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
workbook.SetPaletteColor(10, Color.FromArgb(255, 255, 204));
bodyStyle.Color = Color.FromArgb(255, 255, 204);
bodyStyle.Font.Size = 10;
bodyStyle.Font.Color = ExcelKnownColors.Black;
bodyStyle.Font.FontName = "Arial";
bodyStyle.Font.Bold = false;
bodyStyle.Font.Color = ExcelKnownColors.Black;
bodyStyle.Borders.LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
bodyStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
//bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
//bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
bodyStyle.EndUpdate();
worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle);
IRanges data = worksheet.CreateRangesCollection();
data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]);
data.RowHeight = 31.50;
//IStyle bodyStyle = worksheet.CreateRangesCollection().CellStyle;
//DateTime beginFillPattern = DateTime.Now;
//data.CellStyle.FillPattern = ExcelPattern.Solid;
//DateTime endFillPattern = DateTime.Now;
//messages.Add(string.Format("\tFillPattern:\t{0}", (endFillPattern - beginFillPattern)));
//worksheet.SetDefaultRowStyle(2, table.Rows.Count + 1, bodyStyle);
IRanges rangesOne = worksheet.CreateRangesCollection();
rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]);
worksheet.AutoFilters.FilterRange = rangesOne;
worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true);
IRanges dateTimeColl = worksheet.CreateRangesCollection();
dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]);
dateTimeColl.NumberFormat = "MM/DD/YYYY h:mm am/pm";
for (int i = 1; i <= table.Columns.Count; i++)
{
worksheet.AutofitColumn(i);
}
worksheet.SetColumnWidth(5, 18.00);
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
workbook.Close();
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
byte[] xlsData = ms.ToArray();
SaveFile(xlsData, filePath);
DateTime end = DateTime.Now;
messages.Add(string.Format("Total time:\t{0}", (end - begin)));
LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion3(...) - {0},{1}", Path.GetFileName(filePath), (end - begin)));
StringBuilder sb = new StringBuilder();
sb.AppendLine("");
foreach (string message in messages)
{
sb.AppendLine(message);
}
LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion3 steps\n{0}", sb));
}
private void BtnGenerate_Clicked(object sender, EventArgs e)
{
int rows = Convert.ToInt32(this.rowCount.Text);
int columns = Convert.ToInt32(this.colCount.Text);
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
workbook = application.Workbooks.Create(1);
workbook.Version = ExcelVersion.Excel2013;
IWorksheet sheet = workbook.Worksheets[0];
if (this.Import.IsChecked.Value)
{
DataTable dataTable = new DataTable();
for (int column = 1; column <= columns; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rows; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= columns; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
sheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
IMigrantRange migrantRange = sheet.MigrantRange;
for (int column = 1; column <= Convert.ToInt32(this.colCount.Text); column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.SetValue("Column: " + column.ToString());
}
//Writing Data using normal interface
for (int row = 2; row <= rows; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= columns; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.SetValue(row * column);
}
}
}
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Sample.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Sample.xlsx", "application/msexcel", stream);
}
}
public static void CreateSurveyExcelSyncfusion0(DataTable table, string dir)
{
DateTime begin = DateTime.Now;
ArrayList messages = new ArrayList();
string filePath = Path.Combine(dir, FileName.Replace(NameKey, "Syncfusion"));
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2010;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//GlobalStyles
DateTime beginDateTimeFormat = DateTime.Now;
IRanges dateTimeColl = worksheet.CreateRangesCollection();
dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]);
//dateTimeColl.NumberFormat = "mm/dd/yyyy h:mm tt";
dateTimeColl.NumberFormat = "mm/dd/yyyy hh:m am/pm";
messages.Add(string.Format("DateTimeFormat:\t{0}", (DateTime.Now - beginDateTimeFormat)));
//Header
DateTime beginHeaderFormat = DateTime.Now;
IRanges header = worksheet.CreateRangesCollection();
header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]);
header.VerticalAlignment = ExcelVAlign.VAlignCenter;
header.HorizontalAlignment = ExcelHAlign.HAlignCenter;
header.CellStyle.Font.Bold = true;
header.CellStyle.Font.Size = 12;
header.CellStyle.Font.FontName = "Arial";
header.CellStyle.Font.RGBColor = Color.Black;
header.CellStyle.Color = Color.FromArgb(192, 192, 192);
header.CellStyle.Locked = true;
header.RowHeight = 36.75;
header.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;
header.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
header.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
messages.Add(string.Format("HeaderFormat:\t{0}\n", (DateTime.Now - beginHeaderFormat)));
//Body
DateTime beginBodyFormat = DateTime.Now;
IRanges data = worksheet.CreateRangesCollection();
data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]);
data.CellStyle.Font.Size = 10;
data.RowHeight = 31.50;
data.CellStyle.Color = Color.FromArgb(255, 255, 204);
data.VerticalAlignment = ExcelVAlign.VAlignCenter;
data.CellStyle.Font.FontName = "Arial";
data.CellStyle.Font.RGBColor = Color.Black;
data.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;
data.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
data.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
messages.Add(string.Format("BodyFormat:\t{0}\n", (DateTime.Now - beginBodyFormat)));
DateTime beginAutoFilters = DateTime.Now;
IRanges rangesOne = worksheet.CreateRangesCollection();
rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]);
worksheet.AutoFilters.FilterRange = rangesOne;
messages.Add(string.Format("AutoFilters:\t{0}", (DateTime.Now - beginAutoFilters)));
DateTime beginImport = DateTime.Now;
worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true);
messages.Add(string.Format("Import:\t{0}", (DateTime.Now - beginImport)));
DateTime beginAutofitColumn = DateTime.Now;
for (int i = 1; i <= table.Columns.Count; i++)
{
worksheet.AutofitColumn(i);
}
messages.Add(string.Format("AutofitColumn:\t{0}", (DateTime.Now - beginAutofitColumn)));
MemoryStream ms = new MemoryStream();
DateTime beginSaveAsMemoryStream = DateTime.Now;
workbook.SaveAs(ms);
DateTime endSaveAsMemoryStream = DateTime.Now;
messages.Add(string.Format("SaveAsMemoryStream:\t{0}", (endSaveAsMemoryStream - beginSaveAsMemoryStream)));
//workbook.Close();
//excelEngine.ThrowNotSavedOnDestroy = false;
//excelEngine.Dispose();
DateTime beginSaveFile = DateTime.Now;
byte[] xlsData = ms.ToArray();
SaveFile(xlsData, filePath);
DateTime endSaveFile = DateTime.Now;
messages.Add(string.Format("SaveFile:\t{0}", (endSaveFile - beginSaveFile)));
//LsiLogger.Trace("End of CreateSurveyExcelSyncfusion(...)");
DateTime end = DateTime.Now;
messages.Add(string.Format("Total time:\t{0}", (end - begin)));
LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion(...) - {0},{1}", Path.GetFileName(filePath), (end - begin)));
StringBuilder sb = new StringBuilder();
sb.AppendLine("");
foreach (string message in messages)
{
sb.AppendLine(message);
}
LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion steps\n{0}", sb));
}
public static void CreateSurveyExcelSyncfusion2(ExcelVersion version, bool withFilter, DataTable table, string dir)
{
ArrayList messages = new ArrayList();
string filePath = Path.Combine(dir, FileName.Replace(NameKey, string.Format("Syncfusion_{0}", version)));
DateTime begin = DateTime.Now;
//LsiLogger.Trace("Begin of CreateSurveyExcelSyncfusion(...)");
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = version;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
DateTime beginColumnNames = DateTime.Now;
worksheet.Range["A1"].Text = "Uid";
worksheet.Range["B1"].Text = "Suid";
worksheet.Range["C1"].Text = "Survey";
worksheet.Range["D1"].Text = "Location";
worksheet.Range["E1"].Text = "Date / Time Name";
worksheet.Range["F1"].Text = "Prompt 1";
worksheet.Range["G1"].Text = "Prompt 2";
worksheet.Range["H1"].Text = "Prompt 3";
worksheet.Range["I1"].Text = "Prompt 4";
worksheet.Range["J1"].Text = "Prompt 5";
worksheet.Range["K1"].Text = "Duration (sec)";
worksheet.Range["L1"].Text = "Expired";
DateTime endColumnNames = DateTime.Now;
messages.Add(string.Format("ColumnNames:\t{0}", (endColumnNames - beginColumnNames)));
DateTime beginDateTimeFormat = DateTime.Now;
IRanges dateTimeColl = worksheet.CreateRangesCollection();
dateTimeColl.Add(worksheet.Range[1, 5, table.Rows.Count + 1, 5]);
//dateTimeColl.NumberFormat = "mm/dd/yyyy h:mm tt";
dateTimeColl.NumberFormat = "mm/dd/yyyy hh:mm";
DateTime endDateTimeFormat = DateTime.Now;
messages.Add(string.Format("DateTimeFormat:\t{0}", (endDateTimeFormat - beginDateTimeFormat)));
//Header
DateTime beginHeaderFormat = DateTime.Now;
IRanges header = worksheet.CreateRangesCollection();
header.Add(worksheet.Range[1, 1, 1, table.Columns.Count]);
header.HorizontalAlignment = ExcelHAlign.HAlignCenter;
header.CellStyle.Font.Bold = true;
header.CellStyle.Font.Size = 12;
header.RowHeight = 36.75;
header.CellStyle.Color = Color.FromArgb(192, 192, 192);
header.CellStyle.Locked = true;
header.VerticalAlignment = ExcelVAlign.VAlignCenter;
header.CellStyle.Font.FontName = "Arial";
header.CellStyle.Font.RGBColor = Color.Black;
header.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;
header.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
header.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
DateTime endHeaderFormat = DateTime.Now;
messages.Add(string.Format("HeaderFormat:\t{0}\n", (endHeaderFormat - beginHeaderFormat)));
//Body
DateTime beginBodyFormat = DateTime.Now;
for (int i = 0; i < table.Rows.Count + 1; i++)
{
IRanges data = worksheet.CreateRangesCollection();
data.Add(worksheet.Range[2 + i, 1, 2 + i, table.Columns.Count]);
//IRanges data = worksheet.CreateRangesCollection();
//data.Add(worksheet.Range[2, 1, table.Rows.Count + 1, table.Columns.Count]);
DateTime beginLineStyle = DateTime.Now;
data.CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;
DateTime endLineStyle = DateTime.Now;
messages.Add(string.Format("\tLineStyle:\t{0}", (endLineStyle - beginLineStyle)));
DateTime beginDiagonalDown = DateTime.Now;
data.CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
DateTime endDiagonalDown = DateTime.Now;
messages.Add(string.Format("\tDiagonalDown:\t{0}", (endDiagonalDown - beginDiagonalDown)));
DateTime beginDiagonalUp = DateTime.Now;
data.CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
DateTime endDiagonalUp = DateTime.Now;
messages.Add(string.Format("\tDiagonalUp:\t{0}", (endDiagonalUp - beginDiagonalUp)));
IFont font = data.CellStyle.Font;
DateTime beginFontName = DateTime.Now;
//data.CellStyle.Font.FontName = "Arial";
font.FontName = "Arial";
DateTime endFontName = DateTime.Now;
messages.Add(string.Format("\tFontName:\t{0}", (endFontName - beginFontName)));
DateTime beginFontSize = DateTime.Now;
//data.CellStyle.Font.Size = 10;
font.Size = 10;
DateTime endFontSize = DateTime.Now;
messages.Add(string.Format("\tFontSize:\t{0}", (endFontSize - beginFontSize)));
DateTime beginFontRGBColor = DateTime.Now;
//data.CellStyle.Font.RGBColor = Color.Black;
font.Color = ExcelKnownColors.Black;
DateTime endFontRGBColor = DateTime.Now;
messages.Add(string.Format("\tFontRGBColor:\t{0}", (endFontRGBColor - beginFontRGBColor)));
DateTime beginVerticalAlignment = DateTime.Now;
data.VerticalAlignment = ExcelVAlign.VAlignCenter;
DateTime endVerticalAlignment = DateTime.Now;
messages.Add(string.Format("\tVerticalAlignment:\t{0}", (endVerticalAlignment - beginVerticalAlignment)));
DateTime beginFillPattern = DateTime.Now;
data.CellStyle.FillPattern = ExcelPattern.Solid;
DateTime endFillPattern = DateTime.Now;
messages.Add(string.Format("\tFillPattern:\t{0}", (endFillPattern - beginFillPattern)));
DateTime beginRowHeight = DateTime.Now;
//data.RowHeight = 31.50;
worksheet.SetRowHeight(i + 2, 31.5);
DateTime endRowHeight = DateTime.Now;
messages.Add(string.Format("\tRowHeight:\t{0}", (endRowHeight - beginRowHeight)));
DateTime beginColor = DateTime.Now;
data.CellStyle.Color = Color.FromArgb(255, 255, 204);
DateTime endColor = DateTime.Now;
messages.Add(string.Format("\tColor:\t{0}", (endColor - beginColor)));
}
DateTime endBodyFormat = DateTime.Now;
messages.Add("\t---------------------------");
messages.Add(string.Format("BodyFormat:\t{0}\n", (endBodyFormat - beginBodyFormat)));
if (withFilter)
{
DateTime beginAutoFilters = DateTime.Now;
IRanges rangesOne = worksheet.CreateRangesCollection();
rangesOne.Add(worksheet.Range[1, 1, table.Rows.Count, table.Columns.Count]);
worksheet.AutoFilters.FilterRange = rangesOne;
DateTime endAutoFilters = DateTime.Now;
messages.Add(string.Format("AutoFilters:\t{0}", (endAutoFilters - beginAutoFilters)));
}
DateTime beginImport = DateTime.Now;
worksheet.ImportDataTable(table, true, 1, 1, -1, -1, true);
DateTime endImport = DateTime.Now;
messages.Add(string.Format("Import:\t{0}", (endImport - beginImport)));
DateTime beginAutofitColumn = DateTime.Now;
for (int i = 1; i <= table.Columns.Count; i++)
{
worksheet.AutofitColumn(i); //10000-7sec.
}
DateTime endAutofitColumn = DateTime.Now;
messages.Add(string.Format("AutofitColumn:\t{0}", (endAutofitColumn - beginAutofitColumn)));
MemoryStream ms = new MemoryStream();
DateTime beginSaveAsMemoryStream = DateTime.Now;
workbook.SaveAs(ms);
DateTime endSaveAsMemoryStream = DateTime.Now;
messages.Add(string.Format("SaveAsMemoryStream:\t{0}", (endSaveAsMemoryStream - beginSaveAsMemoryStream)));
//workbook.Close();
//excelEngine.ThrowNotSavedOnDestroy = false;
//excelEngine.Dispose();
DateTime beginSaveFile = DateTime.Now;
byte[] xlsData = ms.ToArray();
SaveFile(xlsData, filePath);
DateTime endSaveFile = DateTime.Now;
messages.Add(string.Format("SaveFile:\t{0}", (endSaveFile - beginSaveFile)));
//LsiLogger.Trace("End of CreateSurveyExcelSyncfusion(...)");
DateTime end = DateTime.Now;
messages.Add(string.Format("Total time:\t{0}", (end - begin)));
LsiLogger.Trace(string.Format("Duration of CreateSurveyExcelSyncfusion2(...) - {0},{1}", Path.GetFileName(filePath), (end - begin)));
StringBuilder sb = new StringBuilder();
sb.AppendLine("");
foreach (string message in messages)
{
sb.AppendLine(message);
}
LsiLogger.Trace(string.Format("CreateSurveyExcelSyncfusion2 steps\n{0}", sb));
}
/// <summary>
/// Convert the Excel document to JSON
/// </summary>
/// <returns>Return the JSON document as stream</returns>
public MemoryStream ImportExportXlsIO(string button, string option, DataTable dataTable)
{
if (button == "Input Document")
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//Opening the encrypted Workbook
FileStream inputStream = new FileStream(ResolveApplicationPath("northwind-data-template.xls"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelParseOptions.Default);
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
}
}
else
{
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Exports the DataTable to a spreadsheet.
string fileName = string.Empty;
#region Workbook Initialize
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the Workbook version as Excel 97to2003
if (option == "XLS")
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
fileName = "ExportToExcel.xls";
}
//Set the Workbook version as Excel 2007
else
{
application.DefaultVersion = ExcelVersion.Excel2007;
fileName = "ExportToExcel.xlsx";
}
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 3 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Export DataTable to Excel
//Export DataTable.
if (dataTable != null)
{
worksheet.ImportDataTable(dataTable, true, 3, 1, -1, -1);
}
#endregion
#region Formatting the Report
//Formatting the Report
#region Applying Body Stlye
//Body Style
IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
bodyStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(9, Syncfusion.Drawing.Color.FromArgb(239, 242, 247));
bodyStyle.Color = Syncfusion.Drawing.Color.FromArgb(239, 243, 247);
bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.UsedRange.CellStyleName = "BodyStyle";
bodyStyle.EndUpdate();
#endregion
#region Applying Header Style
//Header Style
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
//Add custom colors to the palette.
workbook.SetPaletteColor(8, Syncfusion.Drawing.Color.FromArgb(182, 189, 218));
headerStyle.Color = Syncfusion.Drawing.Color.FromArgb(182, 189, 218);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
//Apply Style
worksheet.Range["A1:K3"].CellStyleName = "HeaderStyle";
headerStyle.EndUpdate();
#endregion
//Remove grid lines in the worksheet.
worksheet.IsGridLinesVisible = false;
//Autofit Rows and Columns
worksheet.UsedRange.AutofitRows();
worksheet.UsedRange.AutofitColumns();
//Adjust Row Height.
worksheet.Rows[1].RowHeight = 25;
//Freeze header row.
worksheet.Range["A4"].FreezePanes();
worksheet.Range["C2"].Text = "Customer Details";
worksheet.Range["C2:D2"].Merge();
worksheet.Range["C2"].CellStyle.Font.Size = 14;
worksheet.Range["C2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
#endregion
#region Workbook Save and Close
//Saving the workbook to disk.
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
#endregion
}
}
}
private void generarreporte()
{
DataTable tabla = dtblcatalogo;
//Refresh(CashierBusyIndicator);
//generar();
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MMMM yyyy HH mm ").ToUpper();
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
int osos = tabla.Rows.Count;
worksheet.Name = "Servicios";
worksheet.ImportDataTable(tabla, true, 2, 1);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:K2"];
worksheet.Range["A1"].Text = "CATÁLOGO DE SERVICIOS AL " + dateday;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113));
headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
pStyle.Color = System.Drawing.Color.FromArgb(89, 171, 227);
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 10);
worksheet.SetColumnWidth(2, 30);
worksheet.SetColumnWidth(3, 15);
worksheet.SetColumnWidth(4, 20);
worksheet.SetColumnWidth(5, 7);
worksheet.SetColumnWidth(6, 7);
worksheet.SetColumnWidth(7, 10);
worksheet.SetColumnWidth(8, 15);
worksheet.SetColumnWidth(11, 15);
IStyle pStyles = workbook.Styles.Add("pStyles");
pStyles.BeginUpdate();
worksheet.Columns[3].HorizontalAlignment = ExcelHAlign.HAlignLeft;
pStyles.EndUpdate();
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "H2,H" + cossacks + "";
string russevel = "H" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = @"C:\BIG\LRG\Excel\CATÁLOGO DE SERVICIOS AL " + dateday + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
string argument = @"/select, " + fileName;
System.Diagnostics.Process.Start("explorer.exe", argument);
}
}
static void Main(string[] args)
{
intEMPRESAID = Convert.ToByte(GetSetting(appName, section, "EmpresaID", String.Empty));
intSUCURSALID = Convert.ToByte(GetSetting(appName, section, "SucursalID", String.Empty));
DateTime date = DateTime.Now;
string datewithformat = date.ToString();
string dateday = date.ToString("dd MMMM yyyy HH mm ");
using (ExcelEngine excelEngine = new ExcelEngine())
{
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//IStyle headerStyle = wo.Styles.Add("HeaderStyle");
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.EnableSheetCalculations();
DataTable tabla = GetExistenciaDataTable();
int osos = tabla.Rows.Count;
worksheet.ImportDataTable(tabla, true, 2, 1);
worksheet.AutoFilters.FilterRange = worksheet.Range["A2:H2"];
string namesuc = GetName(intSUCURSALID);
worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al " + dateday + namesuc;
// worksheet.Range["A1"].Text = "Llantas y Rines del Guadiana S.A. de C.V. - Existencias LRG Al "+dateday+"- B4 Francisco Villa";
worksheet.Rows[1].FreezePanes();
worksheet.Rows[2].FreezePanes();
IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.BeginUpdate();
workbook.SetPaletteColor(8, System.Drawing.Color.FromArgb(46, 204, 113));
headerStyle.Color = System.Drawing.Color.FromArgb(46, 204, 113);
headerStyle.Font.Bold = true;
headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
headerStyle.EndUpdate();
worksheet.Rows[1].CellStyle = headerStyle;
IStyle pStyle = workbook.Styles.Add("pStyle");
pStyle.BeginUpdate();
workbook.SetPaletteColor(9, System.Drawing.Color.FromArgb(89, 171, 227));
pStyle.Color = System.Drawing.Color.FromArgb(89, 171, 227);
pStyle.Font.Bold = true;
pStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
pStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
pStyle.EndUpdate();
worksheet.Rows[0].CellStyle = pStyle;
worksheet.SetColumnWidth(1, 13);
worksheet.SetColumnWidth(2, 50);
worksheet.SetColumnWidth(3, 17);
worksheet.SetColumnWidth(4, 28);
// Create Table with data in the given range
int soviet = osos;
int rojos = soviet + 3;
int rus = soviet + 4;
string rusia = rus.ToString();
string cossacks = rojos.ToString();
string gulag = "A2:H" + cossacks + "";
//IListObject table = worksheet.ListObjects.Create("Table1", worksheet[gulag]);
string registros = soviet.ToString();
//IRange range = worksheet.Range[gulag];
//table.ShowTotals = true;
//table.Columns[0].TotalsRowLabel = "Total";
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
string chorchill = "H2,H" + cossacks + "";
string russevel = "H" + rusia + "";
string totalr = "A" + rusia + "";
worksheet.Range[totalr].Text = registros + " Registros";
worksheet.Range[totalr].CellStyle = pStyle;
string nrusia = "=SUM(H2:H" + cossacks + ")";
worksheet.Range[russevel].Formula = nrusia;
worksheet.Range[russevel].CellStyle = pStyle;
//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
//table.Columns[5].TotalsCalculation = ExcelTotalsCalculation.Sum;
//hacer el subtotal pero conformula ** el otro marca error con total calculation
//range.SubTotal(0, ConsolidationFunction.Sum, new int[] {1,rojos});
string namer = dateday;
string fileName = "LRG-Existencias al " + namer + namesuc + ".xlsx";
// string fileName = "LRG-Existencias al " + namer + "B4 Francisco Villa.xlsx";
workbook.SaveAs(fileName);
workbook.Close();
excelEngine.Dispose();
}
}
public ActionResult Performance(string SaveOption, string Import, string rowCount, string colCount)
{
if (SaveOption == null)
{
return(View());
}
int rows = Convert.ToInt32(rowCount);
int columns = Convert.ToInt32(colCount);
if (SaveOption == "Xls")
{
if (columns > 256)
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Column count must be less than or equal to 256 for Excel 2003 format.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>");
return(View());
}
if (rows > 65536)
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Row count must be less than or equal to 65,536 for Excel 2003 format.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>");
return(View());
}
}
if (SaveOption == "Xlsx")
{
if (rows > 100001)
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Row count must be less than or equal to 100,000.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>");
return(View());
}
if (columns > 151)
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Column count must be less than or equal to 151.');document.location='" + VirtualPathUtility.ToAbsolute("~/XlsIO/Performance") + "';</script>");
return(View());
}
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
if (SaveOption == "Xlsx")
{
application.DefaultVersion = ExcelVersion.Excel2016;
}
else
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
}
workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
if (Import == "importonsave")
{
workbook.Version = ExcelVersion.Excel2013;
DataTable dataTable = new DataTable();
for (int column = 1; column <= columns; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rows; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= columns; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
sheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
IMigrantRange migrantRange = sheet.MigrantRange;
for (int column = 1; column <= columns; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.SetValue("Column: " + column.ToString());
}
//Writing Data using normal interface
for (int row = 2; row <= rows; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= columns; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.SetValue(row * column);
}
}
}
try
{
if (SaveOption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "Performance.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "Performance.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
public ActionResult Performance(string SaveOption, string Import, string rowCount, string colCount)
{
if (SaveOption == null)
{
return(View());
}
int rows = Convert.ToInt32(rowCount);
int columns = Convert.ToInt32(colCount);
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Creating new workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
#region Generate Excel
if (Import == "importonsave")
{
DataTable dataTable = new DataTable();
for (int column = 1; column <= columns; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rows; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= columns; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
sheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
IMigrantRange migrantRange = workbook.Worksheets[0].MigrantRange;
for (int column = 1; column <= columns; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.Text = "Column: " + column.ToString();
}
//Writing Data using normal interface
for (int row = 2; row <= rows; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= columns; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.Number = row * column;
}
}
}
#endregion
string ContentType = null;
string fileName = null;
if (SaveOption == "ExcelXls")
{
ContentType = "Application/vnd.ms-excel";
fileName = "Sample.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "Sample.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
public ActionResult Invoice(string id, string SaveOption)
{
if (SaveOption == null)
{
InitializeData();
return(View());
}
int invoiceId = Convert.ToInt32(id);
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
sqlCeConnection = new SqlCeConnection();
if (sqlCeConnection.ServerVersion.StartsWith("3.5"))
{
sqlCeConnection.ConnectionString = "Data Source = " + ResolveApplicationDataPath("NorthwindIO_3.5.sdf");
}
else
{
sqlCeConnection.ConnectionString = "Data Source = " + ResolveApplicationDataPath("NorthwindIO.sdf");
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
if (SaveOption == "Xlsx")
{
application.DefaultVersion = ExcelVersion.Excel2016;
workbook = application.Workbooks.Open(ResolveApplicationDataPath("Invoice.xlsx"));
}
else
{
application.DefaultVersion = ExcelVersion.Excel97to2003;
workbook = application.Workbooks.Open(ResolveApplicationDataPath("Invoice.xls"));
}
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["A5"].Text = "One Portals Way";
sheet.Range["A6"].Text = "Twin Points WA 98156";
sheet.Range["A7"].Text = "Phone: 1-206-555-1417 ";
sheet.Range["A8"].Text = "Fax: 1-206-555-5938";
sheet.Range["D5"].Text = "INVOICE NO:";
sheet.Range["D6"].Text = "DATE:";
sheet.Range["D7"].Text = "CUSTOMER ID :";
sheet.Range["E6"].Formula = "TODAY()";
sheet.Range["E5"].Number = invoiceId;
//Set values for Ship To.
GetShipDetails(invoiceId);
sheet.Range["E7"].Text = shipName;
sheet.Range["E10"].Text = shipName;
sheet.Range["E11"].Text = address;
sheet.Range["E12"].Text = shipCity;
sheet.Range["E13"].Text = shipCountry;
//Set values for Bill To.
sheet.Range["B10"].Text = shipName;
sheet.Range["B11"].Text = address;
sheet.Range["B12"].Text = shipCity;
sheet.Range["B13"].Text = shipCountry;
//Calculates sub total
sheet.Range["E27"].Formula = "SUM(E20:E26)";
//Set the number format
sheet.Range["E20:E29"].NumberFormat = "$#,##0.00";
sheet.Range["E28"].Value = freight.ToString();
//Calculates final total
sheet.Range["E29"].Formula = "E27+E28";
//Import the data tables.
sheet.ImportDataTable(GetOrder(invoiceId), false, 17, 1);
sheet.ImportDataTable(GetProductDetails(invoiceId), false, 20, 1);
//Calculates price from discount and unit price.
for (int i = 20; i <= 26; i++)
{
if (sheet.Range["A" + i.ToString()].Value == "")
{
break;
}
else
{
sheet.Range["E" + i.ToString()].Formula = "(B" + i.ToString() + "*C" + i.ToString() + ")- (D" + i.ToString() + "/100)";
}
}
try
{
if (SaveOption == "Xls")
{
return(excelEngine.SaveAsActionResult(workbook, "Invoice.xls", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
return(excelEngine.SaveAsActionResult(workbook, "Invoice.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
adapter.Dispose();
connection.Close();
return(View());
}
void OnExportButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Get DataGrid's datasource as DataTable
System.Data.DataTable dataTable = (System.Data.DataTable)sfGrid.ItemsSource;
//Import DataTable to Excel worksheet.
sheet.ImportDataTable(dataTable, 5, 1, false);
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = COLOR.Color.FromArgb(255, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.Black;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = COLOR.Color.FromArgb(255, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:D1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A1"].RowHeight = 20;
sheet["A2:D2"].Merge();
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A2"].RowHeight = 20;
sheet["A3:A4"].Merge();
sheet["D3:D4"].Merge();
sheet["B3:C3"].Merge();
sheet["B3"].Text = "Sales";
sheet["A3:D4"].CellStyle = tableHeader;
sheet["A3"].Text = "Sales Person";
sheet["B4"].Text = "Jan - Jun";
sheet["C4"].Text = "Jul - Dec";
sheet["D3"].Text = "Change (%)";
sheet.Columns[0].ColumnWidth = 19;
sheet.Columns[1].ColumnWidth = 10;
sheet.Columns[2].ColumnWidth = 10;
sheet.Columns[3].ColumnWidth = 11;
#endregion
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (stream != null)
{
SaveiOS iOSSave = new SaveiOS();
iOSSave.Save("DataTable.xlsx", "application/msexcel", stream);
}
}
private void Button1_Click(object sender, EventArgs e)
{
int rowCount = Convert.ToInt32(textBox1.Value);
int colCount = Convert.ToInt32(textBox2.Value);
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
if (chkImport.Checked)
{
workbook.Version = ExcelVersion.Excel2013;
DataTable dataTable = new DataTable();
for (int column = 1; column <= colCount; column++)
{
dataTable.Columns.Add("Column: " + column.ToString(), typeof(int));
}
//Adding data into data table
for (int row = 1; row < rowCount; row++)
{
dataTable.Rows.Add();
for (int column = 1; column <= colCount; column++)
{
dataTable.Rows[row - 1][column - 1] = row * column;
}
}
sheet.ImportDataTable(dataTable, 1, 1, true, true);
}
else
{
IMigrantRange migrantRange = sheet.MigrantRange;
for (int column = 1; column <= colCount; column++)
{
migrantRange.ResetRowColumn(1, column);
migrantRange.SetValue("Column: " + column.ToString());
}
//Writing Data using normal interface
for (int row = 2; row <= rowCount; row++)
{
//double columnSum = 0.0;
for (int column = 1; column <= colCount; column++)
{
//Writing number
migrantRange.ResetRowColumn(row, column);
migrantRange.SetValue(row * column);
}
}
}
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (stream != null)
{
SaveAndroid androidSave = new SaveAndroid();
androidSave.Save("CreateSheet.xlsx", "application/msexcel", stream, m_context);
}
}
public void convertirXls(string rutaFile, long idInterno)
{
string xlsxFile = rutaFile.Split('.')[0] + ".xlsx";
_logger.LogInformation("Si existe el archivo xlsx {0} lo borra.", xlsxFile);
if (File.Exists(xlsxFile))
{
File.Delete(xlsxFile);
}
try
{
try
{
_logger.LogInformation("Intenta abrir el archivo xls {0}.", rutaFile);
ExcelPackage package = new ExcelPackage();
try
{
DataTableCollection dts;
using (var stream = File.Open(rutaFile, FileMode.Open, FileAccess.Read))
{
_logger.LogInformation("Usa stream para el archivo xls {0}.", rutaFile);
using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
{
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
dts = result.Tables;
_logger.LogInformation("Obtiene tablas el archivo xlsx {0}, {1} tablas.", xlsxFile, result.Tables.Count);
}
}
_logger.LogInformation("Abrió el archivo xls {0}.", rutaFile);
_logger.LogInformation("Intenta escribir el archivo xlsx {0}.", xlsxFile);
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
DataTable dataTable = dts[0];
worksheet.ImportDataTable(dataTable, true, 1, 1);
worksheet.UsedRange.AutofitColumns();
using (FileStream file_stream = new FileStream(xlsxFile, FileMode.Create))
{
_logger.LogInformation("Usa stream para el archivo xlsx {0}.", xlsxFile);
workbook.SaveAs(file_stream);
_logger.LogInformation("Guardó en stream el archivo xlsx {0}.", xlsxFile);
}
}
_logger.LogInformation("Escribió el archivo xlsx {0}.", xlsxFile);
}
catch (Exception e)
{
RegistraError(e, "No se pudo abrir el XLSX");
return;
}
}
catch (Exception e)
{
RegistraError(e, "No se pudo abrir el XLSX");
return;
}
// using (ExcelPackage excel = new ExcelPackage())
// {
// excel.Workbook.Worksheets.Add("Worksheet1");
// excel.Workbook.Worksheets.Add("Worksheet2");
// excel.Workbook.Worksheets.Add("Worksheet3");
// FileInfo excelFile = new FileInfo(rutaFile.Split('.')[0] + "xlsx");
// excel.SaveAs(excelFile);
// }
}
catch (Exception ex)
{
}
procesarXlsx(xlsxFile, idInterno);
}
internal void OnExportClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
//Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Create new DataTable to store the data from DataGrid
DataTable dataTable = new DataTable();
//Convert the Grid's data to DataTable
ConvertGridDataToDataTable(dataTable, dataGrid.ItemsSource);
//Import the DataTable to worksheet
sheet.ImportDataTable(dataTable, 5, 1, false);
// Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = COLOR.Color.FromArgb(255, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.Black;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = COLOR.Color.FromArgb(255, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
// Apply Styles
// Apply style for header
sheet["A1:D1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A1"].RowHeight = 20;
sheet["A2:D2"].Merge();
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A2"].RowHeight = 20;
sheet["A3:A4"].Merge();
sheet["D3:D4"].Merge();
sheet["B3:C3"].Merge();
sheet["B3"].Text = "Sales";
sheet["A3:D4"].CellStyle = tableHeader;
sheet["A3"].Text = "Sales Person";
sheet["B4"].Text = "Jan - Jun";
sheet["C4"].Text = "Jul - Dec";
sheet["D3"].Text = "Change (%)";
sheet.Columns[0].ColumnWidth = 19;
sheet.Columns[1].ColumnWidth = 10;
sheet.Columns[2].ColumnWidth = 10;
sheet.Columns[3].ColumnWidth = 11;
// Saving workbook and disposing objects
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("DataTable.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("DataTable.xlsx", "application/msexcel", stream);
}
}
public async Task ExportToExcel(string GridModel, string gridId, int id, string process, int?userId)
{
ExcelExport exp = new ExcelExport();
GridProperties obj = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel);
//Clear if there are any filter columns
//syncfusion bug in exporting while in filter mode
obj.FilterSettings.FilteredColumns.Clear();
grid = gridId;
count = 0;
if (gridId == "Audits")
{
var audits = await _prepareService.GetAudits();
if (userId.HasValue)
{
audits = audits.Where(a => a.AuditorId == userId.Value || a.Inspection.InspectionSteps.Any(s => s.InspectorId == userId.Value));
}
var auditManage = AuditMapper.ToAuditManageViewModel(audits);
//var dataSource = auditManage.Audits.ToList();
//auditViewModel = dataSource;
//var currentDate = DateTime.Today.ToShortDateString().Replace("/", "-");
//obj.ServerExcelQueryCellInfo = QueryCellInfo;
//exp.Export(obj, dataSource, "Audits " + currentDate + ".xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron");
using (ExcelEngine excelEngine = new ExcelEngine())
{
// Set the default application version as Excel 2016.
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016;
//var group1 = auditManage.Audits.ToList();
var allAudits = auditManage.Audits.ToList();
int i = 0;
//Create a workbook with a worksheet.
IWorkbook workbook = excelEngine.Excel.Workbooks.Create(audits.Select(a => a.Survey).Distinct().Count());
foreach (var survey in audits.Select(a => a.Survey).Distinct())
{
IWorksheet worksheet = workbook.Worksheets[i];
DataTable tbl = new DataTable();
//fill columns
tbl.Columns.Add(LocalizedStrings.GetString("AuditDate"), typeof(string));
tbl.Columns.Add(LocalizedStrings.GetString("Schedule"), typeof(string));
tbl.Columns.Add(LocalizedStrings.GetString("Team"), typeof(string));
tbl.Columns.Add(LocalizedStrings.GetString("Auditor"), typeof(string));
tbl.Columns.Add(LocalizedStrings.GetString("Auditee"), typeof(string));
tbl.Columns.Add(LocalizedStrings.GetString("AuditStatus"), typeof(string));
tbl.Columns.Add(LocalizedStrings.GetString("StandardAuditee"), typeof(string));
foreach (var surveyItem in survey.SurveyItems)
{
tbl.Columns.Add(surveyItem.Query, typeof(int));
}
tbl.Columns.Add("Score", typeof(int));
tbl.Columns.Add(LocalizedStrings.GetString("Comment"), typeof(string));
//fill rows
foreach (var audit in auditManage.Audits.Where(a => a.SurveyId == survey.Id))
{
DataRow dr = null;
dr = tbl.NewRow();
dr[LocalizedStrings.GetString("AuditDate")] = audit.EndDate.Value.ToShortDateString();
dr[LocalizedStrings.GetString("Schedule")] = audit.EndDate.Value.ToShortTimeString().Replace(":", "H");
dr[LocalizedStrings.GetString("Team")] = string.Join(",", audit.AuditorTeams);
dr[LocalizedStrings.GetString("Auditor")] = audit.AuditorName;
dr[LocalizedStrings.GetString("Auditee")] = audit.AuditeeName;
dr[LocalizedStrings.GetString("AuditStatus")] = audits.Select(a => a.Auditor).FirstOrDefault(u => u.UserId == audit.AuditorId).Tenured.HasValue ? audits.Select(a => a.Auditor).FirstOrDefault(u => u.UserId == audit.AuditorId).Tenured.Value ? "Titulaire" : "Intérimaire" : "";
dr[LocalizedStrings.GetString("StandardAuditee")] = audit.ProcessName;
int scoreTemp = 0;
foreach (var surveyItem in survey.SurveyItems)
{
dr[surveyItem.Query] = audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.HasValue ? audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.Value ? 1 : 0 : -1;
if (audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.Value == true)
{
scoreTemp++;
}
}
dr["Score"] = scoreTemp;
tbl.Rows.Add(dr);
}
worksheet.ImportDataTable(tbl, true, 2, 1);
worksheet.Name = survey.Name;
worksheet.UsedRange.WrapText = true;
worksheet.Columns[0].AutofitColumns();
worksheet.SetColumnWidth(1, 10);
IListObject table = worksheet.ListObjects.Create("tbl_" + survey.Name.Replace(" ", "_"), worksheet.UsedRange);
table.BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9;
foreach (var surveyItem in survey.SurveyItems)
{
//Apply conditional formats for IsOK questionnaire items
IConditionalFormats condition = worksheet.Columns[table.Columns.IndexOf(s => s.Name == surveyItem.Query)].ConditionalFormats;
IConditionalFormat condition1 = condition.AddCondition();
condition1.FormatType = ExcelCFType.CellValue;
condition1.Operator = ExcelComparisonOperator.Equal;
condition1.FirstFormula = "1";
condition1.BackColor = ExcelKnownColors.Green;
IConditionalFormat condition2 = condition.AddCondition();
condition2.FormatType = ExcelCFType.CellValue;
condition2.Operator = ExcelComparisonOperator.Equal;
condition2.FirstFormula = "0";
condition2.BackColor = ExcelKnownColors.Red;
worksheet.Columns[table.Columns.IndexOf(s => s.Name == surveyItem.Query)].RowHeight = 80;
worksheet.AutofitColumn(table.Columns.IndexOf(s => s.Name == surveyItem.Query));
}
i++;
}
//worksheet.ImportData(group1, 2, 1, true);
var path = Server.MapPath("~/App_Data/" + LocalizedStrings.GetString("Audits") + ".xlsx");
workbook.SaveAs(path, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2010);
workbook.Close();
excelEngine.Dispose();
}
}
if (gridId == "AuditItems")
{
var audits = await _prepareService.GetAudits(id);
var audit = AuditMapper.ToAuditViewModel(audits.FirstOrDefault());
var dataSource = audit.AuditItems.ToList();
auditItemViewModel = dataSource;
var currentDate = DateTime.Today.ToShortDateString().Replace("/", "-");
obj.ServerExcelQueryCellInfo = QueryCellInfo;
exp.Export(obj, dataSource, LocalizedStrings.GetString("Audit") + " " + process + " " + currentDate + ".xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron");
}
}
