DataTable dt = SqlHelper.ReturnDataTable("select companymasterid ,name as 'Company Name' ,ISNULL(TotalToken, 0 ) as 'Total Tokens',ISNULL(LeftToken, 0 ) as 'Approve Tokens',(ISNULL(TotalToken, 0 ) - ISNULL(LeftToken, 0 )) as 'Remaining Tokens' from companymaster where isactive='Y' and companymasterid=" + companyId + " and companymasterid !=0 order by name asc");
if (dt.Rows.Count > 0)
{
string path = Server.MapPath("exportedfiles\\");
if (!Directory.Exists(path)) // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
{
Directory.CreateDirectory(path);
}
File.Delete(path + "EmployeeDetails.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE.
// ADD A WORKBOOK USING THE EXCEL APPLICATION.
Excel.Application xlAppToExport = new Excel.Application();
xlAppToExport.Workbooks.Add("");
// ADD A WORKSHEET.
Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet);
xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"];
// ROW ID FROM WHERE THE DATA STARTS SHOWING.
int iRowCnt = 2;
Excel.Range range = xlWorkSheetToExport.Cells[2, 1] as Excel.Range;
range.EntireRow.Font.Name = "Calibri";
// xlWorkSheetToExport.Cells[2, 1].Font.Bold = true;
//range.Rows.AutoFit();
//range.Columns.AutoFit();
//Excel.Range range;
//range.EntireRow.Font.Bold = true;
//range.EntireRow.Font.Size = 20;
Microsoft.Office.Interop.Excel.Range oRange = (Microsoft.Office.Interop.Excel.Range)xlWorkSheetToExport.Cells[2, 3];
//oRange.ColumnWidth = 40;
//oRange.RowHeight = 45;
oRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
float Left = (float)((double)oRange.Left);
float Top = (float)((double)oRange.Top);
//ws.Cells["A1:K20"].AutoFitColumns();
//oRange("E:E").ColumnWidth = 17.29;
//const float ImageSize = 32;
//add image in particular cells with Karco logo
//xlWorkSheetToExport.Shapes.AddPicture("C:\\Users\\admin\\Desktop\\images.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 165, 20, ImageSize, ImageSize);
//xlWorkSheetToExport.Range["A1:E1"] = true; // MERGE CELLS OF THE HEADER.
//xlWorkSheetToExport.Cells[3, 3] = "Karishma Marine Solutions Pvt Ltd.";
//xlWorkSheetToExport.Cells[4, 3] = " KARCO CBT VER 1.2";
// SHOW COLUMNS ON THE TOP.
xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "Company Name";
xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "Total Tokens";
xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "Approve Tokens";
xlWorkSheetToExport.Cells[iRowCnt - 1, 4] = "Remaining Tokens";
//xlWorkSheetToExport.Cells[iRowCnt - 1, 5] = "timestamp";
int i;
for (i = 0; i <= dt.Rows.Count - 1; i++)
{
xlWorkSheetToExport.Cells[iRowCnt, 1] = dt.Rows[i].Field<string>("Company Name");
xlWorkSheetToExport.Cells[iRowCnt, 2] = dt.Rows[i].Field<int>("Total Tokens");
xlWorkSheetToExport.Cells[iRowCnt, 3] = dt.Rows[i].Field<int>("Approve Tokens");
xlWorkSheetToExport.Cells[iRowCnt, 4] = dt.Rows[i].Field<int>("Remaining Tokens");
//range.EntireColumn.ColumnWidth = 50.43;
//xlWorkSheetToExport.Cells[iRowCnt, 5] = dt.Rows[i].Field<int>("timestamp");
iRowCnt = iRowCnt + 1;
}
// FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
Excel.Range range11 = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Excel.Range;
//range11.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3);
// SAVE THE FILE IN A FOLDER.
xlWorkSheetToExport.SaveAs(path + "EmployeeDetails.xlsx");
// CLEAR.
xlAppToExport.Workbooks.Close();
xlAppToExport.Quit();
xlAppToExport = null;
xlWorkSheetToExport = null;
//string path = Server.MapPath("exportedfiles\\");
// CHECK IF THE FOLDER EXISTS.
if (Directory.Exists(path))
{
// CHECK IF THE FILE EXISTS.
if (File.Exists(path + "EmployeeDetails.xlsx"))
{
string sPath = Server.MapPath("exportedfiles\\");
Response.AppendHeader("Content-Disposition", "attachment; filename=EmployeeDetails.xlsx");
Response.TransmitFile(sPath + "EmployeeDetails.xlsx");
Response.End();
}
}
}
}
catch (Exception ex)
{
//lblConfirm.Text = ex.Message.ToString();
//lblConfirm.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial");
}
finally
{
//sda.Dispose();
//sda = null;
}
