使用NPOI导出导入导出Excel
2024-08-24 10:59:10
Excel2003
#region Excel2003
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLS(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(); //表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = ; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLS(header.GetCell(i) as XLS.HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + ; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as XLS.HSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
} /// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLS(DataTable dt, string file)
{
XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Test"); //表头
IRow row = sheet.CreateRow();
for (int i = ; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
} //数据
for (int i = ; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + );
for (int j = ; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
} //转为字节数组
MemoryStream stream = new MemoryStream();
hssfworkbook.Write(stream);
var buf = stream.ToArray(); //保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, , buf.Length);
fs.Flush();
}
} /// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(XLS.HSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.BLANK: //BLANK:
return null;
case CellType.BOOLEAN: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.NUMERIC: //NUMERIC:
return cell.NumericCellValue;
case CellType.STRING: //STRING:
return cell.StringCellValue;
case CellType.ERROR: //ERROR:
return cell.ErrorCellValue;
case CellType.FORMULA: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
Excel2007
#region Excel2007
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xlsx)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLSX(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(); //表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = ; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + ; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
} /// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLSX(DataTable dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Test"); //表头
IRow row = sheet.CreateRow();
for (int i = ; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
} //数据
for (int i = ; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + );
for (int j = ; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
/*不能使用如下方法生成Excel,因为在xssfworkbook.Write(stream);操作后会关闭流,导致报错【不能操作已关闭的流】*/
////转为字节数组
//MemoryStream stream = new MemoryStream();
//xssfworkbook.Write(stream);
//var buf = stream.ToArray(); ////保存为Excel文件
//using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
//{
// fs.Write(buf, 0, buf.Length);
// fs.Flush();
//}
/*可以使用下面方式导出*/
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";//经过测试发现不添加会打不开excel本地可以
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
Response.BinaryWrite(stream.ToArray());
xssfworkbook = null;
stream.Close();
stream.Dispose();
} /// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.BLANK: //BLANK:
return null;
case CellType.BOOLEAN: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.NUMERIC: //NUMERIC:
return cell.NumericCellValue;
case CellType.STRING: //STRING:
return cell.StringCellValue;
case CellType.ERROR: //ERROR:
return cell.ErrorCellValue;
case CellType.FORMULA: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
注意:上文提到使用NPOI导出excel2007的时候会报错【不能操作已经关闭的流】,原因是在write的时候给Dispose了,故可以重写MemenryStream的Dispose接口,代码如下:
/// <summary>
/// 这是为了避免流被NPOI关闭而实现的流
/// 当CanDispose为false时此流的dispose接口无效,仅当该值为true时有效
/// </summary>
public class ExcelStream:System.IO.MemoryStream
{
protected override void Dispose(bool disposing)
{
if (CanDispose)
{
base.Dispose(disposing);
}
} public bool CanDispose { get; set; }
}
如下调用:
ExcelStream stream = new ExcelStream();
workbook.Write(stream);
stream.CanDispose = true;
注意:操作Excel2003与操作Excel2007使用的是不同的命名空间下的内容
使用NPOI.HSSF.UserModel空间下的HSSFWorkbook操作Excel2003
使用NPOI.XSSF.UserModel空间下的XSSFWorkbook操作Excel2007
感谢:http://blog.csdn.net/halym/article/details/9235823
感谢:http://www.cnblogs.com/yu-er/p/3434173.html
最新文章
- Mysql 查看、创建、更改 数据库和表
- Github代理设置
- windows系统下的第一个console程序
- 一些 Shell 脚本(持续更新)
- Diagram of Spring 3.0 module dependencies--转载
- 如何快速的开发一个完整的iOS直播app(原理篇)
- symfony2-创建提交表单生成数据过程
- CoreAnimation 变换
- HDU Today(最短路)
- docker pull 镜像报错
- 帆软报表(finereport) 折叠树
- .NET复习笔记-泛型
- nodejs学习(imooc课程笔记, 主讲人Scott)
- NLP-Progress记录NLP最新数据集、论文和代码: 助你紧跟NLP前沿
- Docker镜像(二)
- java并发编程——Excutor
- 阿里云rds 磁盘空间满导致实例锁定
- OpenGL ES2学习笔记(6)-- Line Strip和Line Loop
- IOCP~~
- 【idea--git】