一、根据Excel模板导出excel
1、导入NPOI.dll
2、DAL中添加类ExportExcel.cs
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
namespace DAL.Assessment
{
public class ExportExcel
{
NPOIExcel excel; //构造 采用模板方式
public ExportExcel(string tempPath)
{
excel = new NPOIExcel(tempPath);
} /// <summary>
/// 流的形式
/// </summary>
/// <returns></returns>
private MemoryStream WriteToStream()
{
return excel.Save();
} /// <summary>
/// PPK 导出
/// </summary>
/// <param name="list"></param>
/// <param name="fileName"></param>
public void PPKToExcel(string ProductInfo,string avgWeightStr, string DivWeightStr, int[][] dataArray, string fileName)
{
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlEncode(fileName)));
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
HttpContext.Current.Response.Clear();
ISheet sheet;
sheet = excel.ActiveSheet;
sheet.ForceFormulaRecalculation = true; //允许excel里的公式生效 ICellStyle style = excel.CreateCellStyle();
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
style.BorderTop = BorderStyle.THIN;
style.WrapText = true;
style.VerticalAlignment = VerticalAlignment.CENTER;
///////////////
ICellStyle styleGray = excel.CreateCellStyle();
styleGray.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index;
styleGray.FillPattern = FillPatternType.SOLID_FOREGROUND;//设置背景是否填充
styleGray.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index;
styleGray.VerticalAlignment = VerticalAlignment.CENTER; excel.SetValue(, , ProductInfo);
excel.SetValue(, , Convert.ToDouble(avgWeightStr));
excel.SetValue(, , Convert.ToDouble(DivWeightStr));
excel.SetValue(, , DateTime.Now); int rowIndex = ;
for (int i = ; i < ; i++)
{
int colIndex = ;
for (int j = ; j < dataArray[i].Length; j++)
{
excel.SetValue(rowIndex, colIndex, Convert.ToDouble(dataArray[i][j]) / ); //给excel里格子赋值
//excel.SetStyle(rowIndex, colIndex, styleGray); 设置格式
colIndex++;
}
rowIndex++;
}
byte[] fs;
fs = WriteToStream().ToArray();
HttpContext.Current.Response.BinaryWrite(WriteToStream().GetBuffer());
HttpContext.Current.Response.End();
} }
} 添加 类NPOIExcel.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Drawing;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util; public class NPOIExcel
{
internal IWorkbook Book { get; set; } private int sheetID = ;
/// <summary>
/// 当前活动的SheetID，所有的操作将指向这个Sheet
/// </summary>
public int ActiveSheetID
{
get
{
return sheetID;
}
set
{
sheetID = value;
}
} /// <summary>
/// 当前活动的SheetName，所有的操作将指向这个Sheet
/// </summary>
public string ActiveSheetName
{
get
{
return Book.GetSheetAt(sheetID).SheetName;
}
set
{
sheetID = Book.GetSheetIndex(value);
}
}
/// <summary>
/// 当前活动的Sheet，所有的操作将指向这个Sheet
/// </summary>
public ISheet ActiveSheet
{
get
{
return Book.GetSheetAt(sheetID);
}
} /// <summary>
/// 第一行非空行的行号
/// </summary>
public int FirstRowNum
{
get
{
return Book.GetSheetAt(sheetID).FirstRowNum;
}
} /// <summary>
/// 最后一行非空行的行号
/// </summary>
public int LastRostNum
{
get
{
return Book.GetSheetAt(sheetID).LastRowNum;
}
} /// <summary>
/// 无模板的Excel生成或操作
/// </summary>
public NPOIExcel()
{
Book = new HSSFWorkbook();
Book.CreateSheet();
} public NPOIExcel(Stream fileStream, string fileName)
{
if (fileName.Substring(fileName.LastIndexOf(".")) == ".xls")
{
Book = new HSSFWorkbook(fileStream);
}
else
{
Book = new XSSFWorkbook(fileStream);
}
}
/// <summary>
/// 带模板或数据的Excel生成或操作
/// </summary>
/// <param name="fileName"></param>
public NPOIExcel(string fileName)
{
Book = CreateBook(fileName);
} /// <summary>
/// 创建Excel Book
/// </summary>
/// <param name="fileName">模板文件名</param>
/// <returns></returns>
private IWorkbook CreateBook(string fileName)
{
FileInfo file = new FileInfo(fileName);
if (!file.Exists)
{
File.Create(fileName).Close();
}
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
IWorkbook book;
if (file.Extension == ".xls")
{
book = new HSSFWorkbook(fs);
}
else
{
book = new XSSFWorkbook(fs);
}
fs.Close();
if (book.NumberOfSheets == )
{
book.CreateSheet();
}
return book;
} /// <summary>
/// 新建Sheet
/// </summary>
/// <returns>新建Sheet</returns>
public ISheet CreateSheet()
{
return Book.CreateSheet();
} /// <summary>
/// 新建Sheet
/// </summary>
/// <param name="sheetName">新建Sheet的名称</param>
/// <returns>新建Sheet</returns>
public ISheet CreateSheet(string sheetName)
{
return Book.CreateSheet(sheetName);
} /// <summary>
/// 设置行高
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="height">高度</param>
public void SetRowHeight(int rowIndex, float height)
{
IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
if (row == null)
{
row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
}
row.Height = (short)(height * );
} /// <summary>
/// 设置列宽
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="columnIndex">列号</param>
/// <param name="width">宽度</param>
public void SetColumnWidth(int columnIndex, short width)
{
Book.GetSheetAt(sheetID).SetColumnWidth(columnIndex, width * );
} /// <summary>
/// 获取或设置默认行高
/// 注：只对当前ActiveSheet有效
/// </summary>
public short DefaultRowHeight
{
get
{
return (short)(Book.GetSheetAt(sheetID).DefaultRowHeight / );
}
set
{
Book.GetSheetAt(sheetID).DefaultRowHeight = value * ;
}
} /// <summary>
/// 获取或设置默认列宽
/// 注：只对当前ActiveSheet有效
/// </summary>
public int DefaultColWidth
{
get
{
return Book.GetSheetAt(sheetID).DefaultColumnWidth;
}
set
{
Book.GetSheetAt(sheetID).DefaultColumnWidth = value;
}
} /// <summary>
/// 某一列的列宽自动调整大小
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="colIndex">列号</param>
public void AutoColWidth(int colIndex)
{
Book.GetSheetAt(sheetID).AutoSizeColumn(colIndex, true);
} /// <summary>
/// 隐藏一行
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
public void HiddenRow(int rowIndex)
{
IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
if (row == null)
{
row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
}
row.ZeroHeight = true;
} /// <summary>
/// 删除一行
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
public void RemoveRow(int rowIndex)
{
IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
if (row != null)
{
ActiveSheet.RemoveRow(row);
}
} /// <summary>
/// 读取单元格的值
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="columnIndex">列号</param>
/// <returns>单元格的值</returns>
public object ReadValue(int rowIndex, int columnIndex, bool? isDateTime = null)
{
try
{
ICell cell = Book.GetSheetAt(sheetID).GetRow(rowIndex).GetCell(columnIndex);
short df = cell.CellStyle.DataFormat; //return cell.ToString();
switch (cell.CellType)
{
case CellType.BLANK:
return null;
case CellType.BOOLEAN:
return cell.BooleanCellValue;
case CellType.ERROR:
throw new Exception("Cell Value Error");
case CellType.FORMULA:
{
switch (cell.CachedFormulaResultType)
{
case CellType.BLANK:
return "";
case CellType.BOOLEAN:
return cell.BooleanCellValue;
case CellType.ERROR:
throw new Exception("Cell Value Error");
case CellType.FORMULA:
throw new Exception("The formula of this cell is too complex！");
case CellType.NUMERIC:
if (isDateTime == null)
{
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
else if (isDateTime == true)
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
case CellType.STRING:
return cell.StringCellValue;
case CellType.Unknown:
return cell.ToString();
default:
return cell.ToString();
}
}
case CellType.NUMERIC:
{
if (isDateTime == null)
{
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
else if (isDateTime == true)
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
case CellType.STRING:
return cell.StringCellValue;
case CellType.Unknown:
return cell.ToString();
default:
return cell.ToString();
}
}
catch (System.NullReferenceException)
{
return null;
}
catch (Exception ex)
{
throw ex;
}
} /// <summary>
/// 设置单元格的值
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="columnIndex">列号</param>
/// <param name="value">单元格的值</param>
public void SetValue(int rowIndex, int columnIndex, object value)
{
SetValue(rowIndex, columnIndex, value, false);
} /// <summary>
/// 设置单元格的值
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="columnIndex">列号</param>
/// <param name="value">单元格的值</param>
/// <param name="isFormula">是否是公式</param>
public void SetValue(int rowIndex, int columnIndex, object value, bool isFormula)
{
IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
if (row == null)
{
row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
}
ICell cell = row.GetCell(columnIndex);
if (cell == null)
{
cell = row.CreateCell(columnIndex);
}
if (value == null)
{
cell.SetCellValue("");
}
if (isFormula)
{
cell.SetCellFormula(value.ToString());
}
else
{
if (value is short)
{
cell.SetCellValue((short)value);
}
else if (value is int)
{
cell.SetCellValue((int)value);
}
else if (value is long)
{
cell.SetCellValue((long)value);
}
else if (value is float)
{
cell.SetCellValue((float)value);
}
else if (value is double)
{
cell.SetCellValue((double)value);
}
else if (value is bool)
{
cell.SetCellValue((bool)value);
}
else if (value is DateTime)
{
cell.SetCellValue((DateTime)value);
}
else if (value == null)
{
}
else
{
cell.SetCellValue(value.ToString());
}
} } /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="type">验证类型</param>
/// <param name="operatorType">验证方式</param>
/// <param name="minValue">最小值</param>
/// <param name="maxValue">最大值</param>
public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue)
{
SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, "", "");
} /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="type">验证类型</param>
/// <param name="operatorType">验证方式</param>
/// <param name="minValue">最小值</param>
/// <param name="maxValue">最大值</param>
/// <param name="formate">数据格式</param>
public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate)
{
SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, formate, "");
} /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="type">验证类型</param>
/// <param name="operatorType">验证方式</param>
/// <param name="minValue">最小值</param>
/// <param name="maxValue">最大值</param>
/// <param name="formate">数据格式</param>
/// <param name="AlertMassage">报错信息</param>
public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate, string AlertMassage)
{
CellRangeAddressList regions = new CellRangeAddressList(startRowIndex, EndRowIndex, startColInex, endColIndex);
DVConstraint constraint = DVConstraint.CreateNumericConstraint(ValidationType.ANY, , null, null);
switch (type)
{
case NPOIDataType.Integer:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, (int)operatorType, minValue, maxValue);
break;
case NPOIDataType.Float:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, (int)operatorType, minValue, maxValue);
break;
case NPOIDataType.Date:
if (formate == "")
{
formate = "yyyy/MM/dd";
}
constraint = DVConstraint.CreateDateConstraint((int)operatorType, minValue, maxValue, formate);
break;
case NPOIDataType.Time:
constraint = DVConstraint.CreateTimeConstraint((int)operatorType, minValue, maxValue);
break;
case NPOIDataType.TextLength:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, (int)operatorType, minValue, maxValue);
break;
default:
break;
} HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions, constraint);
if (!string.IsNullOrEmpty(AlertMassage))
{
dataValidate1.CreateErrorBox("Error", AlertMassage);
}
ActiveSheet.AddValidationData(dataValidate1); } /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="dataRange">值系列</param>
public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, string[] dataRange)
{ SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, dataRange, "");
} /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="dataRange">值系列</param>
/// <param name="alertMassage">报错信息</param>
public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string[] dataRange, string alertMassage)
{
ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
CellRangeAddress[] regions = {
new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
}; CellRangeAddressList rangeList = new CellRangeAddressList();
rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(dataRange);
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); if (!string.IsNullOrEmpty(alertMassage))
{
dataValidation.CreateErrorBox("Error", alertMassage);
} ActiveSheet.AddValidationData(dataValidation);
} /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="formula">计算公式</param>
/// <param name="alertMassage">报错信息</param>
public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula, string alertMassage)
{
ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
CellRangeAddress[] regions = {
new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
}; CellRangeAddressList rangeList = new CellRangeAddressList();
rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(formula);
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); if (!string.IsNullOrEmpty(alertMassage))
{
dataValidation.CreateErrorBox("Error", alertMassage);
} ActiveSheet.AddValidationData(dataValidation);
} /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="formula">计算公式</param>
public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula)
{
SetValueRange(startRowIndex, endColIndex, startRowIndex, endColIndex, formula, "");
} /// <summary>
/// 生成单元格样式
/// </summary>
/// <returns>与当前Excel相关的单元格样式</returns>
public ICellStyle CreateCellStyle()
{
return Book.CreateCellStyle();
} /// <summary>
/// 生成字体
/// </summary>
/// <returns>与当前Excel相关的字体</returns>
public IFont CreateFont()
{
return Book.CreateFont();
} /// <summary>
/// 设置单元格样式
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="columnIndex">列号</param>
/// <param name="style">样式</param>
public void SetStyle(int rowIndex, int columnIndex, ICellStyle style)
{
IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
if (row == null)
{
row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
}
ICell cell = row.GetCell(columnIndex);
if (cell == null)
{
cell = row.CreateCell(columnIndex);
}
cell.CellStyle = style;
} /// <summary>
/// 合并单元格
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行号</param>
/// <param name="startColumnIndex">开始列号</param>
/// <param name="endRowIndex">结束行号</param>
/// <param name="endColumnIndex">结束列号</param>
public void MergeCells(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
{
int index = Book.GetSheetAt(sheetID).AddMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
} /// <summary>
/// 拆分单元格
/// 注1：只对当前ActiveSheet有效
/// 注2：只有合并的单元格才能拆分
/// </summary>
/// <param name="startRowIndex">开始行号</param>
/// <param name="startColumnIndex">开始列号</param>
public void UnMergeCells(int startRowIndex, int startColumnIndex)
{
int merges = Book.GetSheetAt(sheetID).NumMergedRegions;
CellRangeAddress merge;
for (int i = ; i < merges; i++)
{
merge = Book.GetSheetAt(sheetID).GetMergedRegion(i);
if (merge.FirstRow == startRowIndex && merge.FirstColumn == startColumnIndex)
{
Book.GetSheetAt(sheetID).RemoveMergedRegion(i);
break;
}
}
} /// <summary>
/// 保存到文件
/// 注：有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
/// </summary>
/// <param name="fileName">保存文件名</param>
public void Save(string fileName)
{
FileStream file = new FileStream(fileName, FileMode.Create);
Book.Write(file);
file.Close();
} /// <summary>
/// 保存到流
/// 注：保存或下载时，有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
/// </summary>
/// <returns>内存流</returns>
public MemoryStream Save()
{
MemoryStream ms = new MemoryStream();
Book.Write(ms);
return ms;
} /// <summary>
/// 把Excel读成DataSet
/// 注：必须是正规表格式
/// </summary>
/// <returns>读出的Excel</returns>
public DataSet ReadAsDataSet()
{
DataSet rtn = new DataSet();
for (int i = ; i < SheetCount; i++)
{
ISheet sheet = Book.GetSheetAt(i);
rtn.Tables.Add(GetDataTableBySheet(sheet));
}
return rtn;
} private DataTable GetDataTableBySheet(ISheet sheet)
{
DataTable dt = new DataTable(sheet.SheetName);
int maxCols = ;
object value;
while ((value = ReadValue(sheet, , maxCols)) != null)
{
dt.Columns.Add(value.ToString());
maxCols++;
} int row = ; bool emptyRow = false;
int emptyRowCount = ;
while (emptyRowCount < )
{
emptyRow = true;
DataRow dr = dt.NewRow();
for (int i = ; i < maxCols; i++)
{
value = ReadValue(sheet, row, i);
if (value != null)
{
dr[i] = value;
emptyRow = false;
}
}
if (!emptyRow)
{
dt.Rows.Add(dr);
emptyRowCount = ;
}
else
{
emptyRowCount++;
}
row++;
} return dt;
} /// <summary>
/// 根据SheetName导出数据为DataTable
/// </summary>
/// <param name="sheetName">Sheet名称</param>
/// <returns></returns>
public DataTable GetDataTableBySheet(string sheetName)
{ ISheet sheet = Book.GetSheet(sheetName);
if (sheet != null)
{
return GetDataTableBySheet(sheet);
}
return null; } /// <summary>
/// 根据SheetName导出数据为DataTable
/// </summary>
/// <param name="sheetIndex">Sheet编号</param>
/// <returns></returns>
public DataTable GetDataTableBySheet(int sheetIndex)
{ ISheet sheet = Book.GetSheetAt(sheetIndex);
if (sheet != null)
{
return GetDataTableBySheet(sheet);
}
return null; } /// <summary>
/// 写入表格
/// </summary>
/// <param name="Data">表格数据</param>
/// <param name="col">写入的起始列</param>
/// <param name="row">写入的起始行</param>
/// <param name="titleColor">标题颜色</param>
/// <param name="fullBorder">是否需要四周边框</param>
public void WriteDataTable(DataTable Data, int col = , int row = , short? titleColor = null, bool fullBorder = true)
{
if (Data == null)
{
return;
} var titleStyle = CreateCellStyle();
var rowStyle = CreateCellStyle();
if (titleColor != null)
titleStyle.FillForegroundColor = titleColor.Value; titleStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
if (fullBorder)
{
titleStyle.BorderBottom = BorderStyle.THIN;
titleStyle.BorderLeft = BorderStyle.THIN;
titleStyle.BorderRight = BorderStyle.THIN;
titleStyle.BorderTop = BorderStyle.THIN;
titleStyle.BottomBorderColor = NPOIColor.BLACK;
titleStyle.LeftBorderColor = NPOIColor.BLACK;
titleStyle.RightBorderColor = NPOIColor.BLACK;
titleStyle.TopBorderColor = NPOIColor.BLACK;
rowStyle.BorderBottom = BorderStyle.THIN;
rowStyle.BorderLeft = BorderStyle.THIN;
rowStyle.BorderRight = BorderStyle.THIN;
rowStyle.BorderTop = BorderStyle.THIN;
rowStyle.BottomBorderColor = NPOIColor.BLACK;
rowStyle.LeftBorderColor = NPOIColor.BLACK;
rowStyle.RightBorderColor = NPOIColor.BLACK;
rowStyle.TopBorderColor = NPOIColor.BLACK;
} int iCol = , iRow = ; foreach (DataColumn dc in Data.Columns)
{
SetValue(row, col + iCol, dc.ColumnName);
SetStyle(row, col + iCol, titleStyle);
iCol++;
} rowStyle.FillForegroundColor = NPOIColor.WHITE; foreach (DataRow dr in Data.Rows)
{
iCol = ;
foreach (DataColumn dc in Data.Columns)
{
SetValue(row + iRow, col + iCol, dr[dc]);
SetStyle(row + iRow, col + iCol, rowStyle);
iCol++;
}
iRow++;
} for (int i = ; i < iCol; i++)
{
this.AutoColWidth(i);
}
} /// <summary>
/// 读取单元格的值
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="columnIndex">列号</param>
/// <returns>单元格的值</returns>
public object ReadValue(ISheet sheet, int rowIndex, int columnIndex, bool? isDateTime = null)
{
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(columnIndex);
short df = cell.CellStyle.DataFormat; //return cell.ToString();
switch (cell.CellType)
{
case CellType.BLANK:
return null;
case CellType.BOOLEAN:
return cell.BooleanCellValue;
case CellType.ERROR:
throw new Exception("Cell Value Error");
case CellType.FORMULA:
{
switch (cell.CachedFormulaResultType)
{
case CellType.BLANK:
return "";
case CellType.BOOLEAN:
return cell.BooleanCellValue;
case CellType.ERROR:
throw new Exception("Cell Value Error");
case CellType.FORMULA:
throw new Exception("The formula of this cell is too complex！");
case CellType.NUMERIC:
if (isDateTime == null)
{
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
else if (isDateTime == true)
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
case CellType.STRING:
return cell.StringCellValue;
case CellType.Unknown:
return cell.ToString();
default:
return cell.ToString();
}
}
case CellType.NUMERIC:
{
if (isDateTime == null)
{
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
else if (isDateTime == true)
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
case CellType.STRING:
return cell.StringCellValue;
case CellType.Unknown:
return cell.ToString();
default:
return cell.ToString();
}
}
catch (System.NullReferenceException)
{
return null;
}
catch (Exception ex)
{
throw ex;
}
} public int SheetCount
{
get
{
return Book.NumberOfSheets;
}
} public string GetSheetName(int index)
{
return Book.GetSheetName(index);
} public void AddPicture(byte[] data, int row, int col)
{
int picIndex = Book.AddPicture(data, PictureType.PNG); IDrawing draw = ActiveSheet.CreateDrawingPatriarch(); IClientAnchor anchor = draw.CreateAnchor(, , , , col, row, col + , col + );
IPicture pic = draw.CreatePicture(anchor, picIndex);
pic.Resize();
}
} public enum OperatorTypes
{
/// <summary>
/// 介于最大值与小值之间
/// </summary>
BETWEEN = OperatorType.BETWEEN,
/// <summary>
/// 等于最小值
/// </summary>
EQUAL = OperatorType.EQUAL,
/// <summary>
/// 大于或等于最小值
/// </summary>
GREATER_OR_EQUAL = OperatorType.GREATER_OR_EQUAL,
/// <summary>
/// 大于最小值
/// </summary>
GREATER_THAN = OperatorType.GREATER_THAN,
/// <summary>
/// 忽略
/// </summary>
NO_COMPARISON = OperatorType.IGNORED,
/// <summary>
/// 小于或等于最小值
/// </summary>
LESS_OR_EQUAL = OperatorType.LESS_OR_EQUAL,
/// <summary>
/// 小于最小值
/// </summary>
LESS_THAN = OperatorType.LESS_THAN,
/// <summary>
/// 不在最小值与最大值之间
/// </summary>
NOT_BETWEEN = OperatorType.NOT_BETWEEN,
/// <summary>
/// 不等于最小值
/// </summary>
NOT_EQUAL = OperatorType.NOT_EQUAL
} public enum NPOIDataType
{
/// <summary>
/// 验证整数
/// </summary>
Integer,
/// <summary>
/// 验证符点数
/// </summary>
Float,
/// <summary>
/// 验证日期
/// </summary>
Date,
/// <summary>
/// 验证时间
/// </summary>
Time,
/// <summary>
/// 验证字符长度
/// </summary>
TextLength
} public static class NPOIColor
{ /// <summary>
/// 红色
/// </summary>
public static short RED { get { return NPOI.HSSF.Util.HSSFColor.RED.index; } } /// <summary>
/// 蓝色
/// </summary>
public static short BLUE { get { return NPOI.HSSF.Util.HSSFColor.BLUE.index; } } /// <summary>
/// 浅绿色
/// </summary>
public static short AQUA { get { return NPOI.HSSF.Util.HSSFColor.AQUA.index; } } /// <summary>
/// 自动
/// </summary>
public static short AUTOMATIC { get { return NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index; } } /// <summary>
/// 黑色
/// </summary>
public static short BLACK { get { return NPOI.HSSF.Util.HSSFColor.BLACK.index; } } /// <summary>
/// 蓝灰色
/// </summary>
public static short BLUE_GREY { get { return NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index; } } /// <summary>
/// 明绿色
/// </summary>
public static short BRIGHT_GREEN { get { return NPOI.HSSF.Util.HSSFColor.BRIGHT_GREEN.index; } } /// <summary>
/// 棕色
/// </summary>
public static short BROWN { get { return NPOI.HSSF.Util.HSSFColor.BROWN.index; } } /// <summary>
/// 正常
/// </summary>
public static short COLOR_NORMAL { get { return NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL; } } /// <summary>
/// 珊瑚色
/// </summary>
public static short CORAL { get { return NPOI.HSSF.Util.HSSFColor.CORAL.index; } } /// <summary>
/// 亮蓝色
/// </summary>
public static short CORNFLOWER_BLUE { get { return NPOI.HSSF.Util.HSSFColor.CORNFLOWER_BLUE.index; } } /// <summary>
/// 深蓝色
/// </summary>
public static short DARK_BLUE { get { return NPOI.HSSF.Util.HSSFColor.DARK_BLUE.index; } } /// <summary>
/// 深绿色
/// </summary>
public static short DARK_GREEN { get { return NPOI.HSSF.Util.HSSFColor.DARK_GREEN.index; } } /// <summary>
/// 深红色
/// </summary>
public static short DARK_RED { get { return NPOI.HSSF.Util.HSSFColor.DARK_RED.index; } } /// <summary>
/// 深茶色
/// </summary>
public static short DARK_TEAL { get { return NPOI.HSSF.Util.HSSFColor.DARK_TEAL.index; } } /// <summary>
/// 深黄
/// </summary>
public static short DARK_YELLOW { get { return NPOI.HSSF.Util.HSSFColor.DARK_YELLOW.index; } } /// <summary>
/// 金色
/// </summary>
public static short GOLD { get { return NPOI.HSSF.Util.HSSFColor.GOLD.index; } } /// <summary>
/// 绿色
/// </summary>
public static short GREEN { get { return NPOI.HSSF.Util.HSSFColor.GREEN.index; } } /// <summary>
/// 25%灰色
/// </summary>
public static short GREY_25_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index; } } /// <summary>
/// 40%灰色
/// </summary>
public static short GREY_40_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index; } } /// <summary>
/// 50%灰色
/// </summary>
public static short GREY_50_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_50_PERCENT.index; } } /// <summary>
/// 80%灰色
/// </summary>
public static short GREY_80_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_80_PERCENT.index; } } /// <summary>
/// 靛蓝色
/// </summary>
public static short INDIGO { get { return NPOI.HSSF.Util.HSSFColor.INDIGO.index; } } /// <summary>
/// 淡紫色
/// </summary>
public static short LAVENDER { get { return NPOI.HSSF.Util.HSSFColor.LAVENDER.index; } } /// <summary>
/// 粉黄色
/// </summary>
public static short LEMON_CHIFFON { get { return NPOI.HSSF.Util.HSSFColor.LEMON_CHIFFON.index; } } /// <summary>
/// 淡蓝色
/// </summary>
public static short LIGHT_BLUE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index; } } /// <summary>
/// 淡亮蓝色
/// </summary>
public static short LIGHT_CORNFLOWER_BLUE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; } } /// <summary>
/// 淡绿色
/// </summary>
public static short LIGHT_GREEN { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index; } } /// <summary>
/// 淡桔黄色
/// </summary>
public static short LIGHT_ORANGE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_ORANGE.index; } } /// <summary>
/// 淡蓝绿色
/// </summary>
public static short LIGHT_TURQUOISE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_TURQUOISE.index; } } /// <summary>
/// 淡黄色
/// </summary>
public static short LIGHT_YELLOW { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index; } } /// <summary>
/// 绿黄色
/// </summary>
public static short LIME { get { return NPOI.HSSF.Util.HSSFColor.LIME.index; } } /// <summary>
/// 栗色
/// </summary>
public static short MAROON { get { return NPOI.HSSF.Util.HSSFColor.MAROON.index; } } /// <summary>
/// 橄榄绿色
/// </summary>
public static short OLIVE_GREEN { get { return NPOI.HSSF.Util.HSSFColor.OLIVE_GREEN.index; } } /// <summary>
/// 桔色
/// </summary>
public static short ORANGE { get { return NPOI.HSSF.Util.HSSFColor.ORANGE.index; } } /// <summary>
/// 白灰蓝色
/// </summary>
public static short PALE_BLUE { get { return NPOI.HSSF.Util.HSSFColor.PALE_BLUE.index; } } /// <summary>
/// 粉红色
/// </summary>
public static short PINK { get { return NPOI.HSSF.Util.HSSFColor.PINK.index; } } /// <summary>
/// 紫红色
/// </summary>
public static short PLUM { get { return NPOI.HSSF.Util.HSSFColor.PLUM.index; } } /// <summary>
/// 玫瑰红色
/// </summary>
public static short ROSE { get { return NPOI.HSSF.Util.HSSFColor.ROSE.index; } } /// <summary>
/// 高贵蓝
/// </summary>
public static short ROYAL_BLUE { get { return NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index; } } /// <summary>
/// 海绿色
/// </summary>
public static short SEA_GREEN { get { return NPOI.HSSF.Util.HSSFColor.SEA_GREEN.index; } } /// <summary>
/// 天空蓝
/// </summary>
public static short SKY_BLUE { get { return NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index; } } /// <summary>
/// 棕褐色
/// </summary>
public static short TAN { get { return NPOI.HSSF.Util.HSSFColor.TAN.index; } } /// <summary>
/// 茶色
/// </summary>
public static short TEAL { get { return NPOI.HSSF.Util.HSSFColor.TEAL.index; } } /// <summary>
/// 蓝绿色
/// </summary>
public static short TURQUOISE { get { return NPOI.HSSF.Util.HSSFColor.TURQUOISE.index; } } /// <summary>
/// 紫色
/// </summary>
public static short VIOLET { get { return NPOI.HSSF.Util.HSSFColor.VIOLET.index; } } /// <summary>
/// 白色
/// </summary>
public static short WHITE { get { return NPOI.HSSF.Util.HSSFColor.WHITE.index; } } /// <summary>
/// 黄色
/// </summary>
public static short YELLOW { get { return NPOI.HSSF.Util.HSSFColor.YELLOW.index; } } } /// <summary>
/// 针对excel的Oledb
/// </summary>
public class OleDbExcel
{
/// <summary>
/// OLEDB连接
/// </summary>
public OleDbConnection Connection
{
get;
set;
} /// <summary>
/// 用Oledb对Excel进行操作
/// 注：必须是标准表形式Excel内容
/// </summary>
/// <param name="excelFile">Excel文件</param>
public OleDbExcel(string excelFile)
{
string conStr = string.Empty;
FileInfo file = new FileInfo(excelFile);
if (!file.Exists) { throw new Exception("文件不存在"); }
string extension = file.Extension;
switch (extension)
{
case ".xls":
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
case ".xlsx":
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
} //链接Excel
Connection = new OleDbConnection(conStr);
} private List<string> tableNames; /// <summary>
/// 获取Excel内的Sheet名称
/// </summary>
public List<string> Sheets
{
get
{
if (tableNames == null)
{
try
{
tableNames = new List<string>();
//读取Excel里面的sheet名
Connection.Open(); DataTable schemaTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); for (int i = ; i < schemaTable.Rows.Count; i++)
{
DataRow dr = schemaTable.Rows[i];
string tbName = dr["table_name"].ToString();
if (tbName[tbName.Length - ] == '$')
{
tableNames.Add(tbName);
}
}
Connection.Close();
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
}
return tableNames;
}
} /// <summary>
/// 查询出所有数据
/// </summary>
/// <param name="tableName">Sheet名称</param>
/// <returns>sheet内的所有数据</returns>
public DataSet QueryAll(string tableName)
{
try
{
DataSet excelData = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand();
adapter.SelectCommand.Connection = Connection;
adapter.SelectCommand.CommandText = string.Format("SELECT * FROM {0}", "[" + tableName + "]");
adapter.Fill(excelData);
return excelData;
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
} /// <summary>
/// 查询出所有数据
/// </summary>
/// <param name="tableIndex">Sheet序号（从0开始）</param>
/// <returns>sheet内的所有数据</returns>
public DataSet QueryAll(int tableIndex)
{
return QueryAll(Sheets[tableIndex]);
} /// <summary>
/// 利用Sql进行查询
/// </summary>
/// <param name="sql">Sql语句</param>
/// <returns>查询出的数据</returns>
public DataSet Query(string sql)
{
try
{
DataSet excelData = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, Connection);
adapter.Fill(excelData);
return excelData;
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
} /// <summary>
/// 利用Sql进行查询
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="param">查询参数</param>
/// <returns>查询出的数据</returns>
public DataSet Query(string sql, params OleDbParameter[] param)
{
try
{
DataSet excelData = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, Connection);
adapter.SelectCommand.Parameters.AddRange(param);
adapter.Fill(excelData);
return excelData;
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
} /// <summary>
/// 利用Sql进行数据操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>影响的行数</returns>
public int ExecuteSql(string sql)
{
try
{
Connection.Open();
OleDbCommand cmd = Connection.CreateCommand();
cmd.CommandText = sql;
int rtn = cmd.ExecuteNonQuery();
Connection.Close();
return rtn;
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
} /// <summary>
/// 利用Sql进行数据操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">执行参数</param>
/// <returns>影响的行数</returns>
public int ExecuteSql(string sql, params OleDbParameter[] param)
{
try
{
Connection.Open();
OleDbCommand cmd = Connection.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddRange(param);
int rtn = cmd.ExecuteNonQuery();
Connection.Close();
return rtn;
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
}
}
3、使用
前台调用 传入参数，获取数据
[HttpPost]
public string PPKExport(string id)
{
var ProductInfo = "";
var avgWeightStr = "";
var DivWeightStr = "";
int[][] dataArray = new int[8][];
string templateFile = "~/ExcelTemp/PPKTemplate.xls"; //模板位置
ex = new ExportExcel(Server.MapPath(templateFile));
string fileName = "Xbar-R控制图.xls"; //导出的excel命名
ex.PPKToExcel(ProductInfo,avgWeightStr, DivWeightStr, dataArray, fileName); //传入参数
return fileName;
｝
4、效果
界面导出按钮
点击导出按钮，选择位置保存即可。
二、将界面table里的内容直接生成excel
1、操作excel的NPOI工具类
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Drawing;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util; public class NPOIExcel
{
internal IWorkbook Book { get; set; } private int sheetID = ;
/// <summary>
/// 当前活动的SheetID，所有的操作将指向这个Sheet
/// </summary>
public int ActiveSheetID
{
get
{
return sheetID;
}
set
{
sheetID = value;
}
} /// <summary>
/// 当前活动的SheetName，所有的操作将指向这个Sheet
/// </summary>
public string ActiveSheetName
{
get
{
return Book.GetSheetAt(sheetID).SheetName;
}
set
{
sheetID = Book.GetSheetIndex(value);
}
}
/// <summary>
/// 当前活动的Sheet，所有的操作将指向这个Sheet
/// </summary>
public ISheet ActiveSheet
{
get
{
return Book.GetSheetAt(sheetID);
}
} /// <summary>
/// 第一行非空行的行号
/// </summary>
public int FirstRowNum
{
get
{
return Book.GetSheetAt(sheetID).FirstRowNum;
}
} /// <summary>
/// 最后一行非空行的行号
/// </summary>
public int LastRostNum
{
get
{
return Book.GetSheetAt(sheetID).LastRowNum;
}
} /// <summary>
/// 无模板的Excel生成或操作
/// </summary>
public NPOIExcel()
{
Book = new HSSFWorkbook();
Book.CreateSheet();
} public NPOIExcel(Stream fileStream, string fileName)
{
if (fileName.Substring(fileName.LastIndexOf(".")) == ".xls")
{
Book = new HSSFWorkbook(fileStream);
}
else
{
Book = new XSSFWorkbook(fileStream);
}
}
/// <summary>
/// 带模板或数据的Excel生成或操作
/// </summary>
/// <param name="fileName"></param>
public NPOIExcel(string fileName)
{
Book = CreateBook(fileName);
} /// <summary>
/// 创建Excel Book
/// </summary>
/// <param name="fileName">模板文件名</param>
/// <returns></returns>
private IWorkbook CreateBook(string fileName)
{
FileInfo file = new FileInfo(fileName);
if (!file.Exists)
{
File.Create(fileName).Close();
}
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
IWorkbook book;
if (file.Extension == ".xls")
{
book = new HSSFWorkbook(fs);
}
else
{
book = new XSSFWorkbook(fs);
}
fs.Close();
if (book.NumberOfSheets == )
{
book.CreateSheet();
}
return book;
} /// <summary>
/// 新建Sheet
/// </summary>
/// <returns>新建Sheet</returns>
public ISheet CreateSheet()
{
return Book.CreateSheet();
} /// <summary>
/// 新建Sheet
/// </summary>
/// <param name="sheetName">新建Sheet的名称</param>
/// <returns>新建Sheet</returns>
public ISheet CreateSheet(string sheetName)
{
return Book.CreateSheet(sheetName);
} /// <summary>
/// 设置行高
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="height">高度</param>
public void SetRowHeight(int rowIndex, float height)
{
IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
if (row == null)
{
row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
}
row.Height = (short)(height * );
} /// <summary>
/// 设置列宽
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="columnIndex">列号</param>
/// <param name="width">宽度</param>
public void SetColumnWidth(int columnIndex, short width)
{
Book.GetSheetAt(sheetID).SetColumnWidth(columnIndex, width * );
} /// <summary>
/// 获取或设置默认行高
/// 注：只对当前ActiveSheet有效
/// </summary>
public short DefaultRowHeight
{
get
{
return (short)(Book.GetSheetAt(sheetID).DefaultRowHeight / );
}
set
{
Book.GetSheetAt(sheetID).DefaultRowHeight = value * ;
}
} /// <summary>
/// 获取或设置默认列宽
/// 注：只对当前ActiveSheet有效
/// </summary>
public int DefaultColWidth
{
get
{
return Book.GetSheetAt(sheetID).DefaultColumnWidth;
}
set
{
Book.GetSheetAt(sheetID).DefaultColumnWidth = value;
}
} /// <summary>
/// 某一列的列宽自动调整大小
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="colIndex">列号</param>
public void AutoColWidth(int colIndex)
{
Book.GetSheetAt(sheetID).AutoSizeColumn(colIndex, true);
} /// <summary>
/// 隐藏一行
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
public void HiddenRow(int rowIndex)
{
IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
if (row == null)
{
row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
}
row.ZeroHeight = true;
} /// <summary>
/// 删除一行
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
public void RemoveRow(int rowIndex)
{
IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
if (row != null)
{
ActiveSheet.RemoveRow(row);
}
} /// <summary>
/// 读取单元格的值
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="columnIndex">列号</param>
/// <returns>单元格的值</returns>
public object ReadValue(int rowIndex, int columnIndex, bool? isDateTime = null)
{
try
{
ICell cell = Book.GetSheetAt(sheetID).GetRow(rowIndex).GetCell(columnIndex);
short df = cell.CellStyle.DataFormat; //return cell.ToString();
switch (cell.CellType)
{
case CellType.BLANK:
return null;
case CellType.BOOLEAN:
return cell.BooleanCellValue;
case CellType.ERROR:
throw new Exception("Cell Value Error");
case CellType.FORMULA:
{
switch (cell.CachedFormulaResultType)
{
case CellType.BLANK:
return "";
case CellType.BOOLEAN:
return cell.BooleanCellValue;
case CellType.ERROR:
throw new Exception("Cell Value Error");
case CellType.FORMULA:
throw new Exception("The formula of this cell is too complex！");
case CellType.NUMERIC:
if (isDateTime == null)
{
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
else if (isDateTime == true)
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
case CellType.STRING:
return cell.StringCellValue;
case CellType.Unknown:
return cell.ToString();
default:
return cell.ToString();
}
}
case CellType.NUMERIC:
{
if (isDateTime == null)
{
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
else if (isDateTime == true)
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
case CellType.STRING:
return cell.StringCellValue;
case CellType.Unknown:
return cell.ToString();
default:
return cell.ToString();
}
}
catch (System.NullReferenceException)
{
return null;
}
catch (Exception ex)
{
throw ex;
}
} /// <summary>
/// 设置单元格的值
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="columnIndex">列号</param>
/// <param name="value">单元格的值</param>
public void SetValue(int rowIndex, int columnIndex, object value)
{
SetValue(rowIndex, columnIndex, value, false);
} /// <summary>
/// 设置单元格的值
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="columnIndex">列号</param>
/// <param name="value">单元格的值</param>
/// <param name="isFormula">是否是公式</param>
public void SetValue(int rowIndex, int columnIndex, object value, bool isFormula)
{
IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
if (row == null)
{
row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
}
ICell cell = row.GetCell(columnIndex);
if (cell == null)
{
cell = row.CreateCell(columnIndex);
}
if (value == null)
{
cell.SetCellValue("");
}
if (isFormula)
{
cell.SetCellFormula(value.ToString());
}
else
{
if (value is short)
{
cell.SetCellValue((short)value);
}
else if (value is int)
{
cell.SetCellValue((int)value);
}
else if (value is long)
{
cell.SetCellValue((long)value);
}
else if (value is float)
{
cell.SetCellValue((float)value);
}
else if (value is double)
{
cell.SetCellValue((double)value);
}
else if (value is bool)
{
cell.SetCellValue((bool)value);
}
else if (value is DateTime)
{
cell.SetCellValue((DateTime)value);
}
else if (value == null)
{
}
else
{
cell.SetCellValue(value.ToString());
}
} } /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="type">验证类型</param>
/// <param name="operatorType">验证方式</param>
/// <param name="minValue">最小值</param>
/// <param name="maxValue">最大值</param>
public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue)
{
SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, "", "");
} /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="type">验证类型</param>
/// <param name="operatorType">验证方式</param>
/// <param name="minValue">最小值</param>
/// <param name="maxValue">最大值</param>
/// <param name="formate">数据格式</param>
public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate)
{
SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, type, operatorType, minValue, maxValue, formate, "");
} /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="type">验证类型</param>
/// <param name="operatorType">验证方式</param>
/// <param name="minValue">最小值</param>
/// <param name="maxValue">最大值</param>
/// <param name="formate">数据格式</param>
/// <param name="AlertMassage">报错信息</param>
public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, NPOIDataType type, OperatorTypes operatorType, string minValue, string maxValue, string formate, string AlertMassage)
{
CellRangeAddressList regions = new CellRangeAddressList(startRowIndex, EndRowIndex, startColInex, endColIndex);
DVConstraint constraint = DVConstraint.CreateNumericConstraint(ValidationType.ANY, , null, null);
switch (type)
{
case NPOIDataType.Integer:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.INTEGER, (int)operatorType, minValue, maxValue);
break;
case NPOIDataType.Float:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.DECIMAL, (int)operatorType, minValue, maxValue);
break;
case NPOIDataType.Date:
if (formate == "")
{
formate = "yyyy/MM/dd";
}
constraint = DVConstraint.CreateDateConstraint((int)operatorType, minValue, maxValue, formate);
break;
case NPOIDataType.Time:
constraint = DVConstraint.CreateTimeConstraint((int)operatorType, minValue, maxValue);
break;
case NPOIDataType.TextLength:
constraint = DVConstraint.CreateNumericConstraint(ValidationType.TEXT_LENGTH, (int)operatorType, minValue, maxValue);
break;
default:
break;
} HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions, constraint);
if (!string.IsNullOrEmpty(AlertMassage))
{
dataValidate1.CreateErrorBox("Error", AlertMassage);
}
ActiveSheet.AddValidationData(dataValidate1); } /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="dataRange">值系列</param>
public void SetValueRange(int startRowIndex, int EndRowIndex, int startColInex, int endColIndex, string[] dataRange)
{ SetValueRange(startRowIndex, EndRowIndex, startColInex, endColIndex, dataRange, "");
} /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="dataRange">值系列</param>
/// <param name="alertMassage">报错信息</param>
public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string[] dataRange, string alertMassage)
{
ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
CellRangeAddress[] regions = {
new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
}; CellRangeAddressList rangeList = new CellRangeAddressList();
rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(dataRange);
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); if (!string.IsNullOrEmpty(alertMassage))
{
dataValidation.CreateErrorBox("Error", alertMassage);
} ActiveSheet.AddValidationData(dataValidation);
} /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="formula">计算公式</param>
/// <param name="alertMassage">报错信息</param>
public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula, string alertMassage)
{
ISheetConditionalFormatting hscf = ActiveSheet.SheetConditionalFormatting;
CellRangeAddress[] regions = {
new CellRangeAddress(startRowIndex, endRowIndex,startColInex,endColIndex)
}; CellRangeAddressList rangeList = new CellRangeAddressList();
rangeList.AddCellRangeAddress(new CellRangeAddress(startRowIndex, endRowIndex, startColInex, endColIndex));
DVConstraint dvconstraint = DVConstraint.CreateFormulaListConstraint(formula);
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); if (!string.IsNullOrEmpty(alertMassage))
{
dataValidation.CreateErrorBox("Error", alertMassage);
} ActiveSheet.AddValidationData(dataValidation);
} /// <summary>
/// 设置一个区域内的单元格的值范围
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行</param>
/// <param name="EndRowIndex">结束行</param>
/// <param name="startColInex">开始列</param>
/// <param name="endColIndex">结束列</param>
/// <param name="formula">计算公式</param>
public void SetValueRange(int startRowIndex, int endRowIndex, int startColInex, int endColIndex, string formula)
{
SetValueRange(startRowIndex, endColIndex, startRowIndex, endColIndex, formula, "");
} /// <summary>
/// 生成单元格样式
/// </summary>
/// <returns>与当前Excel相关的单元格样式</returns>
public ICellStyle CreateCellStyle()
{
return Book.CreateCellStyle();
} /// <summary>
/// 生成字体
/// </summary>
/// <returns>与当前Excel相关的字体</returns>
public IFont CreateFont()
{
return Book.CreateFont();
} /// <summary>
/// 设置单元格样式
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="columnIndex">列号</param>
/// <param name="style">样式</param>
public void SetStyle(int rowIndex, int columnIndex, ICellStyle style)
{
IRow row = Book.GetSheetAt(sheetID).GetRow(rowIndex);
if (row == null)
{
row = Book.GetSheetAt(sheetID).CreateRow(rowIndex);
}
ICell cell = row.GetCell(columnIndex);
if (cell == null)
{
cell = row.CreateCell(columnIndex);
}
cell.CellStyle = style;
} /// <summary>
/// 合并单元格
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="startRowIndex">开始行号</param>
/// <param name="startColumnIndex">开始列号</param>
/// <param name="endRowIndex">结束行号</param>
/// <param name="endColumnIndex">结束列号</param>
public void MergeCells(int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
{
int index = Book.GetSheetAt(sheetID).AddMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex));
} /// <summary>
/// 拆分单元格
/// 注1：只对当前ActiveSheet有效
/// 注2：只有合并的单元格才能拆分
/// </summary>
/// <param name="startRowIndex">开始行号</param>
/// <param name="startColumnIndex">开始列号</param>
public void UnMergeCells(int startRowIndex, int startColumnIndex)
{
int merges = Book.GetSheetAt(sheetID).NumMergedRegions;
CellRangeAddress merge;
for (int i = ; i < merges; i++)
{
merge = Book.GetSheetAt(sheetID).GetMergedRegion(i);
if (merge.FirstRow == startRowIndex && merge.FirstColumn == startColumnIndex)
{
Book.GetSheetAt(sheetID).RemoveMergedRegion(i);
break;
}
}
} /// <summary>
/// 保存到文件
/// 注：有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
/// </summary>
/// <param name="fileName">保存文件名</param>
public void Save(string fileName)
{
FileStream file = new FileStream(fileName, FileMode.Create);
Book.Write(file);
file.Close();
} /// <summary>
/// 保存到流
/// 注：保存或下载时，有模板的，文件扩展名与模板一样；没有模板的，文件扩展名为“.xls”；
/// </summary>
/// <returns>内存流</returns>
public MemoryStream Save()
{
MemoryStream ms = new MemoryStream();
Book.Write(ms);
return ms;
} /// <summary>
/// 把Excel读成DataSet
/// 注：必须是正规表格式
/// </summary>
/// <returns>读出的Excel</returns>
public DataSet ReadAsDataSet()
{
DataSet rtn = new DataSet();
for (int i = ; i < SheetCount; i++)
{
ISheet sheet = Book.GetSheetAt(i);
rtn.Tables.Add(GetDataTableBySheet(sheet));
}
return rtn;
} private DataTable GetDataTableBySheet(ISheet sheet)
{
DataTable dt = new DataTable(sheet.SheetName);
int maxCols = ;
object value;
while ((value = ReadValue(sheet, , maxCols)) != null)
{
dt.Columns.Add(value.ToString());
maxCols++;
} int row = ; bool emptyRow = false;
int emptyRowCount = ;
while (emptyRowCount < )
{
emptyRow = true;
DataRow dr = dt.NewRow();
for (int i = ; i < maxCols; i++)
{
value = ReadValue(sheet, row, i);
if (value != null)
{
dr[i] = value;
emptyRow = false;
}
}
if (!emptyRow)
{
dt.Rows.Add(dr);
emptyRowCount = ;
}
else
{
emptyRowCount++;
}
row++;
} return dt;
} /// <summary>
/// 根据SheetName导出数据为DataTable
/// </summary>
/// <param name="sheetName">Sheet名称</param>
/// <returns></returns>
public DataTable GetDataTableBySheet(string sheetName)
{ ISheet sheet = Book.GetSheet(sheetName);
if (sheet != null)
{
return GetDataTableBySheet(sheet);
}
return null; } /// <summary>
/// 根据SheetName导出数据为DataTable
/// </summary>
/// <param name="sheetIndex">Sheet编号</param>
/// <returns></returns>
public DataTable GetDataTableBySheet(int sheetIndex)
{ ISheet sheet = Book.GetSheetAt(sheetIndex);
if (sheet != null)
{
return GetDataTableBySheet(sheet);
}
return null; } /// <summary>
/// 写入表格
/// </summary>
/// <param name="Data">表格数据</param>
/// <param name="col">写入的起始列</param>
/// <param name="row">写入的起始行</param>
/// <param name="titleColor">标题颜色</param>
/// <param name="fullBorder">是否需要四周边框</param>
public void WriteDataTable(DataTable Data, int col = , int row = , short? titleColor = null, bool fullBorder = true)
{
if (Data == null)
{
return;
} var titleStyle = CreateCellStyle();
var rowStyle = CreateCellStyle();
if (titleColor != null)
titleStyle.FillForegroundColor = titleColor.Value; titleStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
if (fullBorder)
{
titleStyle.BorderBottom = BorderStyle.THIN;
titleStyle.BorderLeft = BorderStyle.THIN;
titleStyle.BorderRight = BorderStyle.THIN;
titleStyle.BorderTop = BorderStyle.THIN;
titleStyle.BottomBorderColor = NPOIColor.BLACK;
titleStyle.LeftBorderColor = NPOIColor.BLACK;
titleStyle.RightBorderColor = NPOIColor.BLACK;
titleStyle.TopBorderColor = NPOIColor.BLACK;
rowStyle.BorderBottom = BorderStyle.THIN;
rowStyle.BorderLeft = BorderStyle.THIN;
rowStyle.BorderRight = BorderStyle.THIN;
rowStyle.BorderTop = BorderStyle.THIN;
rowStyle.BottomBorderColor = NPOIColor.BLACK;
rowStyle.LeftBorderColor = NPOIColor.BLACK;
rowStyle.RightBorderColor = NPOIColor.BLACK;
rowStyle.TopBorderColor = NPOIColor.BLACK;
} int iCol = , iRow = ; foreach (DataColumn dc in Data.Columns)
{
SetValue(row, col + iCol, dc.ColumnName);
SetStyle(row, col + iCol, titleStyle);
iCol++;
} rowStyle.FillForegroundColor = NPOIColor.WHITE; foreach (DataRow dr in Data.Rows)
{
iCol = ;
foreach (DataColumn dc in Data.Columns)
{
SetValue(row + iRow, col + iCol, dr[dc]);
SetStyle(row + iRow, col + iCol, rowStyle);
iCol++;
}
iRow++;
} for (int i = ; i < iCol; i++)
{
this.AutoColWidth(i);
}
} /// <summary>
/// 读取单元格的值
/// 注：只对当前ActiveSheet有效
/// </summary>
/// <param name="rowIndex">行号</param>
/// <param name="columnIndex">列号</param>
/// <returns>单元格的值</returns>
public object ReadValue(ISheet sheet, int rowIndex, int columnIndex, bool? isDateTime = null)
{
try
{
ICell cell = sheet.GetRow(rowIndex).GetCell(columnIndex);
short df = cell.CellStyle.DataFormat; //return cell.ToString();
switch (cell.CellType)
{
case CellType.BLANK:
return null;
case CellType.BOOLEAN:
return cell.BooleanCellValue;
case CellType.ERROR:
throw new Exception("Cell Value Error");
case CellType.FORMULA:
{
switch (cell.CachedFormulaResultType)
{
case CellType.BLANK:
return "";
case CellType.BOOLEAN:
return cell.BooleanCellValue;
case CellType.ERROR:
throw new Exception("Cell Value Error");
case CellType.FORMULA:
throw new Exception("The formula of this cell is too complex！");
case CellType.NUMERIC:
if (isDateTime == null)
{
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
else if (isDateTime == true)
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
case CellType.STRING:
return cell.StringCellValue;
case CellType.Unknown:
return cell.ToString();
default:
return cell.ToString();
}
}
case CellType.NUMERIC:
{
if (isDateTime == null)
{
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
else if (isDateTime == true)
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
}
case CellType.STRING:
return cell.StringCellValue;
case CellType.Unknown:
return cell.ToString();
default:
return cell.ToString();
}
}
catch (System.NullReferenceException)
{
return null;
}
catch (Exception ex)
{
throw ex;
}
} public int SheetCount
{
get
{
return Book.NumberOfSheets;
}
} public string GetSheetName(int index)
{
return Book.GetSheetName(index);
} public void AddPicture(byte[] data, int row, int col)
{
int picIndex = Book.AddPicture(data, PictureType.PNG); IDrawing draw = ActiveSheet.CreateDrawingPatriarch(); IClientAnchor anchor = draw.CreateAnchor(, , , , col, row, col + , col + );
IPicture pic = draw.CreatePicture(anchor, picIndex);
pic.Resize();
}
} public enum OperatorTypes
{
/// <summary>
/// 介于最大值与小值之间
/// </summary>
BETWEEN = OperatorType.BETWEEN,
/// <summary>
/// 等于最小值
/// </summary>
EQUAL = OperatorType.EQUAL,
/// <summary>
/// 大于或等于最小值
/// </summary>
GREATER_OR_EQUAL = OperatorType.GREATER_OR_EQUAL,
/// <summary>
/// 大于最小值
/// </summary>
GREATER_THAN = OperatorType.GREATER_THAN,
/// <summary>
/// 忽略
/// </summary>
NO_COMPARISON = OperatorType.IGNORED,
/// <summary>
/// 小于或等于最小值
/// </summary>
LESS_OR_EQUAL = OperatorType.LESS_OR_EQUAL,
/// <summary>
/// 小于最小值
/// </summary>
LESS_THAN = OperatorType.LESS_THAN,
/// <summary>
/// 不在最小值与最大值之间
/// </summary>
NOT_BETWEEN = OperatorType.NOT_BETWEEN,
/// <summary>
/// 不等于最小值
/// </summary>
NOT_EQUAL = OperatorType.NOT_EQUAL
} public enum NPOIDataType
{
/// <summary>
/// 验证整数
/// </summary>
Integer,
/// <summary>
/// 验证符点数
/// </summary>
Float,
/// <summary>
/// 验证日期
/// </summary>
Date,
/// <summary>
/// 验证时间
/// </summary>
Time,
/// <summary>
/// 验证字符长度
/// </summary>
TextLength
} public static class NPOIColor
{ /// <summary>
/// 红色
/// </summary>
public static short RED { get { return NPOI.HSSF.Util.HSSFColor.RED.index; } } /// <summary>
/// 蓝色
/// </summary>
public static short BLUE { get { return NPOI.HSSF.Util.HSSFColor.BLUE.index; } } /// <summary>
/// 浅绿色
/// </summary>
public static short AQUA { get { return NPOI.HSSF.Util.HSSFColor.AQUA.index; } } /// <summary>
/// 自动
/// </summary>
public static short AUTOMATIC { get { return NPOI.HSSF.Util.HSSFColor.AUTOMATIC.index; } } /// <summary>
/// 黑色
/// </summary>
public static short BLACK { get { return NPOI.HSSF.Util.HSSFColor.BLACK.index; } } /// <summary>
/// 蓝灰色
/// </summary>
public static short BLUE_GREY { get { return NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index; } } /// <summary>
/// 明绿色
/// </summary>
public static short BRIGHT_GREEN { get { return NPOI.HSSF.Util.HSSFColor.BRIGHT_GREEN.index; } } /// <summary>
/// 棕色
/// </summary>
public static short BROWN { get { return NPOI.HSSF.Util.HSSFColor.BROWN.index; } } /// <summary>
/// 正常
/// </summary>
public static short COLOR_NORMAL { get { return NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL; } } /// <summary>
/// 珊瑚色
/// </summary>
public static short CORAL { get { return NPOI.HSSF.Util.HSSFColor.CORAL.index; } } /// <summary>
/// 亮蓝色
/// </summary>
public static short CORNFLOWER_BLUE { get { return NPOI.HSSF.Util.HSSFColor.CORNFLOWER_BLUE.index; } } /// <summary>
/// 深蓝色
/// </summary>
public static short DARK_BLUE { get { return NPOI.HSSF.Util.HSSFColor.DARK_BLUE.index; } } /// <summary>
/// 深绿色
/// </summary>
public static short DARK_GREEN { get { return NPOI.HSSF.Util.HSSFColor.DARK_GREEN.index; } } /// <summary>
/// 深红色
/// </summary>
public static short DARK_RED { get { return NPOI.HSSF.Util.HSSFColor.DARK_RED.index; } } /// <summary>
/// 深茶色
/// </summary>
public static short DARK_TEAL { get { return NPOI.HSSF.Util.HSSFColor.DARK_TEAL.index; } } /// <summary>
/// 深黄
/// </summary>
public static short DARK_YELLOW { get { return NPOI.HSSF.Util.HSSFColor.DARK_YELLOW.index; } } /// <summary>
/// 金色
/// </summary>
public static short GOLD { get { return NPOI.HSSF.Util.HSSFColor.GOLD.index; } } /// <summary>
/// 绿色
/// </summary>
public static short GREEN { get { return NPOI.HSSF.Util.HSSFColor.GREEN.index; } } /// <summary>
/// 25%灰色
/// </summary>
public static short GREY_25_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index; } } /// <summary>
/// 40%灰色
/// </summary>
public static short GREY_40_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_40_PERCENT.index; } } /// <summary>
/// 50%灰色
/// </summary>
public static short GREY_50_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_50_PERCENT.index; } } /// <summary>
/// 80%灰色
/// </summary>
public static short GREY_80_PERCENT { get { return NPOI.HSSF.Util.HSSFColor.GREY_80_PERCENT.index; } } /// <summary>
/// 靛蓝色
/// </summary>
public static short INDIGO { get { return NPOI.HSSF.Util.HSSFColor.INDIGO.index; } } /// <summary>
/// 淡紫色
/// </summary>
public static short LAVENDER { get { return NPOI.HSSF.Util.HSSFColor.LAVENDER.index; } } /// <summary>
/// 粉黄色
/// </summary>
public static short LEMON_CHIFFON { get { return NPOI.HSSF.Util.HSSFColor.LEMON_CHIFFON.index; } } /// <summary>
/// 淡蓝色
/// </summary>
public static short LIGHT_BLUE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index; } } /// <summary>
/// 淡亮蓝色
/// </summary>
public static short LIGHT_CORNFLOWER_BLUE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_CORNFLOWER_BLUE.index; } } /// <summary>
/// 淡绿色
/// </summary>
public static short LIGHT_GREEN { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index; } } /// <summary>
/// 淡桔黄色
/// </summary>
public static short LIGHT_ORANGE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_ORANGE.index; } } /// <summary>
/// 淡蓝绿色
/// </summary>
public static short LIGHT_TURQUOISE { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_TURQUOISE.index; } } /// <summary>
/// 淡黄色
/// </summary>
public static short LIGHT_YELLOW { get { return NPOI.HSSF.Util.HSSFColor.LIGHT_YELLOW.index; } } /// <summary>
/// 绿黄色
/// </summary>
public static short LIME { get { return NPOI.HSSF.Util.HSSFColor.LIME.index; } } /// <summary>
/// 栗色
/// </summary>
public static short MAROON { get { return NPOI.HSSF.Util.HSSFColor.MAROON.index; } } /// <summary>
/// 橄榄绿色
/// </summary>
public static short OLIVE_GREEN { get { return NPOI.HSSF.Util.HSSFColor.OLIVE_GREEN.index; } } /// <summary>
/// 桔色
/// </summary>
public static short ORANGE { get { return NPOI.HSSF.Util.HSSFColor.ORANGE.index; } } /// <summary>
/// 白灰蓝色
/// </summary>
public static short PALE_BLUE { get { return NPOI.HSSF.Util.HSSFColor.PALE_BLUE.index; } } /// <summary>
/// 粉红色
/// </summary>
public static short PINK { get { return NPOI.HSSF.Util.HSSFColor.PINK.index; } } /// <summary>
/// 紫红色
/// </summary>
public static short PLUM { get { return NPOI.HSSF.Util.HSSFColor.PLUM.index; } } /// <summary>
/// 玫瑰红色
/// </summary>
public static short ROSE { get { return NPOI.HSSF.Util.HSSFColor.ROSE.index; } } /// <summary>
/// 高贵蓝
/// </summary>
public static short ROYAL_BLUE { get { return NPOI.HSSF.Util.HSSFColor.ROYAL_BLUE.index; } } /// <summary>
/// 海绿色
/// </summary>
public static short SEA_GREEN { get { return NPOI.HSSF.Util.HSSFColor.SEA_GREEN.index; } } /// <summary>
/// 天空蓝
/// </summary>
public static short SKY_BLUE { get { return NPOI.HSSF.Util.HSSFColor.SKY_BLUE.index; } } /// <summary>
/// 棕褐色
/// </summary>
public static short TAN { get { return NPOI.HSSF.Util.HSSFColor.TAN.index; } } /// <summary>
/// 茶色
/// </summary>
public static short TEAL { get { return NPOI.HSSF.Util.HSSFColor.TEAL.index; } } /// <summary>
/// 蓝绿色
/// </summary>
public static short TURQUOISE { get { return NPOI.HSSF.Util.HSSFColor.TURQUOISE.index; } } /// <summary>
/// 紫色
/// </summary>
public static short VIOLET { get { return NPOI.HSSF.Util.HSSFColor.VIOLET.index; } } /// <summary>
/// 白色
/// </summary>
public static short WHITE { get { return NPOI.HSSF.Util.HSSFColor.WHITE.index; } } /// <summary>
/// 黄色
/// </summary>
public static short YELLOW { get { return NPOI.HSSF.Util.HSSFColor.YELLOW.index; } } } /// <summary>
/// 针对excel的Oledb
/// </summary>
public class OleDbExcel
{
/// <summary>
/// OLEDB连接
/// </summary>
public OleDbConnection Connection
{
get;
set;
} /// <summary>
/// 用Oledb对Excel进行操作
/// 注：必须是标准表形式Excel内容
/// </summary>
/// <param name="excelFile">Excel文件</param>
public OleDbExcel(string excelFile)
{
string conStr = string.Empty;
FileInfo file = new FileInfo(excelFile);
if (!file.Exists) { throw new Exception("文件不存在"); }
string extension = file.Extension;
switch (extension)
{
case ".xls":
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
case ".xlsx":
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
} //链接Excel
Connection = new OleDbConnection(conStr);
} private List<string> tableNames; /// <summary>
/// 获取Excel内的Sheet名称
/// </summary>
public List<string> Sheets
{
get
{
if (tableNames == null)
{
try
{
tableNames = new List<string>();
//读取Excel里面的sheet名
Connection.Open(); DataTable schemaTable = Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); for (int i = ; i < schemaTable.Rows.Count; i++)
{
DataRow dr = schemaTable.Rows[i];
string tbName = dr["table_name"].ToString();
if (tbName[tbName.Length - ] == '$')
{
tableNames.Add(tbName);
}
}
Connection.Close();
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
}
return tableNames;
}
} /// <summary>
/// 查询出所有数据
/// </summary>
/// <param name="tableName">Sheet名称</param>
/// <returns>sheet内的所有数据</returns>
public DataSet QueryAll(string tableName)
{
try
{
DataSet excelData = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = new OleDbCommand();
adapter.SelectCommand.Connection = Connection;
adapter.SelectCommand.CommandText = string.Format("SELECT * FROM {0}", "[" + tableName + "]");
adapter.Fill(excelData);
return excelData;
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
} /// <summary>
/// 查询出所有数据
/// </summary>
/// <param name="tableIndex">Sheet序号（从0开始）</param>
/// <returns>sheet内的所有数据</returns>
public DataSet QueryAll(int tableIndex)
{
return QueryAll(Sheets[tableIndex]);
} /// <summary>
/// 利用Sql进行查询
/// </summary>
/// <param name="sql">Sql语句</param>
/// <returns>查询出的数据</returns>
public DataSet Query(string sql)
{
try
{
DataSet excelData = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, Connection);
adapter.Fill(excelData);
return excelData;
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
} /// <summary>
/// 利用Sql进行查询
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="param">查询参数</param>
/// <returns>查询出的数据</returns>
public DataSet Query(string sql, params OleDbParameter[] param)
{
try
{
DataSet excelData = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, Connection);
adapter.SelectCommand.Parameters.AddRange(param);
adapter.Fill(excelData);
return excelData;
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
} /// <summary>
/// 利用Sql进行数据操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>影响的行数</returns>
public int ExecuteSql(string sql)
{
try
{
Connection.Open();
OleDbCommand cmd = Connection.CreateCommand();
cmd.CommandText = sql;
int rtn = cmd.ExecuteNonQuery();
Connection.Close();
return rtn;
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
} /// <summary>
/// 利用Sql进行数据操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">执行参数</param>
/// <returns>影响的行数</returns>
public int ExecuteSql(string sql, params OleDbParameter[] param)
{
try
{
Connection.Open();
OleDbCommand cmd = Connection.CreateCommand();
cmd.CommandText = sql;
cmd.Parameters.AddRange(param);
int rtn = cmd.ExecuteNonQuery();
Connection.Close();
return rtn;
}
catch (Exception ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
throw new Exception(ex.Message, ex);
}
}
}
2、使用 后台
[HttpPost]
public void ExportExcel(string excelName, string tableStr, string imageAction = "")
{
tableStr = Microsoft.JScript.GlobalObject.unescape(tableStr);
//tableStr = "<table><tr><td>test1</td><td>test2</td></tr><tr><td>1</td><td>2</td></tr></table>";
DataTable dt = HtmlTableToDataTable(tableStr);
NPOIExcel excel = new NPOIExcel();
excel.WriteDataTable(dt, , , NPOIColor.SKY_BLUE); if (!string.IsNullOrWhiteSpace(imageAction))
{
var method = this.GetType().GetMethod(imageAction);
var param = method.GetParameters();
object[] objParam = new object[param.Length];
int i = ;
foreach (var pa in param)
{
if (pa.Name == "export")
{
objParam[i] = true;
continue;
}
string value = Request[pa.Name];
if (string.IsNullOrEmpty(value))
{
objParam[i] = pa.RawDefaultValue;
}
else
{
objParam[i] = Convert.ChangeType(value, pa.ParameterType);
}
i++;
} byte[] bytes = (byte[])method.Invoke(this, objParam);
excel.AddPicture(bytes, dt.Rows.Count + , ); }
MemoryStream ms = excel.Save();
ms.Position = ;
Response.Clear();
Response.ContentType = "application/-excel";
Response.HeaderEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(excelName + ".xls"));
byte[] buffer = new byte[ms.Length];
ms.Read(buffer, , buffer.Length);
Response.AddHeader("Content-Length", buffer.Length.ToString());
Response.BinaryWrite(buffer);
Response.Flush(); } private DataTable HtmlTableToDataTable(string tableStr)
{
tableStr = tableStr.Replace("\n", "");
tableStr = tableStr.Replace("\t", "");
Regex re = new Regex("<BR>", RegexOptions.IgnoreCase);
tableStr = re.Replace(tableStr, "\r\n");
re = new Regex(" ", RegexOptions.IgnoreCase);
XmlDocument doc = new XmlDocument();
doc.LoadXml(tableStr); var trs = doc.GetElementsByTagName("tr"); DataTable rtn = new DataTable(); for (int i = ; i < trs.Count; i++)
{
for (int j = ; j < trs[i].ChildNodes.Count; j++)
{
var td = trs[i].ChildNodes[j].InnerText.Trim();
td = td.Replace(" ", "");
re.Replace(td, " ");
if (rtn.Columns.Count <= j)
{
rtn.Columns.Add(td);
}
else
{
if (rtn.Rows.Count <= i - )
{
rtn.Rows.Add(rtn.NewRow());
}
rtn.Rows[i - ][j] = td;
}
}
} return rtn; }
3、前台
按钮
<a href="javascript:void(0)" onclick="ExportExcel('AssManageQueryClose','exportTable')" style="font-size: 13px;">@Html.Lang("输出报表", "Export Report")</a>
table：
<table class="dataList" id="exportTable">
<tr class="title">
<td>
title</td>
</tr> @foreach (var item in Model)
{
<tr class="item">
<td>
Content</td>
</tr>
}
</table>
js：
//导出Excel
function ExportExcel(excelName, tableID) {
if ($("#" + excelName).length <= ) {
var table = "<table>" + $("#" + tableID).html() + "</table>";
table = escape(table);
var form = "<form id='" + excelName + "' action='@Url.Action("ExportExcel")' method='post'>";
form += "<input type='hidden' id='excelName' name='excelName' value='" + excelName + "' />";
form += "<input type='hidden' id='tableStr' name='tableStr' value='" + table + "' />";
form += "</form>";
$(form).appendTo("body");
}
$("#" + excelName).submit();
}