NPOI读取Excel笔记总结

NPOI读取Excel文件

protected DataTable getExcelData(string FilePath, string sheetName)//NPOI读取Excel,不会出现日期错误
    {
        using (Stream stream = File.OpenRead(FilePath))
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
            ISheet sheet = hssfworkbook.GetSheet(sheetName);
            // 获取 sheet 的首行
            IRow rowHeader = sheet.GetRow(0);
            DataTable table = new DataTable();

            // 一行最后一个方格的编号 即总的列数
            int cellCount = rowHeader.LastCellNum;
            for (int i = rowHeader.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(rowHeader.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            // 最后一列的标号  即总的行数
            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ICell cell = row.GetCell(j);
                    if (cell != null && cell.CellType!=CellType.BLANK)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.NUMERIC:
                                // Date Type的数据CellType是Numeric
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    //dataRow[j] = cell.DateCellValue;//日期直接读文本
                                    DateTime dt = cell.DateCellValue;
                                    dataRow[j] = dt.ToString("yyyy/MM/dd");
                                }
                                else
                                {
                                    // Numeric type
                                    dataRow[j] = cell.NumericCellValue;
                                }
                                break;
                            case CellType.FORMULA://公式处理
                                try
                                {
                                    if (cell.CachedFormulaResultType == CellType.STRING)
                                    {
                                        dataRow[j] = cell.StringCellValue;
                                    }
                                    else if (cell.CachedFormulaResultType == CellType.NUMERIC)
                                    {
                                        // Date Type的数据CellType是Numeric
                                        if (DateUtil.IsCellDateFormatted(cell))
                                        {
                                            //dataRow[j] = cell.DateCellValue;//日期直接读文本
                                            DateTime dt = cell.DateCellValue;
                                            dataRow[j] = dt.ToString("yyyy/MM/dd");
                                        }
                                        else
                                        {
                                            // Numeric type
                                            dataRow[j] = cell.NumericCellValue;
                                        }
                                        //dataRow[j] = cell.NumericCellValue;
                                        //File.AppendAllText("c:\\1.txt", rowHeader.GetCell(j).StringCellValue.ToString() + "\n"); 
                                    }
                                    else
                                    {
                                        cell.SetCellType(CellType.STRING);
                                        dataRow[j] = cell.ToString();
                                    }
                                }
                                catch
                                {
                                    cell.SetCellValue("");
                                    cell.SetCellType(CellType.STRING);
                                    dataRow[j] = cell.ToString();
                                }
                                break;
                            default:
                                // String type
                                dataRow[j] = cell.StringCellValue;
                                break;
                        }
                    }
                }
                table.Rows.Add(dataRow);
            }
            return table;
        }
    }

http://www.cnblogs.com/lwme/archive/2011/11/19/2254700.html

http://www.bianceng.cn/Programming/csharp/201410/45750.htm

样式相关http://www.tuicool.com/articles/eMNzyq

样式http://www.tuicool.com/articles/eMNzyq