/// <summary> /// Generic excel creation using data set as input /// </summary> /// <param name="dsSource"></param> /// <param name="strFilename"></param> /// <returns></returns> public string WriteExcel(DataSet dsSource, string strFilename, CustomExcelProperties objCustomExcelProperties) { string strMessage = string.Empty; try { var workbook = new XLWorkbook(); foreach (DataTable dataTable in dsSource.Tables) { string sheetName = dataTable.TableName; var worksheet = workbook.Worksheets.Add(sheetName); worksheet.TabColor = objCustomExcelProperties.TabColor; foreach (DataRow datarow in dataTable.Rows) { for (int i = 1; i <= dataTable.Columns.Count; i++) { worksheet.Cell(1, i).Value = dataTable.Columns[i - 1].ColumnName.ToString(); worksheet.Cell(1, i).Style.Font.Bold = objCustomExcelProperties.ColumnHeaderFontBold; worksheet.Cell(1, i).Style.Font.FontColor = objCustomExcelProperties.ColumnHeaderFontColor; worksheet.Cell(1, i).Style.Fill.BackgroundColor = objCustomExcelProperties.ColumnHeaderBackgroundColor; worksheet.Cell(1, i).Style.Font.Italic = objCustomExcelProperties.ColumnHeaderFontItalic; worksheet.Cell(1, i).Style.Font.Strikethrough = objCustomExcelProperties.ColumnHeaderStrikeFont; worksheet.Cell(1, i).Style.Font.FontName = objCustomExcelProperties.ColumnHeaderFontStyle; worksheet.Cell(1, i).Style.Alignment.WrapText = objCustomExcelProperties.ColumnHeaderWrapText; worksheet.Cell(1, i).Style.Alignment.Horizontal = objCustomExcelProperties.ColumnHeaderAlignmentHorizontal; for (int j = 0; j < dataTable.Rows.Count; j++) { for (int k = 0; k < dataTable.Columns.Count; k++) { worksheet.Cell(j + 2, k + 1).Value = dataTable.Rows[j].ItemArray[k].ToString(); worksheet.Cell(j + 2, k + 1).Style.Font.FontColor = objCustomExcelProperties.RowFontColor; worksheet.Cell(j + 2, k + 1).Style.Fill.BackgroundColor = objCustomExcelProperties.RowBackgroundColor; worksheet.Cell(j + 2, k + 1).Style.Font.Italic = objCustomExcelProperties.RowFontItalic; worksheet.Cell(j + 2, k + 1).Style.Font.Bold = objCustomExcelProperties.RowFontBold; worksheet.Cell(j + 2, k + 1).Style.Font.Strikethrough = objCustomExcelProperties.RowStrikeFont; worksheet.Cell(j + 2, k + 1).Style.Font.FontName = objCustomExcelProperties.RowFontStyle; worksheet.Cell(j + 2, k + 1).Style.Alignment.WrapText = objCustomExcelProperties.RowWrapText; worksheet.Cell(j + 2, k + 1).Style.Alignment.Horizontal = objCustomExcelProperties.RowAlignmentHorizontal; } } } } ////if highlight cell is true then proceed if (objCustomExcelProperties.HighlightCellRequired) { for (int intHighlight = 0; intHighlight < objCustomExcelProperties.HighlightCellRownumberList.Count(); intHighlight++) { if (objCustomExcelProperties.HighlightCellRownumberList[intHighlight].HighlightCellRownumber <= dataTable.Rows.Count && objCustomExcelProperties.HighlightCellRownumberList[intHighlight].HighlightCellColumnnumber <= dataTable.Columns.Count) { worksheet.Cell(objCustomExcelProperties.HighlightCellRownumberList[intHighlight].HighlightCellRownumber, objCustomExcelProperties.HighlightCellRownumberList[intHighlight].HighlightCellColumnnumber).Style.Fill.BackgroundColor = objCustomExcelProperties.HighlightCellColor; } } } //if additional header is defined if (objCustomExcelProperties.AddtionalHeaderRequired) { for (int introw = 0; introw < objCustomExcelProperties.AddtionalHeaderRownumbers.Count(); introw++) { for (int intcolumn = 1; intcolumn <= dataTable.Columns.Count; intcolumn++) { if (objCustomExcelProperties.AddtionalHeaderRownumbers[introw] <= dataTable.Rows.Count) { worksheet.Cell(objCustomExcelProperties.AddtionalHeaderRownumbers[introw], intcolumn).Style.Fill.BackgroundColor = objCustomExcelProperties.ColumnHeaderBackgroundColor; } } } } } //change needed in the BAS code string root = @"C:\Users\N.SANGAVI\Documents\visual studio 2010\Projects\Test_webapplication\tmp\"; if (!Directory.Exists(root)) { Directory.CreateDirectory(root); } System.IO.DirectoryInfo di = new DirectoryInfo(root); foreach (FileInfo file in di.GetFiles()) { if (file.Length > 0) { file.Delete(); } } strFilename = strFilename + DateTime.Now.ToString("_yyyMMdd_HHmmss") + ".xlsx"; workbook.SaveAs(Path.Combine(root + strFilename)); // Response.ContentType = ContentType; // Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(Path.Combine(root + strFilename))); // Response.WriteFile(Path.Combine(root + strFilename)); // Response.End(); return(strMessage = "SUCCESS"); } catch (Exception ex) { return(ex.Message); } finally { } }
protected void Page_Load(object sender, EventArgs e) { DataTable employeeTable = new DataTable("Employee"); employeeTable.Columns.Add("Employee ID"); employeeTable.Columns.Add("Employee Name"); employeeTable.Columns.Add("Employee des"); employeeTable.Rows.Add("1", "Praveen", "Tester"); employeeTable.Rows.Add("2", "PraveenDEF", "Tester"); employeeTable.Rows.Add("3", "PraveenPQR", "Tester1"); employeeTable.Rows.Add("24", "XYZ", "Tester1"); employeeTable.Rows.Add("21", "Praveen", "develop2er"); employeeTable.Rows.Add("22", "PraveenDEF", "develope2r"); employeeTable.Rows.Add("23", "PraveenPQR", "developer2"); employeeTable.Rows.Add("34", "XYZ", "developer2"); employeeTable.Rows.Add("31", "Praveen", "developer1"); employeeTable.Rows.Add("32", "PraveenDEF", "BA"); employeeTable.Rows.Add("33", "PraveenPQR", "BA1"); employeeTable.Rows.Add("54", "XYZ", "eBA1");; //Create a Department Table DataTable departmentTable = new DataTable("Department"); departmentTable.Columns.Add("Department ID"); departmentTable.Columns.Add("Department Name"); departmentTable.Rows.Add("1", "IT"); departmentTable.Rows.Add("2", "HR"); departmentTable.Rows.Add("3", "Finance"); departmentTable.Rows.Add("31", "IT"); departmentTable.Rows.Add("32", "HR"); departmentTable.Rows.Add("33", "Finance"); departmentTable.Rows.Add("41", "IT"); departmentTable.Rows.Add("42", "HR"); departmentTable.Rows.Add("43", "Finance"); //Create a DataSet with the existing DataTables DataSet ds = new DataSet("Organization"); ds.Tables.Add(employeeTable); ds.Tables.Add(departmentTable); ///ADDITIONAL HEADER OPTIONS CustomExcelProperties objCustomExcelProperties = new CustomExcelProperties(); objCustomExcelProperties.ColumnHeaderBackgroundColor = XLColor.Red; objCustomExcelProperties.TabColor = XLColor.Green; objCustomExcelProperties.ColumnHeaderAlignmentHorizontal = XLAlignmentHorizontalValues.Center; //specify the additional row header objCustomExcelProperties.AddtionalHeaderRequired = true; objCustomExcelProperties.AddtionalHeaderRownumbers = new int[] { 4, 6 }; ///CELL HIGHLIGHT OPTIONS objCustomExcelProperties.HighlightCellRequired = true; CustomHighlightCellRownumber objCustomHighlightCellRownumber = new CustomHighlightCellRownumber(); objCustomHighlightCellRownumber.HighlightCellColumnnumber = 2; objCustomHighlightCellRownumber.HighlightCellRownumber = 2; objCustomExcelProperties.HighlightCellRownumberList.Add(objCustomHighlightCellRownumber); CustomHighlightCellRownumber objCustomHighlightCellRownumber1 = new CustomHighlightCellRownumber(); objCustomHighlightCellRownumber1.HighlightCellColumnnumber = 2; objCustomHighlightCellRownumber1.HighlightCellRownumber = 8; objCustomExcelProperties.HighlightCellRownumberList.Add(objCustomHighlightCellRownumber1); objCustomExcelProperties.HighlightCellColor = XLColor.OldGold; WriteExcel(ds, "test", objCustomExcelProperties); }