/// <summary>
/// 复制sheet
/// </summary>
/// <param name="bjDt">sheet名集合</param>
/// <param name="modelfilename">模板附件名</param>
/// <param name="tpath">生成文件路径</param>
/// <returns></returns>
public static HSSFWorkbook SheetCopy(DataTable bjDt, string templetfilepath, string tpath)
{
FileInfo ff = new FileInfo(tpath);
if (ff.Exists)
{
ff.Delete();
}
FileStream fs = File.Create(tpath);//创建中间excel
HSSFWorkbook x1 = new HSSFWorkbook();
x1.Write(fs);
fs.Close();
FileStream fileRead = new FileStream(templetfilepath, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileRead);
FileStream fileSave2 = new FileStream(tpath, FileMode.Open, FileAccess.Read);
HSSFWorkbook book2 = new HSSFWorkbook(fileSave2);
HSSFWorkbook[] book = new HSSFWorkbook[2] {
book2, hssfworkbook
};
HSSFSheet CPS = hssfworkbook.GetSheet("Sheet1") as HSSFSheet;//获得模板sheet
string rsbh = bjDt.Rows[0]["name"].ToString();
CPS.CopyTo(book2, rsbh, true, true); //将模板sheet复制到目标sheet
HSSFSheet sheet = book2.GetSheet(bjDt.Rows[0]["name"].ToString()) as HSSFSheet; //获得当前sheet
for (int i = 1; i < bjDt.Rows.Count; i++)
{
sheet.CopySheet(bjDt.Rows[i]["name"].ToString(), true);//将sheet复制到同一excel的其他sheet上
}
return(book2);
}
public void TestBasicCopySheet()
{
HSSFWorkbook book = new HSSFWorkbook();
HSSFSheet sheetA = book.CreateSheet("Sheet A") as HSSFSheet;
sheetA.CreateRow(0).CreateCell(0).SetCellValue("Test case item 1");
sheetA.CreateRow(1).CreateCell(0).SetCellValue("Test case item 2");
ISheet sheetB = sheetA.CopySheet("Sheet B", false);
//Ensure cell values were copied
Assert.AreEqual(sheetA.GetRow(0).GetCell(0).StringCellValue, sheetB.GetRow(0).GetCell(0).StringCellValue);
Assert.AreEqual(sheetA.GetRow(1).GetCell(0).StringCellValue, sheetB.GetRow(1).GetCell(0).StringCellValue);
//Now test to make sure the copy is independent. Changes to the copy should not affect the original.
sheetB.GetRow(1).GetCell(0).SetCellValue("This was changed");
Assert.AreNotEqual(sheetA.GetRow(1).GetCell(0).StringCellValue, sheetB.GetRow(1).GetCell(0).StringCellValue);
}