C#操作Excelについて、Sheetのレコードをコピー

1.まずNPOIを使って、やります.HSSFWorkbookにはCopyの方法がありますが、これはoffice 2003しかサポートされていません.
対応する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);

        }

    }

}