I work on c#5 app I face Issue Cannot create multiple Excel files With Multiple Excel Sheets With multiple Modules
that mean one file have multiple Tab or sheet and every sheet have multiple modules with data .
suppose I have data table dt and return data below as Input :
what I tryCode:Create Multiple Output Excel File with Multiple Tab with Multiple Module PartId Company Files Tab Module 1222 micro Abc source 1 1321 silicon Abc Types 3 1444 cd2 AutoD Rev 10 1321 cd3 AutoD source 11 1541 tvs AutoD Rev 12 9811 tvs2 Mog Dal 5 1901 tvs Mog Mondo 6 2111 toyo Mog Pingo 7
Expected Result as Image :Code:DataSet ds = new DataSet(); var result = from rows in dt.AsEnumerable() group rows by new { Files = rows["Files"] } into grp select grp; foreach (var item in result) { ds.Tables.Add(item.CopyToDataTable()); } Affected = new CExcel().createExcelFileForDs(ds, exportPath); public Boolean createExcelFileForDs(DataSet ds, String FullFilePathName) { Boolean IsDone = false; try { FileInfo CreatedFile = new FileInfo(FullFilePathName); Boolean ISNew = false; if (!CreatedFile.Exists) { ISNew = true; } using (var pck = new ExcelPackage(CreatedFile)) { ExcelWorksheet ws; foreach (DataTable Table in ds.Tables) { if (ISNew == true) { ws = pck.Workbook.Worksheets.Add(Convert.ToString(Table.Rows[0]["Tab"])); ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet if (System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft) // Right to Left for Arabic lang { ExcelWorksheetView wv = ws.View; wv.ZoomScale = 100; wv.RightToLeft = true; ws.PrinterSettings.Orientation = eOrientation.Landscape; ws.Cells.AutoFitColumns(); } else { ExcelWorksheetView wv = ws.View; wv.ZoomScale = 100; wv.RightToLeft = false; ws.PrinterSettings.Orientation = eOrientation.Landscape; ws.Cells.AutoFitColumns(); } ws.Cells.AutoFitColumns(); ws.Cells[1, 1].LoadFromDataTable(Table, ISNew, OfficeOpenXml.Table.TableStyles.Light8); } else { ws = pck.Workbook.Worksheets.FirstOrDefault(); ws.Cells[2, 1].LoadFromDataTable(Table, ISNew); } } pck.Save(); IsDone = true; } } catch (Exception ex) { throw ex; } return IsDone; }
http://www.mediafire.com/view/w8pw3w...files.png/file




Reply With Quote
