将多个EXCEL 合并一个EXCEL多个sheet-Toy模板网

这篇具有很好参考价值的文章主要介绍了将多个EXCEL 合并一个EXCEL多个sheet。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

合并老版本xls

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;

namespace Merge_Excel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{

bool Result;
string[] FileNames;
FileNames = GetFileNames();
if (FileNames == null) { return; }
var fullWork = new HSSFWorkbook();
// 遍历读取每一个订单文件
foreach (string FileName in FileNames)
{
string sheetName = Path.GetFileNameWithoutExtension(FileName);//获取sheet名称
var excelStream = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var workbook = new HSSFWorkbook(excelStream);//读取excel文件
HSSFSheet sheet = workbook.GetSheetAt(0) as HSSFSheet; //获取第一个工作表（sheet）
sheet.CopyTo(fullWork, sheetName, true, true);//将报表合并至综合报表中
excelStream.Close();
}

using (FileStream filestream = new FileStream(Application.StartupPath + "合并" + System.DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls", FileMode.Create))
{
fullWork.Write(filestream);
filestream.Close();
}
fullWork.Dispose();
fullWork.Close();
fullWork = null;
GC.Collect();
MessageBox.Show("合并完成");
}

public string[] GetFileNames()
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "所有文件|*.*|office2007及高版本|*.xlsx|office2003及老版本|*.xls";
ofd.Multiselect = true;
if (ofd.ShowDialog() == DialogResult.OK)
{
if (ofd.FileNames != null)
{
return ofd.FileNames;
}
}
return null;
}
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
var fullWork = new HSSFWorkbook();//最终的合并excel
//string exportReports = hidExportReports.Value;//需要合并的Excel文件路径
string exportReports = "";
if (!string.IsNullOrEmpty(exportReports))
{
var reportArrary = exportReports.Split(',');
try
{
for (int i = 0; i < reportArrary.Length; i++)
{
string sheetName = Path.GetFileNameWithoutExtension(reportArrary[i]).Split('_')[1];//获取sheet名称
var excelStream = new FileStream(reportArrary[i], FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var workbook = new HSSFWorkbook(excelStream);//读取excel文件
HSSFSheet sheet = workbook.GetSheetAt(0) as HSSFSheet; //获取第一个工作表（sheet）
sheet.CopyTo(fullWork, sheetName, true, true);//将报表合并至综合报表中
}
var stream = new MemoryStream();
fullWork.Write(stream);
byte[] bytes = stream.ToArray();
// Now that you have all the bytes representing the PDF report, buffer it and send it to the client.
//Response.Buffer = true;
//Response.Clear();
//Response.ContentType = "application/vnd.ms-excel";
//String filename = HttpUtility.UrlEncode("综合报表", Encoding.UTF8);
//Response.AddHeader("content-disposition", "attachment; filename=" + filename + ".xls");
//Response.BinaryWrite(bytes); // create the file
//Response.Flush(); // send it to the client to download

using (FileStream filestream = new FileStream(System.IO.Path.Combine(@"D:\", "liumang.xlsx"), FileMode.Create)) { fullWork.Write(filestream); filestream.Close(); }

MessageBox.Show("导出完成");

}
catch
{

}
finally
{
//删除excel文件
for (int i = 0; i < reportArrary.Length; i++)
{
File.Delete(reportArrary[i]);
}
}

}
}

合并新版本xlsx

private void button1_Click(object sender, EventArgs e)
{

bool Result;
string[] FileNames;
FileNames = GetFileNames();
if (FileNames == null) { return; }
var fullWork = new XSSFWorkbook();
// 遍历读取每一个订单文件
foreach (string FileName in FileNames)
{
string sheetName = Path.GetFileNameWithoutExtension(FileName);//获取sheet名称
var excelStream = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var workbook = new XSSFWorkbook(excelStream);//读取excel文件
XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet; //获取第一个工作表（sheet）
sheet.CopyTo(fullWork, sheetName, true, true);//将报表合并至综合报表中
excelStream.Close();
}

using (FileStream filestream = new FileStream(Application.StartupPath + "合并" + System.DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx", FileMode.Create))
{
fullWork.Write(filestream);
filestream.Close();
}
fullWork.Dispose();
fullWork.Close();
fullWork = null;
GC.Collect();
MessageBox.Show("合并完成");
}文章来源地址https://www.toymoban.com/news/detail-693731.html

到了这里，关于将多个EXCEL 合并一个EXCEL多个sheet的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！