create multiple excels with multiple sheet using ExcelLibrary in C#

Question

I want to export the data in multiple sheets with multiple excel files. As you can see in my image I want to generate sheets as ID changes and when ModalityId changes I want to create new excel.

I have made code like this for this concern:

List<string> lstFile = new List<string>();
if (dtAltTag != null && dtAltTag.Rows.Count > 0)
{
    string filePath = string.Empty;
    string fileName = "";

    Excel.Workbook workBook = new Excel.Workbook();
    var workSheet = new Excel.Worksheet(fileName);

    if (dtAltTag.Rows[0]["OldFormCode"] != null && dtAltTag.Rows[0]["OldFormCode"].ToString() != "")
    {
        fileName = dtAltTag.Rows[0]["OldFormCode"].ToString();
    }
    else
    {
        fileName = dtAltTag.Rows[0]["Code"].ToString();
    }
    workSheet.Name = fileName;
    AddValue(0, workSheet, dtAltTag); // function is used to add the value in the sheet
    workBook.Worksheets.Add(workSheet);

    //data working
    for (int i = 0; i < dtAltTag.Rows.Count; i++)
    {
        //for modality changes and first entery 
        //if (i == 0 || dtAltTag.Rows[i]["ModalityId"] != dtAltTag.Rows[i - 1]["ModalityId"])
        //{
        //if form changes then it should be in other sheet
        if (i != 0 && dtAltTag.Rows[i]["ID"].ToString() != dtAltTag.Rows[i - 1]["ID"].ToString())
        {
            if (dtAltTag.Rows[i]["OldFormCode"] != null && dtAltTag.Rows[i]["OldFormCode"].ToString() != "")
            {
                fileName = dtAltTag.Rows[i]["OldFormCode"].ToString();
            }
            else
            {
                fileName = dtAltTag.Rows[i]["Code"].ToString();
            }
            var workSheet1 = new Excel.Worksheet(fileName);

            AddValue(i, workSheet1, dtAltTag);
        }
        else
        {

        }
    }

    filePath = HostingEnvironment.MapPath(ConfigurationManager.AppSettings["ExcelFilesFilePath"]) + "Allitem";
    if (!File.Exists(filePath))
        Directory.CreateDirectory(filePath);
    filePath = filePath + "\\" + fileName + "_" + DateTime.Now.ToString("MM_dd_yyy_HH_mm_ss") + ".xls";
    workBook.Save(filePath);
    lstFile.Add(filePath);
}
return lstFile;

When the id is changed I append new header but after that, I want to continue export data till the id change can't detect how to do this? How can I get the current sheet where I continue adding the value in else case?

I hope you are cleared of what I am trying to do!

Thanks in advance!

Rafalon · Answer 1 · 2019-11-12T09:29:21.817

Can't you do it otherwise:

loop through data
if ModalityId is different save current file and create a new one (assign it back to current file variable)
if ID is different create a new sheet (append it to current file and assign it back to current sheet variable)
add your data to current sheet
outside the loop, save the current (and last) file.

That could be pseudo-code for it:

var currentFile = new ExcelFile();
var currentSheet = currentFile.AppendSheet(rows[0].SheetName);
string lastFileName;

for(int i = 0; i < rows.Count; i++)
{
    // Adds new file if necessary
    if(i != 0 && rows[i].ModalityId != rows[i - 1].ModalityId)
    {
        currentFile.Save(rows[i - 1].FileName);
        currentFile = new ExcelFile();
    }
    // Adds new sheet if necessary
    if(i != 0 && rows[i].ID != rows[i - 1].ID)
    {
        currentSheet = currentFile.AppendSheet(rows[i].SheetName);
    }

    InsertData(currentSheet, rows[i]);

    lastFileName = rows[i].FileName;
}

currentFile.Save(lastFileName);

