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

Question

User10778361 posted

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

Answer 1

User-1917203956 posted

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.

Answer 2

User288213138 posted

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

Answer 3

User379996595 posted

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;

                }

            }

        }

Answer 4

User10778361 posted

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!!!

Answer 5

User288213138 posted

Hi 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

Answer 6

User10778361 posted

<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?

Answer 7

User288213138 posted

Hi 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.

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

Answer 8

User426857743 posted

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.