locked
How to create multiple Excel files with multiple Tab sheet with multiple modules ? RRS feed

  • Question

  • I work on csharp5.0 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 as Below :

    Create Multiple Output Excel File with Multiple Tab with Multiple Module					
    PartId	Company	Files	Tab name  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	tvs3	Mog	Mondo	     6	
    2111	toyo	Mog	Pingo	     7	

    what I try 

     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);

    this create one excel success but more than excel file I dont know How

     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;
            }

    Expected Result as Image :

    create 3 files Abc,AutoD,Mog

    Abc have two sheet source and Types , source sheet name have one row and Types Have one row .

    AutoD have two sheetes Rev and Source , source sheet name have one row and Rev Have two rows .

    Mog have Three sheetes Dal and Mondo and Pingo , Dal sheet name have one row and Mondo Have one row and Pingo have one row .



    Thursday, June 4, 2020 10:53 PM

Answers

  • Hi,

    Thank you for posting here.

    I still use ClosedXML which I used in your last thread, this package is developed based on OpenXML API.


            static void Main(string[] args)
            {
                DataTable dataTable = GetData();
    
                var res = from row in dataTable.AsEnumerable()
                          group row by row["Files"] into g
                          select g;
    
                foreach (var item in res)
                {
                    CreateWorkbook(item.Key.ToString(), item.AsEnumerable().CopyToDataTable());
                }
            }
    
            public static void CreateWorkbook(String file, DataTable dt)
            {
                string fileName = file + ".xlsx";
                XLWorkbook workbook;
                if (!File.Exists(fileName))
                {
                    workbook = new XLWorkbook();
                }
                else
                {
                    workbook = new XLWorkbook(fileName);
                }
                
                var res = from row in dt.AsEnumerable()
                          group row by row["Tab"] into g
                          select g;
    
                foreach (var item in res)
                {
                    DataTable dataTable = item.AsEnumerable().CopyToDataTable();
                    workbook.Worksheets.Add(dataTable, dataTable.Rows[0].Field<string>("Tab"));
                }
    
                workbook.SaveAs(fileName);
            }
            public static DataTable GetData() 
            {
                DataTable dataTable = new DataTable();
                dataTable.Columns.Add("PartId", typeof(int));
                dataTable.Columns.Add("Company", typeof(string));
                dataTable.Columns.Add("Files", typeof(string));
                dataTable.Columns.Add("Tab", typeof(string));
                dataTable.Columns.Add("Module", typeof(int));
    
                dataTable.Rows.Add(1222,"micro","Abc","source",1);
                dataTable.Rows.Add(1321, "silicon", "Abc", "Types", 3);
                dataTable.Rows.Add(1444, "cd2", "AutoD", "Rev", 10);
                dataTable.Rows.Add(1321, "cd3", "AutoD", "source", 11);
                dataTable.Rows.Add(1541,"mtvscro", "AutoD", "Rev", 12);
                dataTable.Rows.Add(9811, "tvs2", "Mog", "Dal", 6);
                dataTable.Rows.Add(1901, "tvs3", "Mog", "Mondo", 6);
                dataTable.Rows.Add(2111, "toyo", "Mog", "Pingo", 7);
    
                return dataTable;
            }


    Result:

    Abc.xlsx

    AutoD.xlsx

    Mog.xlsx

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 5, 2020 5:26 AM