关于C#操作Excel,复制Sheet的记录

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› C++›C++教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

1.先用了NPOI，去做，HSSFWorkbook 里面有一个Copy方法，但这个只支持office2003。

对应的XSSFWorkbook没有些方法。

而且这个这个方法对devexpress导出的2003的excel文件读取不了，出现异常，需要用excel打开后，另存一下才行。

var fs = new FileStream("c://pivotGrid.xls", FileMode.Open, FileAccess.Read);
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            var sheet = workbook.GetSheetAt(0) as HSSFSheet;

            var fs2 = new FileStream("c://test3.xls", FileMode.Create);
            var workbook2 = new HSSFWorkbook();
            sheet.CopyTo(workbook2, "a", true, true);


            workbook2.Write(fs2);
            fs2.Close();

网上有些人对POI写过类似的方法：

http://blog.csdn.net/wutbiao/article/details/8696446

public class POIUtils {
//    /**
//     * 把一个excel中的cellstyletable复制到另一个excel，这里会报错，不能用这种方法，不明白呀？？？？？
//     * @param fromBook
//     * @param toBook
//     */
//    public static void copyBookCellStyle(HSSFWorkbook fromBook,HSSFWorkbook toBook){
//        for(short i=0;i<fromBook.getNumCellStyles();i++){
//            HSSFCellStyle fromStyle=fromBook.getCellStyleAt(i);
//            HSSFCellStyle toStyle=toBook.getCellStyleAt(i);
//            if(toStyle==null){
//                toStyle=toBook.createCellStyle();
//            }
//            copyCellStyle(fromStyle,toStyle);
//        }
//    }
    /**
     * 复制一个单元格样式到目的单元格样式
     * @param fromStyle
     * @param toStyle
     */
    public static void copyCellStyle(HSSFCellStyle fromStyle,
            HSSFCellStyle toStyle) {
        toStyle.setAlignment(fromStyle.getAlignment());
        //边框和边框颜色
        toStyle.setBorderBottom(fromStyle.getBorderBottom());
        toStyle.setBorderLeft(fromStyle.getBorderLeft());
        toStyle.setBorderRight(fromStyle.getBorderRight());
        toStyle.setBorderTop(fromStyle.getBorderTop());
        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
        
        //背景和前景
        toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
        toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
        
        toStyle.setDataFormat(fromStyle.getDataFormat());
        toStyle.setFillPattern(fromStyle.getFillPattern());
//        toStyle.setFont(fromStyle.getFont(null));
        toStyle.setHidden(fromStyle.getHidden());
        toStyle.setIndention(fromStyle.getIndention());//首行缩进
        toStyle.setLocked(fromStyle.getLocked());
        toStyle.setRotation(fromStyle.getRotation());//旋转
        toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
        toStyle.setWrapText(fromStyle.getWrapText());
        
    }
    /**
     * Sheet复制
     * @param fromSheet
     * @param toSheet
     * @param copyValueFlag
     */
    public static void copySheet(HSSFWorkbook wb,HSSFSheet fromSheet, HSSFSheet toSheet,
            boolean copyValueFlag) {
        //合并区域处理
        mergerRegion(fromSheet, toSheet);
        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
            HSSFRow tmpRow = (HSSFRow) rowIt.next();
            HSSFRow newRow = toSheet.createRow(tmpRow.getRowNum());
            //行复制
            copyRow(wb,tmpRow,newRow,copyValueFlag);
        }
    }
    /**
     * 行复制功能
     * @param fromRow
     * @param toRow
     */
    public static void copyRow(HSSFWorkbook wb,HSSFRow fromRow,HSSFRow toRow,boolean copyValueFlag){
        for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
            HSSFCell tmpCell = (HSSFCell) cellIt.next();
            HSSFCell newCell = toRow.createCell(tmpCell.getCellNum());
            copyCell(wb,tmpCell, newCell, copyValueFlag);
        }
    }
    /**
    * 复制原有sheet的合并单元格到新创建的sheet
    * 
    * @param sheetCreat 新创建sheet
    * @param sheet      原有的sheet
    */
    public static void mergerRegion(HSSFSheet fromSheet, HSSFSheet toSheet) {
       int sheetMergerCount = fromSheet.getNumMergedRegions();
       for (int i = 0; i < sheetMergerCount; i++) {
        Region mergedRegionAt = fromSheet.getMergedRegionAt(i);
        toSheet.addMergedRegion(mergedRegionAt);
       }
    }
    /**
     * 复制单元格
     * 
     * @param srcCell
     * @param distCell
     * @param copyValueFlag
     *            true则连同cell的内容一起复制
     */
    public static void copyCell(HSSFWorkbook wb,HSSFCell srcCell, HSSFCell distCell,
            boolean copyValueFlag) {
        HSSFCellStyle newstyle=wb.createCellStyle();
        copyCellStyle(srcCell.getCellStyle(), newstyle);
        distCell.setEncoding(srcCell.getEncoding());
        //样式
        distCell.setCellStyle(newstyle);
        //评论
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }
        // 不同数据类型处理
        int srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);
        if (copyValueFlag) {
            if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
                // nothing21
            } else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
                distCell.setCellFormula(srcCell.getCellFormula());
            } else { // nothing29
            }
        }
    }
}

2.用微软的API实现复制：

using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace UnitTestProject1
{
    [TestClass]
    public class UnitTestExcel
    {
        [TestMethod]
        public void TestMethod1()
        {

            Excel.Application app = new Excel.Application();


            Excel.Workbook workbook1 = app.Workbooks._Open("C:\\a.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing
                 , Type.Missing, Type.Missing, Type.Missing, Type.Missing
                 , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            Excel.Worksheet sheet1 = workbook1.Worksheets["Sheet1"] as Excel.Worksheet;

            Excel.Workbook workbook2 = app.Workbooks._Open("C:\\a1.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            Excel.Worksheet sheet2 = workbook2.Worksheets["Sheet1"] as Excel.Worksheet;

            sheet2.Copy(Type.Missing, sheet1);

            workbook1.Save();
            workbook1.Close(false, Type.Missing, Type.Missing);
            workbook2.Close(false, Type.Missing, Type.Missing);
        }
    }
}