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);
    }