How to read multiple excel files and export them into another excel by C# RSS

8 replies

Last post Mar 12, 2020 07:52 AM by Leon Davis

‹ Previous Thread|Next Thread ›

Shortcuts

- Reply
ttprettycode...
Member

5 Points

20 Posts

How to read multiple excel files and export them into another excel by C#

Mar 03, 2020 03:10 AM|ttprettycoder|LINK

Hi experts,

I want to export data from multiple excel files into anther excel file.

In my demo,i don't need to connect database,all my data source is my multiple excels.

Can anyone help me to make a demo?

Anybody can help me.

I'll be grateful if someone can answer my question.

thanks very much

Sagar-Jaybha...

Member

10 Points

6 Posts

Re: How to read multiple excel files and export them into another excel by C#

Mar 03, 2020 05:26 AM|Sagar-Jaybhay|LINK

public DataTable ReadExcel(string fileName, string fileExt) {  
    string conn = string.Empty;  
    DataTable dtexcel = new DataTable();  
    if (fileExt.CompareTo(".xls") == 0)  
        conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007  
    else  
        conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007  
    using(OleDbConnection con = new OleDbConnection(conn)) {  
        try {  
            OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1  
            oleAdpt.Fill(dtexcel); //fill excel data into dataTable  
        } catch {}  
    }  
    return dtexcel;  
}

From above code you will able to get data from excel file and fill it in DataTable. Create Final DataTable add all datatables data to one table. Here i am assuming all files data are same then use Epplus dll to create excel file.

Sagar Jaybhay
https://sagarjaybhay.net

samwu

Contributor

3370 Points

1409 Posts

Re: How to read multiple excel files and export them into another excel by C#

Mar 03, 2020 05:46 AM|samwu|LINK

Hi ttprettycoder,

ttprettycoder
In my demo,i don't need to connect database,all my data source is my multiple excels.

If you don't want to use a database, you can try to use Gridview.

You need to use the OpenXml SDk.

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
<hr />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

protected void ImportExcel(object sender, EventArgs e)
{
    //Save the uploaded Excel file.
    string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(filePath);
 
    //Open the Excel file in Read Mode using OpenXml.
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
    {
        Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
        Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
        IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
        DataTable dt = new DataTable();
        foreach (Row row in rows)
        {
            if (row.RowIndex.Value == 1)
            {
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Columns.Add(GetValue(doc, cell));
                }
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (Cell cell in row.Descendants<Cell>())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
                    i++;
                }
            }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}
 
private string GetValue(SpreadsheetDocument doc, Cell cell)
{
    string value = cell.CellValue.InnerText;
    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
    {
        return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
    }
    return value;
}

More information about Read and Import Excel you can refer to this link: https://www.aspsnippets.com/Articles/Read-and-Import-Excel-without-using-OELDB-in-ASPNet-using-C-and-VBNet.aspx

Best regards,

Sam

.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.

- Reply
Freya009
None

0 Points

3 Posts

Re: How to read multiple excel files and export them into another excel by C#

Mar 03, 2020 06:14 PM|Freya009|LINK

Here we can export several tables into a single excel sheet.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

using Excel = Microsoft.Office.Interop.Excel;

namespace multiplesheets_export

{

    class Program

    {

    public static void Main(string[] args)

                {

                    object missing = Type.Missing;

                    SqlConnection con = new SqlConnection("Data Source=WINCTRL-KJ8RKFO;Initial Catalog=excel;Integrated Security=True");

                    SqlDataAdapter da = new SqlDataAdapter("select * from Employee", con);

                    SqlDataAdapter da1 = new SqlDataAdapter("select * from Department", con);

                    DataTable dt = new DataTable();

                    DataTable dt1 = new DataTable();

                    da.Fill(dt);

                    da1.Fill(dt1);

                    if (dt == null || dt.Columns.Count == 0)

                                                        throw new Exception("ExportToExcel: Null or empty input table!\n");

                    Excel.Application oXL = new Excel.Application();

                    Excel.Workbook oWB = oXL.Workbooks.Add(missing);

                    Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;

                    oSheet.Name = "Employee Details";

                    for (var i = 0; i < dt.Columns.Count; i++)

                    {

                        oSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;

                    }

                    for (var i = 0; i < dt.Rows.Count; i++)

                    {

                        for (var j = 0; j < dt.Columns.Count; j++)

                        {

                            oSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];

                        }

                    }

