Copying Excel worksheets with NPOI CopySheet; copy is always blank
I'm trying to create an application that will merge the excel spreadsheets in a given directory into a single, tabbed Excel workbook. I only have access to VS Community 2015, so VSTO is out of the question; I'm using Interop and NPOI 2.1.3.1 instead.
Here's my code:
files = Directory.GetFiles(sourcePath);
XSSFWorkbook test = new XSSFWorkbook();
try
{
string fPath = files[0];
FileStream fs2 = new FileStream(fPath, FileMode.Open, FileAccess.ReadWrite);
XSSFWorkbook myBook = new XSSFWorkbook(fs2);
test.CreateSheet(myBook.GetSheetName(0));
var sheet1 = myBook.GetSheetAt(0).CopySheet(test.GetSheetName(0));
test.Write(new FileStream("testFile.xlsx", FileMode.Create, FileAccess.ReadWrite));
}
catch { }
I'm not trying to do anything fancy, here, just get the first file returned by the GetFiles function, copy everything on the first worksheet, and create a new worksheet in new workbook "test" with what was copied. I know the source file is being found, because sheet1 of the output file "testFile.xlsx" is getting the unique sheet name of the source worksheet -- it isn't "Sheet1". However, the rest of the sheet comes up blank, and I don't know why.
I'm using this against Excel 2016, if that makes a difference.
Please try the two different solutions: one for Interop (xlsx file format) and the other for NPOI (xls file format). These solutions get the excel files in a folder, read the excel files and copy the sheets to a new excel file.
Using Interop.Excel:
// using Microsoft.Office.Interop.Excel;
Application app = new Application();
app.Visible = false;
app.DisplayAlerts = false;
string[] files = Directory.GetFiles(@"c:tempexcel");
foreach (string file in files)
{
app.Workbooks.Add(file);
}
for (int i = 2; i <= app.Workbooks.Count; i++)
{
for (int j = 1; j <= app.Workbooks[i].Worksheets.Count; j++)
{
Worksheet ws = app.Workbooks[i].Worksheets[j];
ws.Copy(app.Workbooks[1].Worksheets[1]);
}
}
app.Workbooks[1].SaveCopyAs(@"c:tempexceloutputtestFile.xlsx");
app.Quit();
Using NPOI:
// using NPOI.HSSF.UserModel;
string[] files = Directory.GetFiles(@"c:tempexcel");
HSSFWorkbook workbookMerged = new HSSFWorkbook();
foreach (string file in files)
{
HSSFWorkbook workbook;
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(fs);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
((HSSFSheet)workbook.GetSheetAt(i)).CopyTo(workbookMerged, workbook.GetSheetName(i), true, true);
}
}
}
using (FileStream fs = new FileStream(@"c:tempexceloutputtestFile.xls", FileMode.Append, FileAccess.Write))
{
workbookMerged.Write(fs);
}
Note that for the NPOI solution, I converted my xlsx files to xls to use HSSF instead of XSSF, as there's not CopyTo method available for XSSFSheet . This method is only available for HSSFSheet , as of the latest NPOI version 2.3.0.
上一篇: 免费的CUSIP查找API