在公司搞了几天相关业务新需求开发,接触到NPOI,导出Excel,有下面几点收获。主要为导入数据到一个新建的xls文件中。自定义"任何"样式,可扩展
核心类库:NPOI.dll
单元格实体类:ExcelModel
Excel操作助手类:ExcelHelper
为了提高激情,贴个我做的效果图看看吧:
理清概念:一个单元格的构建为一个ExcelModel类。
so想要新增单元格就要新增ExcelModel类。
ExcelModel类代码:
/// <summary>
/// 表格对象
/// </summary>
public class ExcelModel
{
/// <summary>
/// 行索引
/// </summary>
public int RowIndex { get; set; }
/// <summary>
/// 列索引
/// </summary>
public int ColIndex { get; set; }
/// <summary>
/// 行跨数
/// </summary>
public int RowSpan { get; set; }
/// <summary>
/// 列跨数
/// </summary>
public int ColSpan { get; set; }
/// <summary>
/// 单元格值
/// </summary>
public string CellValue { get; set; }
/// <summary>
/// 单元格样式: left center right
/// </summary>
public string Align { get; set; }
/// <summary>
/// 单元格样式: top center bottom
/// </summary>
public string vAlign { get; set; }
/// <summary>
/// 字体样式: Bold Normal
/// </summary>
public string Boldweight { get; set; }
/// <summary>
/// 单元格样式: 12*12 15*15
/// </summary>
public double FontHeight { get; set; }
/// <summary>
/// 对应excel中的列的背景色 Violet、Coral、Grey
/// </summary>
public string BgColor { get; set; }
/// <summary>
/// 下边框 Thin
/// </summary>
public string BorderBottom { get; set; }
/// <summary>
/// 设置列宽度
/// 使用描述:object.SetColumnWidth = "columnIndex-FontWidth"
/// 使用举例:object.SetColumnWidth = "0-20"
/// </summary>
public string SetColumnWidth { get; set; }
/// <summary>
/// 字体颜色
/// 使用描述:可用Red、Blue、Grey25Percent、Orange、Yellow、Pink、Black、White
/// </summary>
public string FontColor { get; set; }
/// <summary>
/// 是否有下划线
/// </summary>
public bool IsItalic { get; set; }
}
ExcelHelper.cs类代码:
Web版
public class ExcelHelper
{
private HSSFWorkbook hssfworkbook;
private List<ExcelModel> list;
private int cellCount = 0;
/// <summary>
/// 初始化参数
/// </summary>
public ExcelHelper()
{
list = new List<ExcelModel>();
hssfworkbook = new HSSFWorkbook();
}
/// <summary>
/// 动态自定义
/// </summary>
/// <param name="savedPath">是要保存文件的服务本地物理路径</param>
/// <param name="exlName">文件名</param>
/// <param name="data">excel里每一个单元格对象</param>
public void ExportCustomedExcel(string savedPath, string exlName, List<ExcelModel> data)
{
ISheet sheet = hssfworkbook.CreateSheet();
foreach (ExcelModel1 value in data)
{
IRow row = sheet.GetRow(value.RowIndex);
if (row == null)
{
row = sheet.CreateRow(value.RowIndex);
}
ICell cell = row.GetCell(value.ColIndex);
if (cell == null)
{
cell = row.CreateCell(value.ColIndex);
}
cell.SetCellValue(value.CellValue);
//合并行、列算法
int firstrow = 0, lastrow = 0, firstcol = 0, lastcol = 0;
if (value.RowSpan > 0)
{
firstrow = value.RowIndex;
lastrow = firstrow + (value.RowSpan - 1);
firstcol = value.ColIndex;
lastcol = value.ColIndex;
}
if (value.ColSpan > 0)
{
firstcol = value.ColIndex;
lastcol = firstcol + (value.ColSpan - 1);
}
sheet.AddMergedRegion(new CellRangeAddress(firstrow, lastrow, firstcol, lastcol));
//设置列宽
if (!string.IsNullOrEmpty(value.SetColumnWidth))
{
string[] temp = value.SetColumnWidth.Split('-');
if (temp.Length == 2)
{
sheet.SetColumnWidth(Convert.ToInt32(temp[0]), Convert.ToInt32(temp[1]) * 256);
}
}
//设置样式
ICellStyle style = hssfworkbook.CreateCellStyle();
IFont font = hssfworkbook.CreateFont();
if (!string.IsNullOrEmpty(value.Align))
{
switch (value.Align)
{
case "left": style.Alignment = HorizontalAlignment.Left; break;
case "center": style.Alignment = HorizontalAlignment.Center; break;
case "right": style.Alignment = HorizontalAlignment.Right; break;
}
}
if (!string.IsNullOrEmpty(value.vAlign))
{
switch (value.vAlign)
{
case "top": style.VerticalAlignment = VerticalAlignment.Top; break;
case "center": style.VerticalAlignment = VerticalAlignment.Center; break;
case "bottom": style.VerticalAlignment = VerticalAlignment.Bottom; break;
}
}
if (!string.IsNullOrEmpty(value.Boldweight))
{
switch (value.Boldweight)
{
case "Bold": font.Boldweight = (short)FontBoldWeight.Bold; break;
case "Normal": font.Boldweight = (short)FontBoldWeight.Normal; break;
}
}
//字体颜色
if (!string.IsNullOrEmpty(value.FontColor))
{
switch (value.FontColor)
{
case "Red": font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index; break;
case "Grey25Percent": font.Color = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; break;
case "Blue": font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index; break;
case "Yellow": font.Color = NPOI.HSSF.Util.HSSFColor.Yellow.Index; break;
case "Orange": font.Color = NPOI.HSSF.Util.HSSFColor.Orange.Index; break;
case "Pink": font.Color = NPOI.HSSF.Util.HSSFColor.Pink.Index; break;
case "Black": font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index; break;
case "White": font.Color = NPOI.HSSF.Util.HSSFColor.White.Index; break;
}
}
//是否有下划线
if (value.IsItalic!=null)
{
font.IsItalic = value.IsItalic;
}
font.FontHeight = 9/0.05;//0.05是系数,9为excel字号大小
style.SetFont(font);
if (value.FontHeight > 0)
{
font.FontHeight = value.FontHeight;
}
//设置背景色
if (!string.IsNullOrEmpty(value.BgColor))
{
switch (value.BgColor)
{
case "Violet":style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Violet.Index;break;
case "Grey":style.FillForegroundColor =NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;break;
case "Coral": style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Coral.Index; break;
}
style.FillPattern = FillPattern.SolidForeground;
}
if (!string.IsNullOrEmpty(value.BorderBottom))
{
switch (value.BorderBottom)
{
case "Thin": style.BorderBottom = BorderStyle.Thin;
break;
}
}
//边框
style.BorderBottom = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
//style.WrapText = true;//自动换行
style.ShrinkToFit = true;//收缩文字
cell.CellStyle = style;
}
using (FileStream file = new FileStream(savedPath + "\\" + exlName, FileMode.OpenOrCreate))
{
hssfworkbook.Write(file);
}
}
}
ExcelHelper.cs类代码:
C/S版
public class ExcelHelper
{
private HSSFWorkbook hssfworkbook;
private List<ExcelModel> list;
private int cellCount = 0;
/// <summary>
/// 初始化参数
/// </summary>
public ExcelHelper()
{
list = new List<ExcelModel>();
hssfworkbook = new HSSFWorkbook();
}
/// <summary>
/// 动态数据
/// </summary>
/// <param name="savedPath">是要保存文件的服务本地物理路径+带后缀的文件名</param>
/// <param name="data">excel里每一个单元格对象</param>
public void ExportCustomedExcel(string savedPath, List<ExcelModel> data)
{
ISheet sheet = hssfworkbook.CreateSheet();
foreach (ExcelModel1 value in data)
{
IRow row = sheet.GetRow(value.RowIndex);
if (row == null)
{
row = sheet.CreateRow(value.RowIndex);
}
ICell cell = row.GetCell(value.ColIndex);
if (cell == null)
{
cell = row.CreateCell(value.ColIndex);
}
cell.SetCellValue(value.CellValue);
//合并行、列算法
int firstrow = 0, lastrow = 0, firstcol = 0, lastcol = 0;
if (value.RowSpan > 0)
{
firstrow = value.RowIndex;
lastrow = firstrow + (value.RowSpan - 1);
firstcol = value.ColIndex;
lastcol = value.ColIndex;
}
if (value.ColSpan > 0)
{
firstcol = value.ColIndex;
lastcol = firstcol + (value.ColSpan - 1);
}
sheet.AddMergedRegion(new CellRangeAddress(firstrow, lastrow, firstcol, lastcol));
//设置列宽
if (!string.IsNullOrEmpty(value.SetColumnWidth))
{
string[] temp = value.SetColumnWidth.Split('-');
if (temp.Length == 2)
{
sheet.SetColumnWidth(Convert.ToInt32(temp[0]), Convert.ToInt32(temp[1]) * 256);
}
}
//设置样式
ICellStyle style = hssfworkbook.CreateCellStyle();
IFont font = hssfworkbook.CreateFont();
if (!string.IsNullOrEmpty(value.Align))
{
switch (value.Align)
{
case "left": style.Alignment = HorizontalAlignment.Left; break;
case "center": style.Alignment = HorizontalAlignment.Center; break;
case "right": style.Alignment = HorizontalAlignment.Right; break;
}
}
if (!string.IsNullOrEmpty(value.vAlign))
{
switch (value.vAlign)
{
case "top": style.VerticalAlignment = VerticalAlignment.Top; break;
case "center": style.VerticalAlignment = VerticalAlignment.Center; break;
case "bottom": style.VerticalAlignment = VerticalAlignment.Bottom; break;
}
}
if (!string.IsNullOrEmpty(value.Boldweight))
{
switch (value.Boldweight)
{
case "Bold": font.Boldweight = (short)FontBoldWeight.Bold; break;
case "Normal": font.Boldweight = (short)FontBoldWeight.Normal; break;
}
}
//字体颜色
if (!string.IsNullOrEmpty(value.FontColor))
{
switch (value.FontColor)
{
case "Red": font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index; break;
case "Grey25Percent": font.Color = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; break;
case "Blue": font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index; break;
case "Yellow": font.Color = NPOI.HSSF.Util.HSSFColor.Yellow.Index; break;
case "Orange": font.Color = NPOI.HSSF.Util.HSSFColor.Orange.Index; break;
case "Pink": font.Color = NPOI.HSSF.Util.HSSFColor.Pink.Index; break;
case "Black": font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index; break;
case "White": font.Color = NPOI.HSSF.Util.HSSFColor.White.Index; break;
}
}
//是否有下划线
if (value.IsItalic != null)
{
font.IsItalic = value.IsItalic;
}
font.FontHeight = 9 / 0.05;//0.05是系数,9为excel字号大小
style.SetFont(font);
if (value.FontHeight > 0)
{
font.FontHeight = value.FontHeight;
}
//设置背景色
if (!string.IsNullOrEmpty(value.BgColor))
{
switch (value.BgColor)
{
case "Violet": style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Violet.Index; break;
case "Grey": style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; break;
case "Coral": style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Coral.Index; break;
}
style.FillPattern = FillPattern.SolidForeground;
}
if (!string.IsNullOrEmpty(value.BorderBottom))
{
switch (value.BorderBottom)
{
case "Thin": style.BorderBottom = BorderStyle.Thin;
break;
}
}
//边框
style.BorderBottom = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
//style.WrapText = true;//自动换行
style.ShrinkToFit = true;//收缩文字
cell.CellStyle = style;
}
try
{
using (FileStream file = new FileStream(savedPath, FileMode.OpenOrCreate))
{
hssfworkbook.Write(file);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
如何使用呢?
上代码:
写一个方法:
c/s下代码:
List<Model.ExcelModel> data = new List<Model.ExcelModel>();
///
///构建 ExcelModel类
///
private void GetExcelModel(int _RowIndex, int _ColIndex, int _RowSpan, int _ColSpan,
string _CellValue, string _BgColor, string _vAlign, string _Align, string _Boldweight,
string _SetColumnWidth)
{
Model.ExcelModel1 model = new Model.ExcelModel1();
model.RowIndex = _RowIndex;
model.ColIndex = _ColIndex;
model.RowSpan = _RowSpan;
model.ColSpan = _ColSpan;
model.CellValue = _CellValue;
model.BgColor = _BgColor;
model.vAlign = _vAlign;
model.Align = _Align;
model.Boldweight = _Boldweight;
model.SetColumnWidth = _SetColumnWidth;
data.Add(model);
}
B/S下的方法
List<Model.ExcelModel> data = new List<Model.ExcelModel>();
/// <summary>
/// 添加Excel实体类
/// </summary>
/// <param name="_CellValue">单元格内容</param>
/// <param name="_RowIndex">行索引</param>
/// <param name="_ColIndex">列索引</param>
/// <param name="_ColSpan">列合并数量</param>
/// <param name="_RowSpan">行合并数量</param>
/// <param name="_BgColor">背景色</param>
/// <param name="_FontColor">字体色</param>
/// <param name="_Align">水平对齐方式</param>
/// <param name="_vAlign">垂直对齐方式</param>
private void AddCellModel(string _CellValue, int _RowIndex, int _ColIndex, int _ColSpan,
int _RowSpan, string _BgColor,string _FontColor, string _Align, string _vAlign)
{
Model.ExcelModel1 modelzc = new Model.ExcelModel1();
modelzc.CellValue = _CellValue;
modelzc.RowIndex = _RowIndex;
modelzc.ColIndex = _ColIndex;
modelzc.ColSpan = _ColSpan;
modelzc.RowSpan = _RowSpan;
modelzc.Align = _Align;
modelzc.vAlign = _vAlign;
modelzc.BgColor = _BgColor;
modelzc.FontColor = _FontColor;
data.Add(modelzc);
}
如何调用这个方法呢?
请看B/S模式下代码
AddCellModel(value,startRow++,1, 1, 1, (i == _dt.Rows.Count) ? "Coral" : "", "", "center", "center");
excel.ExportCustomedExcelByPlan(context.Server.MapPath("~/EXCELbyNPOI"), filename, data);
请看C/S模式下代码
//路径选择
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel|*.xls";
sfd.FileName = "导出文件名_" + DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day;
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
exceler.ExportCustomedExcel(sfd.FileName, data);
MessageBox.Show("保存成功");
}
写了这么多,你是不是看晕了,好吧….