NPOI使用入门(一)【对Excel基本操作】

2017-01-13 10:51:22来源:csdn作者:WuLex人点击

第七城市
生活若剥去理想、梦想、幻想,那生命便只是一堆空架子。Default.aspx内容:<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form runat="server">
<div>
<asp:Button ID="btnExport" runat="server" Text="导出" OnClick="btnExport_Click" />
<asp:FileUpload ID="fileUpload" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="导入" OnClick="btnImport_Click" />
<asp:GridView ID="gvPS" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="序号" HeaderText="序号" />
<asp:BoundField DataField="姓名" HeaderText="姓名" />
<asp:BoundField DataField="性别" HeaderText="性别" />
<asp:BoundField DataField="身份证" HeaderText="身份证" />
<asp:BoundField DataField="随机唯一标识码" HeaderText="随机唯一标识码" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
Default.aspx.cs代码:public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//gvPS.DataSource = GetDataTable();
//gvPS.DataBind();
}
protected void btnExport_Click(object sender, EventArgs e)
{
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("姓名", "姓名");
dic.Add("序号", "序号");
DataTable dt = GetDataTable();
ExcelOperate.ToExcel(dt,dic,"cs.xls");
}
protected void btnImport_Click(object sender, EventArgs e)
{
string path = Server.MapPath("~/Temp/");
if (fileUpload.HasFile)
{
fileUpload.SaveAs(path + fileUpload.FileName);
}DataTable dt = ExcelOperate.ToDataTable(path + fileUpload.FileName);gvPS.DataSource = dt;
gvPS.DataBind();
}
private DataTable GetDataTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("序号", typeof(int));
dt.Columns.Add("姓名", typeof(string));
dt.Columns.Add("性别", typeof(string));
dt.Columns.Add("身份证", typeof(string));
dt.Columns.Add("随机唯一标识码", typeof(string));dt.Rows.Add(1, "傅芷若", "女", "511702197407135024", Guid.NewGuid().ToString("N"));
dt.Rows.Add(2, "顾岚彩", "女", "511702198304257904", Guid.NewGuid().ToString("N"));
dt.Rows.Add(3, "韦问萍", "女", "511702198107283986", Guid.NewGuid().ToString("N"));
dt.Rows.Add(4, "唐芷文", "女", "511702199001103486", Guid.NewGuid().ToString("N"));
dt.Rows.Add(5, "姜娟巧", "女", "511702197301289703", Guid.NewGuid().ToString("N"));
dt.Rows.Add(6, "郎芳芳", "女", "451025197709242781", Guid.NewGuid().ToString("N"));
dt.Rows.Add(7, "罗忆梅", "女", "451025198607141183", Guid.NewGuid().ToString("N"));
dt.Rows.Add(8, "廉清逸", "女", "451025197606178342", Guid.NewGuid().ToString("N"));
dt.Rows.Add(9, "冯凌雪", "女", "45102519840920354X", Guid.NewGuid().ToString("N"));
dt.Rows.Add(10, "柏娜兰", "女", "411525197204252845", Guid.NewGuid().ToString("N"));
dt.Rows.Add(11, "卞涵韵", "女", "120000198806269580", Guid.NewGuid().ToString("N"));
dt.Rows.Add(12, "岑安卉", "女", "120000198301207800", Guid.NewGuid().ToString("N"));return dt;
}
}Common.cs代码:/// <summary>
/// 操作Excel通用类
/// </summary>
internal static class Common
{
/// <summary>
/// 判断是否xls
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static bool GetIsCompatible(string filePath)
{
return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
}/// <summary>
/// 创建工作薄
/// </summary>
/// <param name="isCompatible"></param>
/// <returns></returns>
public static IWorkbook CreateWorkbook(bool isCompatible)
{
if (isCompatible)
{
return new HSSFWorkbook();
}
else
{
return new XSSFWorkbook();
}
}/// <summary>
/// 创建工作薄(依据文件流)
/// </summary>
/// <param name="isCompatible"></param>
/// <param name="stream"></param>
/// <returns></returns>
public static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
{
if (isCompatible)
{
return new HSSFWorkbook(stream);
}
else
{
return new XSSFWorkbook(stream);
}
}/// <summary>
/// 创建表格头单元格
/// </summary>
/// <param name="sheet"></param>
/// <returns></returns>
public static ICellStyle GetCellStyle(IWorkbook workbook, bool isHeaderRow = false)
{
ICellStyle style = workbook.CreateCellStyle();if (isHeaderRow)
{
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
IFont f = workbook.CreateFont();
f.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(f);
}style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
return style;
}/// <summary>
/// 根据单元格内容重新设置列宽
/// </summary>
/// <param name="sheet"></param>
/// <param name="cell"></param>
public static void ReSizeColumnWidth(ISheet sheet, ICell cell)
{
int cellLength = (Encoding.Default.GetBytes(cell.ToString()).Length + 5) * 256;
const int maxLength = 255 * 256;
if (cellLength > maxLength)
{
cellLength = maxLength;
}
int colWidth = sheet.GetColumnWidth(cell.ColumnIndex);
if (colWidth < cellLength)
{
sheet.SetColumnWidth(cell.ColumnIndex, cellLength);
}
}/// <summary>
/// 从工作表中生成DataTable
/// </summary>
/// <param name="sheet"></param>
/// <param name="headerRowIndex"></param>
/// <returns></returns>
public static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
{
DataTable table = new DataTable();IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列,则不再继续向后读取
cellCount = i;
break;
}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}table.Rows.Add(dataRow);
}
}return table;
}
}ExcelOperate.cs代码:/// <summary>
/// Excel操作类
/// </summary>
public sealed class ExcelOperate
{
/// <summary>
/// DataTable导出到excel
/// </summary>
/// <param name="dtSource">数据源</param>
/// <param name="dicColAliasNames">导出的列重命名,可选</param>
/// <param name="sFileName">文件名(包含后缀名),可选</param>
/// <param name="sSheetName">工作薄名,可选</param>
public static void ToExcel(DataTable dtSource, IDictionary<string, string> dicColAliasNames = null, string sFileName = "新导出工作表.xls", string sSheetName = "Sheet")
{
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(sFileName, Encoding.UTF8));if (string.IsNullOrWhiteSpace(sFileName))
{
sFileName = "新导出工作表.xls";
}
if (string.IsNullOrWhiteSpace(sSheetName))
{
sSheetName = "Sheet";
}
bool isCompatible = Common.GetIsCompatible(sFileName);IWorkbook workbook = Common.CreateWorkbook(isCompatible);
ICellStyle headerCellStyle = Common.GetCellStyle(workbook, true);
ICellStyle cellStyle = Common.GetCellStyle(workbook);
ISheet sheet = workbook.CreateSheet(sSheetName);
int rowIndex = 1;
int colIndex = 1;
int rowIndexMax = 1048575;
int colIndexMan = 16383;
if (isCompatible)
{
rowIndexMax = 65535;
colIndexMan = 255;
}
#region 创建列头
IRow headerRow = sheet.CreateRow(0);
if (dicColAliasNames == null || dicColAliasNames.Count == 0)
{
foreach (DataColumn column in dtSource.Columns)
{
if (colIndex < colIndexMan)
{
ICell headerCell = headerRow.CreateCell(column.Ordinal);
headerCell.SetCellValue(column.ColumnName);
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
colIndex++;
}
}
}
else
{
int i = 0;
foreach (var dic in dicColAliasNames)
{
if (i < colIndexMan)
{
ICell headerCell = headerRow.CreateCell(i);
headerCell.SetCellValue(dic.Value);
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
i++;
}
}
}
#endregion
#region 填充内容
foreach (DataRow row in dtSource.Rows)
{
if (rowIndex % rowIndexMax == 0)
{
sheet = workbook.CreateSheet(sSheetName + ((int)rowIndex / rowIndexMax).ToString());
}
IRow dataRow = sheet.CreateRow(rowIndex);
if (dicColAliasNames == null || dicColAliasNames.Count == 0)
{
foreach (DataColumn column in dtSource.Columns)
{
ICell cell = dataRow.CreateCell(column.Ordinal);
string rowValue = (row[column] ?? "").ToString();
switch (column.DataType.ToString())
{
case "System.DateTime"://日期类型
cell.SetCellValue(rowValue == "" ? rowValue : DateTime.Parse(rowValue).ToShortDateString());
break;
default:
cell.SetCellValue(rowValue);
break;
}
cell.CellStyle = cellStyle;
Common.ReSizeColumnWidth(sheet, cell);
}
}
else
{
int i = 0;
foreach (var dic in dicColAliasNames)
{
ICell cell = dataRow.CreateCell(i);
string rowValue = (row[dtSource.Columns[dic.Key].Ordinal] ?? "").ToString();
switch (dtSource.Columns[dic.Key].DataType.ToString())
{
case "System.DateTime"://日期类型
cell.SetCellValue(rowValue == "" ? rowValue : DateTime.Parse(rowValue).ToShortDateString());
break;
default:
cell.SetCellValue(rowValue);
break;
}
cell.CellStyle = cellStyle;
Common.ReSizeColumnWidth(sheet, cell);
i++;
}
}
rowIndex++;
}
#endregion
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Dispose();
sheet = null;
workbook = null;curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
}
}/// <summary>
/// Excel导入到DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径,为物理路径</param>
/// <param name="sSheetName">Excel工作表名称,可选</param>
/// <param name="headerRowIndex">Excel表头行索引,可选</param>
/// <returns>DataTable</returns>
public static DataTable ToDataTable(string excelFilePath, string sSheetName = "Sheet1", int headerRowIndex = 0)
{
if (string.IsNullOrEmpty(excelFilePath))
{
return null;
}
if (string.IsNullOrWhiteSpace(sSheetName))
{
sSheetName = "Sheet1";
}
using (FileStream stream = File.OpenRead(excelFilePath))
{
bool isCompatible = Common.GetIsCompatible(excelFilePath);
IWorkbook workbook = Common.CreateWorkbook(isCompatible, stream);
ISheet sheet = workbook.GetSheet(sSheetName);
DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex);stream.Close();
workbook = null;
sheet = null;
ClearNullRow(table);
return table;
}
}/// <summary>
/// Excel导入到DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径,为物理路径</param>
/// <param name="headerRowIndex">Excel表头行索引,可选</param>
/// <returns>DataSet</returns>
public static DataSet ToDataSet(string excelFilePath, int headerRowIndex = 0)
{
if (string.IsNullOrEmpty(excelFilePath))
{
return null;
}
using (FileStream stream = File.OpenRead(excelFilePath))
{
DataSet ds = new DataSet();
bool isCompatible = Common.GetIsCompatible(excelFilePath);
IWorkbook workbook = Common.CreateWorkbook(isCompatible, stream);
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable table = Common.GetDataTableFromSheet(sheet, headerRowIndex);
ClearNullRow(table);ds.Tables.Add(table);
}
stream.Close();
workbook = null;return ds;
}
}/// <summary>
/// 清空DataTable中的空行
/// </summary>
/// <param name="dtSource"></param>
private static void ClearNullRow(DataTable dtSource)
{
for (int i = dtSource.Rows.Count - 1; i > 0; i--)
{
bool isNull = true;
for (int j = 0; j < dtSource.Columns.Count; j++)
{
if (dtSource.Rows[i][j] != null)
{
if (dtSource.Rows[i][j].ToString() != "")
{
isNull = false;
break;
}
}
}
if (isNull)
{
dtSource.Rows[i].Delete();
}
}
}
}运行结果如图:


第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台