Thanks for the reply ! I am using ExcelLibray and can't find the close() function in it ! so how can I close and assign it back to current file variable — heer24, Nov 12 '19 at 09:21
On second thought, you might not need to close it, as you don't really open it. I'll replace "close" with "save" — Rafalon, Nov 12 '19 at 09:26

score 0 · Accepted Answer · answered Nov 12 '19 at 13:01

With the reference of 'Rafalon' code , I made some change in my code and its working fine ! thank You!

My code is as below:

 var filePath = "";
            string fileName = "";

            if (dtAltTag.Rows[0]["OldFormCode"] != null && dtAltTag.Rows[0]["OldFormCode"].ToString() != "")
            {
                fileName = dtAltTag.Rows[0]["OldFormCode"].ToString();
            }
            else
            {
                fileName = dtAltTag.Rows[0]["Code"].ToString();
            }

            int Row = 0;
            var currentFile = new Excel.Workbook();
            var currentSheet = new Excel.Worksheet(fileName);
            currentFile.Worksheets.Add(currentSheet);

            List<string> lstFile = new List<string>();
            if (dtAltTag != null && dtAltTag.Rows.Count > 0)
            {


                for (int i = 0; i < dtAltTag.Rows.Count; i++)
                {

                    if (i == 0)
                    {
                        AddValue(i, currentSheet, dtAltTag, Row);
                    }
                    else
                    {
                        if (dtAltTag.Rows[i]["ModalityId"].ToString() != dtAltTag.Rows[i - 1]["ModalityId"].ToString())
                        {
                            filePath = HostingEnvironment.MapPath(ConfigurationManager.AppSettings["ExcelFilesFilePath"]) + "Allitem";
                            if (!File.Exists(filePath))
                                Directory.CreateDirectory(filePath);
                            filePath = filePath + "\\" + fileName + "_" + DateTime.Now.ToString("MM_dd_yyy_HH_mm_ss") + ".xls";
                            lstFile.Add(filePath);
                            currentFile.Save(filePath);
                            currentFile = new Excel.Workbook();

                        }
                        if (dtAltTag.Rows[i]["ID"].ToString() != dtAltTag.Rows[i - 1]["ID"].ToString())
                        {

                            if (dtAltTag.Rows[i]["OldFormCode"] != null && dtAltTag.Rows[i]["OldFormCode"].ToString() != "")
                            {
                                fileName = dtAltTag.Rows[i]["OldFormCode"].ToString();
                            }
                            else
                            {
                                fileName = dtAltTag.Rows[i]["Code"].ToString();
                            }
                            currentSheet = new Excel.Worksheet(fileName);
                            currentFile.Worksheets.Add(currentSheet);
                            Row = 0;

                        }

                        AddValue(i, currentSheet, dtAltTag, Row);


                    }
                    Row++;

                }


                filePath = HostingEnvironment.MapPath(ConfigurationManager.AppSettings["ExcelFilesFilePath"]) + "Allitem";
                if (!File.Exists(filePath))
                    Directory.CreateDirectory(filePath);
                filePath = filePath + "\\" + fileName + "_" + DateTime.Now.ToString("MM_dd_yyy_HH_mm_ss") + ".xls";           
                currentFile.Save(filePath);
                lstFile.Add(filePath);

            }

This function of adding value in sheet is might be useful:

private void AddValue(int i, Excel.Worksheet workSheet, DataTable dtAltTag, int Row)
        {
            //Header Working starts here
            string[] columName = new string[] { "column1", "column2", "column2", "column2", "column2" }; //add header as per you requirement
            if (Row == 0)
            {
                for (int c = 0; c < columName.Length; c++)
                {
                    workSheet.Cells[0, c] = new Excel.Cell(columName[c]);
                }
            }

            //add data
            for (int c = 0; c < columName.Length; c++)
            {
                workSheet.Cells[Row + 1, c] = new Excel.Cell(dtAltTag.Rows[i].ItemArray[c].ToString());            
            }


        }

create multiple excels with multiple sheet using ExcelLibrary in C#

2 Answers2