Merging Multiple Excel Files Into Single Excel File Using NPOI

Blog

Multiple Excel file Merging.zip

After lots of searching I developed this application merging multiple Excel files into single Excel files for .Xls extensions.

Suppose, An Excel file ‘A’ has 3sheets (SheetA1,SheetA2,SheetA3) and another excel file ‘B’ has 2sheets (SheetB1,sheetB2) now we want to merge both Excel files into a single Excel file with all 5 sheets like (SheetA1,SheetA2,SheetA3,SheetB1,SheetB2)

Step 1

Download the NPOI dlls from https://npoi.codeplex.com/releases

Download the Source file which I provided there you will find the Reference folder having related NPOI dlls.

Step 2

Create Design page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>::EXCEL MERGE::</title>
<style>
td {
padding: 10px;
}
</style>
</head>
<body style="font-family:Tahoma; background-color:#c5d6ff">
<form id="form1" runat="server">
<%--<div>
<asp:Button ID="btnNPOI" runat="server" Text="click NPOI" OnClick="btnNPOI_Click" />
</div>
<div>
<asp:LinkButton ID="lbtndwnld" runat="server" OnClick="lbtndwnld_Click" Text="Download"></asp:LinkButton>
</div>--%>
<div style="width:100%" align="center">
<table width="40%" style="background-color:#f2f2f2;padding:0px; margin:0px;">
<tr>
<td colspan="3" style="background-color:#1d1d1d; color:#c5d6ff; padding:10px;" align="center"> <label style="font-size:1.5em; font-weight:bold">Excel File Merging</label></td>
</tr>
<tr>
<td colspan="3" style="padding:10px;"> </td>
</tr>
<tr>
<td width="30%"> Choose Files </td>
<td> : </td>
<td width="70%" align="left">
<asp:FileUpload ID="Fuploads" Multiple="Multiple" runat="server" /> </td>
</tr>
<tr>
<td colspan="3" align="center">
<asp:Panel ID="pnlbody" runat="server" Width="100%">
<asp:GridView ID="grvfileslist" runat="server" AutoGenerateColumns="False" GridLines="None" DataKeyNames="ID" CellPadding="0" BackColor="White" Font-Size="Small" Font-Underline="False" ForeColor="#3333CC" HorizontalAlign="Left" Width="100%" OnRowDeleting="grvfileslist_RowDeleting">
<Columns>
<asp:BoundField DataField="fileName" HeaderStyle-Font-Bold="true" ItemStyle-Width="100%" HeaderText=" List of Files">
<HeaderStyle Font-Bold="True" BackColor="#FFFFCC" Font-Size="Medium" Font-Underline="False" ForeColor="#CC0000" BorderStyle="None" Font-Strikeout="False" Height="30px" HorizontalAlign="Left" VerticalAlign="Middle"></HeaderStyle>
<ItemStyle Width="100%" Font-Underline="True" BorderStyle="None"></ItemStyle>
</asp:BoundField>
<asp:TemplateField ShowHeader="False" Visible="true">
<ItemTemplate>
<asp:ImageButton ID="btnDel" CommandName="Delete" runat="server" ImageUrl="~/Excel/DeleteRed.png" CausesValidation="false" Visible="true" /> </ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:LinkButton ID="lbtdownload" runat="server" OnClick="lbtdownload_Click" Visible="false">Download!</asp:LinkButton> <br /><br /><br />
<asp:Button ID="btnrunAgain" runat="server" Visible="false" Text="Merge Again" OnClick="btnrunAgain_Click" /> </asp:Panel>
</td>
</tr>
<tr>
<td colspan="3" align="right">
<div style="padding:5px; margin:5px; float:left;" id="divop1" runat="server">
<asp:CheckBox id="chkop1" runat="server" Text="" OnCheckedChanged="chkop1_CheckedChanged" Checked="true" AutoPostBack="true" />
<asp:Button runat="server" ID="btnUpload" Text="Upload Files" OnClick="btnUpload_Click" Enabled="false" />
<asp:Button runat="server" ID="btnMerge" Text="Merge Files" OnClick="btnMerge_Click" Enabled="false" /> </div>
<div style="padding:5px; margin:5px; float:left" id="divop2" runat="server">
<asp:CheckBox id="chkop2" runat="server" Text="" OnCheckedChanged="chkop2_CheckedChanged" Checked="false" AutoPostBack="true" />
<asp:Button ID="btnclick" Text="Upload & Merge" runat="server" OnClick="btnclick_Click" Enabled="false" />
<asp:Button ID="btnclear" runat="server" Text="Clear" OnClick="btnclear_Click" /> </div>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>

Step 2

Browse Multiple Excel files from FileUpload and Upload. Save all the paths in List<string> and display list of uploaded files in Gridview

