NPOI读取Excel2003,2007
2024-08-30 14:54:47
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel; namespace DMEnterpriseAdministration
{
public class Excel
{
public static MemoryStream WriteDataToExcel(DataSet ds)
{
MemoryStream memoryStream = new MemoryStream(); try
{
IWorkbook workbook = new HSSFWorkbook(); foreach (DataTable table in ds.Tables)
{
ISheet sheet = workbook.CreateSheet(table.TableName);
IRow headerRow = sheet.CreateRow(); foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
} int rowIndex = ; foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
} rowIndex++;
} sheet = null;
headerRow = null;
} workbook.Write(memoryStream); workbook = null;
}
catch (Exception exception)
{
throw exception;
} return memoryStream;
} public static DataSet ExcelToDataSet(string excelPath)
{
return ExcelToDataSet(excelPath, true);
} public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader)
{
int sheetCount;
return ExcelToDataSet(excelPath, firstRowAsHeader, out sheetCount);
} public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader, out int sheetCount)
{
using (DataSet ds = new DataSet())
{
using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = new HSSFWorkbook(fileStream); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook); sheetCount = workbook.NumberOfSheets; for (int i = ; i < sheetCount; ++i)
{
ISheet sheet = workbook.GetSheetAt(i) as HSSFSheet;
DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
ds.Tables.Add(dt);
} return ds;
}
}
} public static DataTable ExcelToDataTable(string excelPath, string sheetName)
{
return ExcelToDataTable(excelPath, sheetName, true);
} public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader)
{
using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = null;
IFormulaEvaluator evaluator = null;
ISheet sheet = null;
if (excelPath.EndsWith(".xls"))
{
workbook = new HSSFWorkbook(fileStream);
evaluator = new HSSFFormulaEvaluator(workbook);
sheet = workbook.GetSheet(sheetName) as HSSFSheet;
if (sheet == null)
{
sheet = workbook.GetSheetAt();
}
}
else
{
workbook = new XSSFWorkbook(fileStream);
evaluator = new XSSFFormulaEvaluator(workbook);
sheet = workbook.GetSheet(sheetName) as XSSFSheet;
if (sheet == null)
{
sheet = workbook.GetSheetAt();
}
} return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
}
} private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator, bool firstRowAsHeader)
{
if (firstRowAsHeader)
{
return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
}
else
{
return ExcelToDataTable(sheet, evaluator);
}
} private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
IRow firstRow = sheet.GetRow() as IRow;
int cellCount = GetCellCount(sheet); for (int i = ; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + ), typeof(string));
}
else
{
dt.Columns.Add(string.Format("F{0}", i + ), typeof(string));
}
} for (int i = ; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i) as IRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
} dt.TableName = sheet.SheetName;
return dt;
}
} private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
if (sheet.LastRowNum != )
{
int cellCount = GetCellCount(sheet); for (int i = ; i < cellCount; i++)
{
dt.Columns.Add(string.Format("F{0}", i), typeof(string));
} for (int i = ; i < sheet.FirstRowNum; ++i)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
} for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i) as IRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
} dt.TableName = sheet.SheetName;
return dt;
}
} private static void FillDataRowByHSSFRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr)
{
if (row != null)
{
for (int j = ; j < dr.Table.Columns.Count; j++)
{
ICell cell = row.GetCell(j) as ICell; if (cell != null)
{
switch (cell.CellType)
{
case CellType.Blank:
dr[j] = DBNull.Value;
break;
case CellType.Boolean:
dr[j] = cell.BooleanCellValue;
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
case CellType.String:
dr[j] = cell.StringCellValue;
break;
case CellType.Error:
dr[j] = cell.ErrorCellValue;
break;
case CellType.Formula:
cell = evaluator.EvaluateInCell(cell) as ICell;
dr[j] = cell.ToString();
break;
default:
throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
}
}
}
}
} private static int GetCellCount(ISheet sheet)
{
int firstRowNum = sheet.FirstRowNum; int cellCount = ; for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
IRow row = sheet.GetRow(i) as IRow; if (row != null && row.LastCellNum > cellCount)
{
cellCount = row.LastCellNum;
}
} return cellCount;
}
}
}
最新文章
- 关于tomcat文件下载配置
- transformClassesWithDexForDebug
- 消息中间件：RabbitMQ基本探索
- BZOJ 1462: Superkmp
- 使用pl/sql監控PROCEDURE執行時間
- 通用对象池ObjectPool的一种简易设计和实现方案
- java集合--java.util.ConcurrentModificationException异常
- 谓词(NSPredicate)
- 一次向svn中增加所有新增文件 svn add all new files
- 项目mysql数据导入数据的Java程序
- 关于我的FGC的OAuth2.0认证。
- 【翻译自mos文章】11gR2中的asm后台进程
- Flink消费Kafka数据并把实时计算的结果导入到Redis
- 删除U8中单据已经审核完成但工作流未完成的任务
- windows浏览器访问虚拟机开的rabbitmq服务，无法访问
- js 格式为2018-08-25 11:46:29 的日期比较方法
- 16_python_面向对象
- GetNumber的实现（Python & Java & Golang）
- linux写环境变量对字符转义
- flowable Service介绍
热门文章
- count(1)与count（*）
- bzoj 1517 [POI2006]Met 贪心
- 利用java.lang.reflect.Constructor动态实例化对象
- 【BZOJ】2442: [Usaco2011 Open]修剪草坪
- Local Authentication Using Challenge Response with Yubikey for CentOS 7
- MySQL 之 foreign key
- memcached启动脚本（class练习）
- UVA 10303 How Many Trees? (catlan)
- appium===Appium的前世今生
- servlet+forward和direct区别