7 Replies - 1266 Views - Last Post: 15 July 2020 - 05:03 AM Rate Topic: -----

#1 ahmedsa   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 08-November 19

How to create excel file with multiple sheet name based on modules?

Posted 03 June 2020 - 08:10 PM

I work on c# desktop app I Can't export data to excel sheet with multiple tab(multi sheet)

based on data exist on data table module field

I use open XML library

Data table data as below :

Divide Output Excel File To Multi Tab based On Module

PartId  Company Files   Tab     Module  
1222    micro   Abc     source     1    
1321    silicon Abc     source     1    
1444    cd2     Abc     types      2    
1321    cd3     Abc     types      2    
1541    tvs     Abc     types      2

Expected Result :

Create File ABC.xlsx with two sheet first sheet name source and second sheet name types based on module and load data related to every sheet based on data exist on data table.

so if I have two modules meaning I have two sheet .

What I have tried:

public Boolean createExcelFile(DataTable Table,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;
                  if (ISNew == true)
                  {
                      ws = pck.Workbook.Worksheets.Add("Sheet");


                      if (System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.IsRightToLeft)   // Right to Left for Arabic lang
                      {
                          ExcelWorksheetView wv = ws.View;

                          wv.RightToLeft = true;
                          ws.PrinterSettings.Orientation = eOrientation.Landscape;

                      }
                      else
                      {
                          ExcelWorksheetView wv = ws.View;

                          wv.RightToLeft = false;
                          ws.PrinterSettings.Orientation = eOrientation.Landscape;

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


but problem code above create one files with one sheet only

so How to create files with multi sheet based on module ?

Is This A Good Question/Topic? 0
  • +

Replies To: How to create excel file with multiple sheet name based on modules?

#2 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7455
  • View blog
  • Posts: 25,095
  • Joined: 05-May 12

Re: How to create excel file with multiple sheet name based on modules?

Posted 04 June 2020 - 05:34 AM

I'm not seeing any loop where you are trying to create multiple sheets. I see only one call to create a sheet on line 18.
Was This Post Helpful? 0
  • +
  • -

#3 Sheepings   User is offline

  • D.I.C Lover
  • member icon

Reputation: 241
  • View blog
  • Posts: 1,303
  • Joined: 05-December 13

Re: How to create excel file with multiple sheet name based on modules?

Posted 04 June 2020 - 09:29 AM

Eh! Cross-poster, and would you look at that. He knows how to use code tags over here but not over on https://csharpforums...581/#post-16523
Was This Post Helpful? 0
  • +
  • -

#4 ahmedsa   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 08-November 19

Re: How to create excel file with multiple sheet name based on modules?

Posted 04 June 2020 - 06:47 PM

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 :


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	tvs3	Mog	Mondo	6	
2111	toyo	Mog	Pingo	7	


what I try

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


Expected Result as Image :

http://www.mediafire....files.png/file

This post has been edited by ahmedsa: 04 June 2020 - 06:48 PM

Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15799
  • View blog
  • Posts: 63,299
  • Joined: 12-June 08

Re: How to create excel file with multiple sheet name based on modules?

Posted 04 June 2020 - 07:04 PM

Please do not create duplicate topics. Merging.
Was This Post Helpful? 0
  • +
  • -

#6 Sheepings   User is offline

  • D.I.C Lover
  • member icon

Reputation: 241
  • View blog
  • Posts: 1,303
  • Joined: 05-December 13

Re: How to create excel file with multiple sheet name based on modules?

Posted 05 June 2020 - 07:06 AM

Even if we could see your image. (Which we can't) It doesn't tell us what your problem is with your new code, that is your job. There are no mind readers on this forums, as far as i know. It also doesn't tell us what your code is meant to do since changing it. That's for you to explain. Which you have not.

Perhaps it would be a good place to start, by explaining your code to us, and then explain the problem you have with said code? Which line of code is the problem, and have you tried debugging that iteration cycle to see what data is being added?

Being descriptive is what enables us to give you straight answers. If you are as vague as you are on the other forum, you will likely receive the vague responses.
Was This Post Helpful? 0
  • +
  • -

#7 ahmedsa   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 08-November 19

Re: How to create excel file with multiple sheet name based on modules?

Posted 05 June 2020 - 08:13 AM

Thank you for reply
this is not merging or duplicate topic this topic is talking about create Excel files with multi sheet

but another create only one file with multiple sheet .

also I need to helping me on code above for create multiple excel file with multi sheet if possible ?
Was This Post Helpful? 0
  • +
  • -

#8 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7455
  • View blog
  • Posts: 25,095
  • Joined: 05-May 12

Re: How to create excel file with multiple sheet name based on modules?

Posted 15 July 2020 - 05:03 AM

The issue our OP is not with regards to creating the files or contents based on the previous two variations of code he has posted. The issue our OP has is that he can't figure out the logic of how to create the them. This is a recurring theme for this OP where he tends to not delve to deeply into algorithms.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1