                    // From Here am taking EXCEL SHEET -2

                    Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing)as Excel.Worksheet;

                    if (dt1 == null || dt1.Columns.Count == 0)

                        throw new Exception("ExportToExcel: Null or empty input table!\n");

                    oSheet2.Name = "Depatment Details";

                    for (var i = 0; i < dt1.Columns.Count; i++)

                    {

                        oSheet2.Cells[1, i + 1] = dt1.Columns[i].ColumnName;

                    }

                    for (var i = 0; i < dt1.Rows.Count; i++)

                    {

                        for (var j = 0; j < dt1.Columns.Count; j++)

                        {

                            oSheet2.Cells[i + 2, j + 1] = dt1.Rows[i][j];

                        }

                    }

                    oXL.Visible = true;

                }

            }

        }
- Reply
ttprettycode...
Member

5 Points

20 Posts
Re: How to read multiple excel files and export them into another excel by C#

Mar 04, 2020 09:18 AM|ttprettycoder|LINK
HI samwu,

I try you code and it can successfully read and show the data from single excel file.

I want to read multiple excel files and export them into one excel.

when I add multiple="multiple" into the below code,like:
```
 <asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" multiple="multiple" />
```
it can't work,it still only allow upload single file.

So how to modify the code and how to merger the data from multiple excel files into one?

thanks very much!!!
- Reply
samwu
Contributor

3370 Points

1409 Posts
Re: How to read multiple excel files and export them into another excel by C#

Mar 05, 2020 02:58 AM|samwu|LINK
Hi ttprettycoder,
ttprettycoder

when I add multiple="multiple" into the below code,like:
```
 <asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" multiple="multiple" />
```
If you want to upload multiple files, you should set the AllowMultiple="true" in the FileUpload control.
```
<asp:FileUpload ID="FileUpload1" runat="server" AllowMultiple="true" />
```
Best regards,

Sam
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
- Reply
ttprettycode...
Member

5 Points

20 Posts
Re: How to read multiple excel files and export them into another excel by C#

Mar 05, 2020 01:18 PM|ttprettycoder|LINK
samwu
```
<asp:FileUpload ID="FileUpload1" runat="server" AllowMultiple="true" />
```
thanks for your answer.

And how to modify the code and how to merger the data from multiple excel files into one?
- Reply
samwu
Contributor

3370 Points

1409 Posts

Re: How to read multiple excel files and export them into another excel by C#

Mar 09, 2020 02:26 AM|samwu|LINK

Hi ttprettycoder,

ttprettycoder
And how to modify the code

You only need to add AllowMultiple = "true" attribute to the fileupload control to upload multiple files at once.

ttprettycoder
how to merger the data from multiple excel files into one

How do you want to merge them into the same Excel? Is all the data in the same sheet?

Best regards,

Sam

.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
- Reply
Leon Davis
Member

50 Points

37 Posts
Re: How to read multiple excel files and export them into another excel by C#

Mar 12, 2020 07:52 AM|Leon Davis|LINK
Hi,

You can try free spire.xls to merge excel files into one without connecting to database.

First, install free spire.xls via nuget. Second, try below code to merge mutiple excel files into a single excel file in c#, remember to add needed namespaces.
```
Workbook newbook = new Workbook();
newbook.Version = ExcelVersion.Version2013;
newbook.Worksheets.Clear();

Workbook tempbook = new Workbook();
string[] excelFiles = new String[] { "sample1.xlsx", "sample2.xlsx", "sample3.xlsx" };
for (int i = 0; i < excelFiles.Length; i++)
{
    tempbook.LoadFromFile(excelFiles[i]);
    foreach (Worksheet sheet in tempbook.Worksheets)
    {
        newbook.Worksheets.AddCopy(sheet);
    }
}

newbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);
```
For more info check this link. Apart from merging to a single excel file, you can also merge excel files into a single worksheet by simply invoking Worksheet.ExportDataTable and Worksheet.InsertDataTable methods.

Hope it helps.

‹ Previous Thread|Next Thread ›