public class fileList {
public string fileName {
get;
set;
}
public int ID {
get;
set;
}
public string path {
get;
set;
}
}
bool uploadFiles() {
bool hasFiles = false;
if (Fuploads.HasFile) {
hasFiles = true;
if (SourceFilePaths == null) {
HttpFileCollection flImages = Request.Files;
SourceFilePaths = new List < string > ();
lstFiles = new List < fileList > ();
for (int x = 0; x < flImages.Keys.Count; x++) {
HttpPostedFile flfile = flImages[x];
flfile.SaveAs(Server.MapPath(uploadPath) + flfile.FileName);
SourceFilePaths.Add(Server.MapPath(uploadPath) + flfile.FileName);
Session["SourceFilepaths"] = SourceFilePaths;
obj = new fileList();
obj.fileName = flfile.FileName;
obj.path = Server.MapPath(uploadPath) + flfile.FileName;
obj.ID = x;
lstFiles.Add(obj);
Session["lstFiles"] = lstFiles;
}
fillGrid();
btnMerge.Enabled = true;
} else if (SourceFilePaths.Count > 0) {
HttpFileCollection flImages = Request.Files;
for (int x = 0; x < flImages.Keys.Count; x++) {
HttpPostedFile flfile = flImages[x];
flfile.SaveAs(Server.MapPath(uploadPath) + flfile.FileName);
SourceFilePaths.Add(Server.MapPath(uploadPath) + flfile.FileName);
Session["SourceFilepaths"] = SourceFilePaths;
obj = new fileList();
obj.fileName = flfile.FileName;
obj.path = Server.MapPath(uploadPath) + flfile.FileName;
obj.ID = lstFiles.Count + 1;
lstFiles.Add(obj);
Session["lstFiles"] = lstFiles;
}
SourceFilePaths = (List < string > ) Session["SourceFilepaths"];
lstFiles = (List < fileList > ) Session["lstFiles"];
fillGrid();
btnMerge.Enabled = true;
}
}
return hasFiles;
}
void fillGrid() {
grvfileslist.DataSource = lstFiles;
grvfileslist.DataBind();
}

Step 3

If you want to remove the file from the list click delete icon for this write the code in gridview_RowDeleting event.

protected void grvfileslist_RowDeleting(object sender, GridViewDeleteEventArgs e) {
((List < string > ) Session["SourceFilepaths"]).RemoveAt(e.RowIndex);
((List < fileList > ) Session["lstFiles"]).RemoveAt(e.RowIndex);
grvfileslist.DataSource = ((List < fileList > ) Session["lstFiles"]);
grvfileslist.DataBind();
}

Step 4

Then Merge button will enable; click the Merge button to merge listed multiple Excel files into single ‘Result.xls’ file, but before this create a Results Folder and create an empty Result.xls file in Results Folder.

protected void btnMerge_Click(object sender, EventArgs e) {
SourceFilePaths = (List < string > ) Session["SourceFilepaths"];
DoMerge(SourceFilePaths);
Clear();
DeleteupFiles();
lbtdownload.Visible = true;
btnrunAgain.Visible = true;
divop1.Visible = false;
divop2.Visible = false;
}
void DoMerge(List < string > _sourceFiles) {
bool b = false;
int i = 0;
foreach(string strFile in _sourceFiles) {
i = i + 1;
NPOICOPY(strFile, i);
}
}
HSSFWorkbook product = new HSSFWorkbook();
void NPOICOPY(string filename, int X) {
byte[] byteArray = File.ReadAllBytes(filename);
using(MemoryStream stream = new MemoryStream()) {
stream.Write(byteArray, 0, (int) byteArray.Length);
HSSFWorkbook book1 = new HSSFWorkbook(stream);
if (X == 1) {
product = new HSSFWorkbook();
}
for (int i = 0; i < book1.NumberOfSheets; i++) {
HSSFSheet sheet1 = book1.GetSheetAt(i) as HSSFSheet;
sheet1.CopyTo(product, sheet1.SheetName, true, true);
}
using(FileStream fs = new FileStream(Server.MapPath(@ "\Results\Result.xls"), FileMode.Create, FileAccess.Write)) {
product.Write(fs);
}
}
}
void Clear() {
chkop1.Checked = true;
chkop2.Checked = false;
btnclick.Enabled = false;
btnUpload.Enabled = true;
btnMerge.Enabled = false;
btnUpload.Text = "Upload Files";
grvfileslist.DataSource = null;
grvfileslist.DataBind();
Session["SourceFilepaths"] = null;
Session["lstFiles"] = null;
lbtdownload.Visible = false;
btnrunAgain.Visible = false;
divop1.Visible = true;
divop2.Visible = true;
}
public void DeleteupFiles() {
System.IO.DirectoryInfo di = new DirectoryInfo(Server.MapPath(uploadPath));
foreach(FileInfo file in di.GetFiles()) {
if (file.Name != "Result.xls") {
file.Delete();
}
}
foreach(DirectoryInfo dir in di.GetDirectories()) {
dir.Delete(true);
}
}

Step 5

If Merge has no errors download link will appear click the download button to download the result file.

protected void lbtdownload_Click(object sender, EventArgs e) {
download(@ "\Results\Result.xls");
}
public void download(string destFile) {
string filePath = Server.MapPath(destFile);
Response.ContentType = ContentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(filePath));
Response.WriteFile(filePath);
Response.End();
}

If you want to upload & merge directly all at one time just write the code below

protected void btnclick_Click(object sender, EventArgs e) {
SourceFilePaths = null;
if (uploadFiles()) {
DoMerge(SourceFilePaths);
Clear();
DeleteupFiles();
lbtdownload.Visible = true;
btnrunAgain.Visible = true;
divop1.Visible = false;
divop2.Visible = false;
} else {
TestWebMsgApp.WebMsgBox.Show("No Files to Merge. Please select the files to Upload & Merge !");
}
}