NPOI讀取Excel官方demo

關鍵程式碼： 
讀：    

   HSSFWorkbook hssfworkbook;

        void InitializeWorkbook(string path)
        {
            //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
            //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added. 
            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
        }

        void ConvertToDataTable()
        {
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();
            for (int j = 0; j < 5; j++)
            {
                dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString());
            }

            while (rows.MoveNext())
            {
                IRow row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);


                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
            }
            dataSet1.Tables.Add(dt);
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            InitializeWorkbook(@"xls\Book1.xls");
            ConvertToDataTable();

            dataGridView1.DataSource = dataSet1.Tables[0];
        }

 

寫：

//按行列匯出資料：
HSSFWorkbook hssfworkbook = new HSSFWorkbook();　　//名稱空間：using NPOI.HSSF.UserModel;
Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1");　　//名稱空間：using NPOI.SS.UserModel;
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");　　//在第一行，第一列新增一個值
sheet1.CreateRow(1).CreateCell(0).SetCellValue("This is a test");
Row row = sheet1.CreateRow(2);　　//建立第三行。
row.CreateCell(1).SetCellValue("test");　　//在第三行第一列加入值　　這兩行效果和上面那兩行是一樣的。建立多行用Row程式碼清晰
sheet1.AutoSizeColumn(0);　　//會按照值的長短 自動調節列的大小
sheet1.AutoSizeColumn(1);
using (FileStream file = new FileStream(@"test.xls", FileMode.Create))
{
hssfworkbook.Write(file);　　//建立test.xls檔案。
file.Close();
}

補充：
1.

dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString());
DataTable如果不建立DataColumn會報錯：無法找到列 0。  NPOI.HSSF.UserModel.HSSFCell.ToString

2.
NPOI的行和列的index都是從0開始
3、

POI讀取Excel有兩種格式一個是HSSF，另一個是XSSF。本文主要是利用HSSF讀取Excel
先說下HSSF和XSSF的區別
官方給出的解釋如下：
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.
XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
即：HSSF適用2007及以前的版本,XSSF適用2007版本以上的

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
HSSF and XSSF provides ways to read spreadsheets create, modify, read and write XLS spreadsheets. They provide:

low level structures for those with special needs
an eventmodel api for efficient read-only access
a full usermodel api for creating, reading and modifying XLS files

 

表頭格式：

    protected CellStyle defaultHeaderStyle(Workbook wb) {
        Font headFont = wb.createFont();
        headFont.setBold(true);
        headFont.setFontName("宋體");
        headFont.setFontHeightInPoints((short) 11);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFont(headFont);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        return cellStyle;
    }

 

 

    protected void setRow(Workbook workbook) {
        Sheet sheet= sheet = workbook.createSheet();

        sheet.setDefaultColumnWidth(30);
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 20 * 256);

        Row header = sheet.createRow(0);
        CellStyle style = defaultHeaderStyle(workbook);
        setHeadColumn(header, 0, "活動ID", style);
        setHeadColumn(header, 1, "獎品名稱", style);

        // 定義Cell格式
        CreationHelper creationHelper = workbook.getCreationHelper();
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd  hh:mm:ss"));
      
        Row userRow = sheet.createRow(1);
        userRow.createCell(0).setCellValue(1);
           
        Cell dateCell = userRow.createCell(1);
        dateCell.setCellValue(new Date());
        dateCell.setCellStyle(cellStyle);
    }

    private void setHeadColumn(Row header, int column, String cellValue, CellStyle style) {
        Cell cell0 = header.createCell(column);
        cell0.setCellStyle(style);
        cell0.setCellValue(cellValue);
    }