本文将为您提供关于POI读取EXCel属性示例的详细介绍，我们还将为您解释poi读取excel数据方式的相关知识，同时，我们还将为您提供关于ApachePOI读取Excel、asp.net使用NPOI
本文将为您提供关于POI读取EXCel属性示例的详细介绍，我们还将为您解释poi读取excel数据方式的相关知识，同时，我们还将为您提供关于Apache POI读取Excel、asp.net 使用NPOI读取excel文件、C# NPOI读取Excel数据、C#使用NPOI读取excel转为DataSet的实用信息。
本文目录一览：- POI读取EXCel属性示例（poi读取excel数据方式）
- Apache POI读取Excel
- asp.net 使用NPOI读取excel文件
- C# NPOI读取Excel数据
- C#使用NPOI读取excel转为DataSet
POI读取EXCel属性示例（poi读取excel数据方式）
/**
* 将Excel样式及数据一并导入数据库
* @param param
* @return 是否成功标示
* 创建人:王炫皓
* 创建时间:2016/7/26
*/
@Override
public HashMap ExeclReportImportData(HashMap param) {
int sheetNumber = Integer.parseInt(param.get("sheetNumber")+""); //excel 页号
int reportNumber = Integer.parseInt(param.get("reportNumber")+""); // 报表内码
HashMap rowHashMap ;
//封装结果集
List excelDatalist = new ArrayList();
String iyea = param.get("iyear")+""; //年度
String filepath = "";//完整路径
String filename = param.get("filename") + "";//文件名称
String url = this.getClass().getResource("").getFile();
url = url.replaceAll("%20", " ");
// File fpath = new File(url.substring(0, url.indexOf("webapps")) + "webapps/importdata/");
File fpath = new File("E:/apache-tomcat-7.0.69/webapps/importdata");
if (fpath.exists() == false) {
fpath.mkdir();
System.out.println("路径不存在,但是已经成功创建了:->" + fpath);
return null;
}
filepath = fpath + "/" + filename;
Workbook workbook = null;// 以文件流的方式读取文件
try {
//获取工作簿
workbook = new HSSFWorkbook(new POIFSFileSystem( new FileInputStream(filepath)));
//根据下标获取 工作表
Sheet sheet = workbook.getSheetAt(sheetNumber);
//总行数
int rowNum = sheet.getLastRowNum();
for (int i = 0; i <= rowNum; i++) {
rowHashMap = new HashMap();
//根据下标获取行
Row row = sheet.getRow(i);
rowHashMap.put("icellrow", row.getRowNum()); //单元格行号
rowHashMap.put("icellrowheight",row.getHeightInPoints());
//遍历所有列
for (int j = 1; j<=row.getLastCellNum(); j++){
//根据下标获取列
Cell cl = row.getCell(j);
if(cl != null){
CellStyle cs = cl.getCellStyle();
/*
*背景色
*/
Color bgcolor = cs.getFillBackgroundColorColor();
rowHashMap.put("icellbackcolor", ToolUtil.renderColor(bgcolor)) ;
/*
* 字体颜色
*/
Color fontcolor = cs.getFillForegroundColorColor();
rowHashMap.put("icellfontcolor", ToolUtil.renderColor(fontcolor)) ;
/*
* 字体样式
*/
Font font = workbook.getFontAt( cs.getFontIndex());
rowHashMap.put("icellfont", font.getFontName());
//行高
rowHashMap.put("icellfontsize",font.getFontHeightInPoints());
//单元格式
rowHashMap.put("icelltype",cl.getCellType());
//单元格值
rowHashMap.put("icellvalue",cl.getStringCellValue());
//单元格列号
rowHashMap.put("icellcol", cl.getColumnIndex());
//列名
rowHashMap.put("icellname", CellReference.convertNumToColString(j));
//列宽 sheet.getColumnWidth(in units of 1/256th of a character width )
rowHashMap.put("icellcolwidth",sheet.getColumnWidth(cl.getColumnIndex())/256);
excelDatalist.add(rowHashMap);
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
Apache POI读取Excel
1、pom.xml配置文件
1 <!-- 配置Apache POI -->
2 <dependency>
3 <groupId>org.apache.poi</groupId>
4 <artifactId>poi</artifactId>
5 <version>4.1.0</version>
6 </dependency>
7 <dependency>
8 <groupId>org.apache.poi</groupId>
9 <artifactId>poi-ooxml</artifactId>
10 <version>4.1.0</version>
11 </dependency>
12 <dependency>
13 <groupId>org.apache.poi</groupId>
14 <artifactId>poi-ooxml-schemas</artifactId>
15 <version>4.1.0</version>
16 </dependency>2、读取Excel的Java代码
1 @Test
2 public void readExcelTest() throws IOException {
3 File xlsFile = new File("E:\\book.xlsx");
4 // 获得工作簿
5 XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(xlsFile));
6 // 获得sheet
7 XSSFSheet sheet = wb.getSheetAt(0);
8 int rows = sheet.getPhysicalNumberOfRows();
9 String split = "**********";
10 for (int i = 0; i < rows; i++) {
11 // 获取第i行
12 XSSFRow row = sheet.getRow(i);
13 // 列数
14 short nums = row.getLastCellNum();
15 for (int j = 0; j < nums; j++) {
16 Cell cell = row.getCell(j);
17 System.out.print(cell + split);
18 }
19 System.out.println();
20 }
21 }3、Excel文件和测试结果
以上只是java读取Excel应用演示，代码中没有对对象的非空判定，需要的童鞋可自行加上判定，使代码更健壮。
asp.net 使用NPOI读取excel文件
asp.net 使用NPOI读取excel文件内容
NPOI下载地址：NPOI
public class ExcelHelper
{
/// <summary>
/// 读取Excel文件数据到DataSet，一个Sheet对应一个DataTable
/// </summary>
/// <param name="strExcelFilePath">Excel文件的物理路径</param>
/// <returns></returns>
public static DataSet GetDataFromExcel(string strExcelPhysicalPath, out string strError)
{
try
{
DataSet dsResult = new DataSet();
strError = "";
IWorkbook wbook = null;
using (FileStream fs = new FileStream(strExcelPhysicalPath, FileMode.Open, FileAccess.Read))
{
if (strExcelPhysicalPath.IndexOf(".xlsx") > 0)
{
wbook = new XSSFWorkbook(fs);
}
else
{
wbook = new HSSFWorkbook(fs);
}
}
for (int i = 0; i < wbook.NumberOfSheets; i++)
{
ISheet wsheet = wbook.GetSheetAt(i);
if (wsheet == null) continue;
DataTable dtSheet = GetDataFromSheet(wsheet, out strError);
if (dtSheet != null)
{
dtSheet.TableName = wsheet.SheetName.Trim();
dsResult.Tables.Add(dtSheet);
}
else
{
dsResult = null;
break;
}
}
return dsResult;
}
catch (Exception ex)
{
strError = ex.Message.ToString();
return null;
}
}
private static DataTable GetDataFromSheet(ISheet wsheet, out string strError)
{
try
{
DataTable dtResult = new DataTable();
strError = "";
//取sheet最大列数
int max_column = 0;
for (int i = wsheet.FirstRowNum; i <= wsheet.LastRowNum; i++)
{
IRow rsheet = wsheet.GetRow(i);
if (rsheet != null && rsheet.LastCellNum > max_column)
{
max_column = rsheet.LastCellNum;
}
}
//给DataTable添加列
for (int i = 0; i < max_column; i++)
{
dtResult.Columns.Add("A" + i.ToString());
}
for (int i = wsheet.FirstRowNum; i <= wsheet.LastRowNum; i++)
{
DataRow dRow = dtResult.NewRow();
IRow rsheet = wsheet.GetRow(i);
if (rsheet == null) continue;
for (int j = rsheet.FirstCellNum; j < rsheet.LastCellNum; j++)
{
ICell csheet = rsheet.GetCell(j);
if (csheet == null) continue;
switch (csheet.CellType)
{
case CellType.Blank:
dRow[j] = "";
break;
case CellType.Boolean:
dRow[j] = csheet.BooleanCellValue;
break;
case CellType.Error:
dRow[j] = csheet.ErrorCellValue;
break;
case CellType.Formula:
try
{
dRow[j] = csheet.NumericCellValue;
short format1 = csheet.CellStyle.DataFormat;
if (format1 == 177 || format1 == 178 || format1 == 188)
{
dRow[j] = csheet.NumericCellValue.ToString("#0.00");
}
}
catch
{
dRow[j] = csheet.StringCellValue.Trim();
}
break;
case CellType.Numeric:
try
{
short format2 = csheet.CellStyle.DataFormat;
if (format2 == 14 || format2 == 31 || format2 == 57 || format2 == 58)
{
dRow[j] = csheet.DateCellValue;
}
else
{
dRow[j] = csheet.NumericCellValue;
}
if (format2 == 177 || format2 == 178 || format2 == 188)
{
dRow[j] = csheet.NumericCellValue.ToString("#0.00");
}
}
catch
{
dRow[j] = csheet.StringCellValue.Trim();
}
break;
case CellType.String:
dRow[j] = csheet.StringCellValue.Trim();
break;
default:
dRow[j] = csheet.StringCellValue.Trim();
break;
}
}
dtResult.Rows.Add(dRow);
}
return dtResult;
}
catch (Exception ex)
{
strError = ex.Message.ToString();
return null;
}
}
}
C# NPOI读取Excel数据
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace SYS_TEST.BaseClass
{
//NPOI方式
//NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。
//优点：读取Excel速度较快，读取方式操作灵活性
//缺点：需要下载相应的插件并添加到系统引用当中。
public class NPOIClass
{
/// <summary>
/// Excel转换成DataTable（.xls）
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string filePath)
{
var dt = new DataTable();
using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var hssfworkbook = new HSSFWorkbook(file);
var sheet = hssfworkbook.GetSheetAt(0);
for (var j = 0; j < 5; j++)
{
dt.Columns.Add(Convert.ToChar(((int)''A'') + j).ToString());
}
var rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
var row = (HSSFRow)rows.Current;
var dr = dt.NewRow();
for (var i = 0; i < row.LastCellNum; i++)
{
var cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
switch (cell.CellType)
{
case CellType.Blank:
dr[i] = "[null]";
break;
case CellType.Boolean:
dr[i] = cell.BooleanCellValue;
break;
case CellType.Numeric:
dr[i] = cell.ToString();
break;
case CellType.String:
dr[i] = cell.StringCellValue;
break;
case CellType.Error:
dr[i] = cell.ErrorCellValue;
break;
case CellType.Formula:
try
{
dr[i] = cell.NumericCellValue;
}
catch
{
dr[i] = cell.StringCellValue;
}
break;
default:
dr[i] = "=" + cell.CellFormula;
break;
}
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// Excel转换成DataSet（.xlsx/.xls）
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <param name="strMsg"></param>
/// <returns></returns>
public static DataSet ExcelToDataSet(string filePath, out string strMsg)
{
strMsg = "";
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string fileType = Path.GetExtension(filePath).ToLower();
string fileName = Path.GetFileName(filePath).ToLower();
try
{
ISheet sheet = null;
int sheetNumber = 0;
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
if (fileType == ".xlsx")
{
// 2007版本
XSSFWorkbook workbook = new XSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out strMsg);
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
MessageBox.Show("Sheet数据获取失败，原因：" + strMsg);
}
}
}
}
else if (fileType == ".xls")
{
// 2003版本
HSSFWorkbook workbook = new HSSFWorkbook(fs);
sheetNumber = workbook.NumberOfSheets;
for (int i = 0; i < sheetNumber; i++)
{
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheet(sheetName);
if (sheet != null)
{
dt = GetSheetDataTable(sheet, out strMsg);
if (dt != null)
{
dt.TableName = sheetName.Trim();
ds.Tables.Add(dt);
}
else
{
MessageBox.Show("Sheet数据获取失败，原因：" + strMsg);
}
}
}
}
return ds;
}
catch (Exception ex)
{
strMsg = ex.Message;
return null;
}
}
/// <summary>
/// 获取sheet表对应的DataTable
/// </summary>
/// <param name="sheet">Excel工作表</param>
/// <param name="strMsg"></param>
/// <returns></returns>
private static DataTable GetSheetDataTable(ISheet sheet, out string strMsg)
{
strMsg = "";
DataTable dt = new DataTable();
string sheetName = sheet.SheetName;
int startIndex = 0;// sheet.FirstRowNum;
int lastIndex = sheet.LastRowNum;
//最大列数
int cellCount = 0;
IRow maxRow = sheet.GetRow(0);
for (int i = startIndex; i <= lastIndex; i++)
{
IRow row = sheet.GetRow(i);
if (row != null && cellCount < row.LastCellNum)
{
cellCount = row.LastCellNum;
maxRow = row;
}
}
//列名设置
try
{
for (int i = 0; i < maxRow.LastCellNum; i++)//maxRow.FirstCellNum
{
dt.Columns.Add(Convert.ToChar(((int)''A'') + i).ToString());
//DataColumn column = new DataColumn("Column" + (i + 1).ToString());
//dt.Columns.Add(column);
}
}
catch
{
strMsg = "工作表" + sheetName + "中无数据";
return null;
}
//数据填充
for (int i = startIndex; i <= lastIndex; i++)
{
IRow row = sheet.GetRow(i);
DataRow drNew = dt.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
{
if (row.GetCell(j) != null)
{
ICell cell = row.GetCell(j);
switch (cell.CellType)
{
case CellType.Blank:
drNew[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
if (format == 14 || format == 31 || format == 57 || format == 58)
drNew[j] = cell.DateCellValue;
else
drNew[j] = cell.NumericCellValue;
if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
drNew[j] = cell.NumericCellValue.ToString("#0.00");
break;
case CellType.String:
drNew[j] = cell.StringCellValue;
break;
case CellType.Formula:
try
{
drNew[j] = cell.NumericCellValue;
if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
drNew[j] = cell.NumericCellValue.ToString("#0.00");
}
catch
{
try
{
drNew[j] = cell.StringCellValue;
}
catch { }
}
break;
default:
drNew[j] = cell.StringCellValue;
break;
}
}
}
}
dt.Rows.Add(drNew);
}
return dt;
}
}
}
C#使用NPOI读取excel转为DataSet
本文实例为大家分享了C#使用NPOI读取excel转为DataSet的具体代码，供大家参考，具体内容如下
NPOI读取excel转为DataSet
/// <summary>
/// 读取Execl数据到DataTable(DataSet)中
/// </summary>
/// <param name="filePath">指定Execl文件路径</param>
/// <param name="isFirstLineColumnName">设置第一行是否是列名</param>
/// <returns>返回一个DataTable数据集</returns>
public static DataSet ExcelToDataSet(string filePath, bool isFirstLineColumnName)
{
DataSet dataSet = new DataSet();
int startRow = 0;
try
{
using (FileStream fs = File.OpenRead(filePath))
{
IWorkbook workbook = null;
// 如果是2007+的Excel版本
if (filePath.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook(fs);
}
// 如果是2003-的Excel版本
else if (filePath.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
//循环读取Excel的每个sheet，每个sheet页都转换为一个DataTable，并放在DataSet中
for (int p = 0; p < workbook.NumberOfSheets; p++)
{
ISheet sheet = workbook.GetSheetAt(p);
DataTable dataTable = new DataTable();
dataTable.TableName = sheet.SheetName;
if (sheet != null)
{
int rowCount = sheet.LastRowNum;//获取总行数
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0);//获取第一行
int cellCount = firstRow.LastCellNum;//获取总列数
//构建datatable的列
if (isFirstLineColumnName)
{
startRow = 1;//如果第一行是列名，则从第二行开始读取
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
DataColumn column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
}
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
DataColumn column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
//填充行
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue;
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
if (format == 14 || format == 22 || format == 31 || format == 57 || format == 58)
dataRow[j] = cell.DateCellValue;
else
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
}
}
}
dataTable.Rows.Add(dataRow);
}
}
}
dataSet.Tables.Add(dataTable);
}
}
}
return dataSet;
}
catch (Exception ex)
{
var msg = ex.Message;
return null;
}
}Dataset 导出为Excel
/// <summary>
/// 将DataTable(DataSet)导出到Execl文档
/// </summary>
/// <param name="dataSet">传入一个DataSet</param>
/// <param name="Outpath">导出路径（可以不加扩展名，不加默认为.xls）</param>
/// <returns>返回一个Bool类型的值，表示是否导出成功</returns>
/// True表示导出成功，Flase表示导出失败
public static bool DataTableToExcel(DataSet dataSet, string Outpath)
{
bool result = false;
try
{
if (dataSet == null || dataSet.Tables == null || dataSet.Tables.Count == 0 || string.IsNullOrEmpty(Outpath))
throw new Exception("输入的DataSet或路径异常");
int sheetIndex = 0;
//根据输出路径的扩展名判断workbook的实例类型
IWorkbook workbook = null;
string pathExtensionName = Outpath.Trim().Substring(Outpath.Length - 5);
if (pathExtensionName.Contains(".xlsx"))
{
workbook = new XSSFWorkbook();
}
else if(pathExtensionName.Contains(".xls"))
{
workbook = new HSSFWorkbook();
}
else
{
Outpath = Outpath.Trim() + ".xls";
workbook = new HSSFWorkbook();
}
//将DataSet导出为Excel
foreach (DataTable dt in dataSet.Tables)
{
sheetIndex++;
if (dt != null && dt.Rows.Count > 0)
{
ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? ("sheet" + sheetIndex) : dt.TableName);//创建一个名称为Sheet0的表
int rowCount = dt.Rows.Count;//行数
int columnCount = dt.Columns.Count;//列数
//设置列头
IRow row = sheet.CreateRow(0);//excel第一行设为列头
for (int c = 0; c < columnCount; c++)
{
ICell cell = row.CreateCell(c);
cell.SetCellValue(dt.Columns[c].ColumnName);
}
//设置每行每列的单元格,
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i + 1);
for (int j = 0; j < columnCount; j++)
{
ICell cell = row.CreateCell(j);//excel第二行开始写入数据
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
}
}
//向outPath输出数据
using (FileStream fs = File.OpenWrite(Outpath))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
return result;
}
catch (Exception ex)
{
return false;
}
}
}调用方法
DataSet set = ExcelHelper.ExcelToDataTable("test.xlsx", true);//Excel导入
bool b = ExcelHelper.DataTableToExcel(set, "test2.xlsx");//导出Excel以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。
您可能感兴趣的文章:
- C#利用NPOI操作Excel(单元格设置)
- C# 基于NPOI操作Excel
- c# 根据NPOI 读取一个excel 文件的多个Sheet
- C#使用NPOI将excel导入到list的方法
- C#通过NPOI导入导出数据EXCEL
- C#用NPOI导出导入Excel帮助类
- C#使用Npoi导出Excel并合并行列
- C#使用NPOI库读写Excel文件
- C#使用NPOI实现Excel导入导出功能
- C#使用NPOI设置Excel下拉选项
今天的关于POI读取EXCel属性示例和poi读取excel数据方式的分享已经结束，谢谢您的关注，如果想了解更多关于Apache POI读取Excel、asp.net 使用NPOI读取excel文件、C# NPOI读取Excel数据、C#使用NPOI读取excel转为DataSet的相关知识，请在本站进行查询。
本文标签：
