最近在维护公司的十年老项目。写了一些nopi的方法，记录一下。
/// <summary>
/// DataTable转成Excel表格
/// </summary>
/// <returns></returns>
public static byte[] DataTableToExcel(DataTable dt)
{
using (HSSFWorkbook hssfworkbook = new HSSFWorkbook())//建立Excel 2003对象
using (HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet("sheet1"))//新建一个名称为sheet1的工作簿
using (MemoryStream stream = new MemoryStream()) //二进制流中介
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();//配置文件属性
dsi.Company = string.Empty;
dsi.Category = string.Empty;//类别
dsi.Manager = string.Empty;//管理者
hssfworkbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = string.Empty;//主题
si.Title = string.Empty;//标题
si.ApplicationName = string.Empty;//不知道是啥
si.Author = string.Empty;//作者
si.LastAuthor = string.Empty;//上一次保存者
si.Comments = string.Empty;//备注
si.CreateDateTime = DateTime.Now;
hssfworkbook.SummaryInformation = si;
//Func<HSSFWorkbook, string, short, HSSFCellStyle> setstyle =
// (hssfwb, fontname, fontsize)
// =>
// {
// HSSFFont font = (HSSFFont)hssfwb.CreateFont();
// HSSFCellStyle style = (HSSFCellStyle)hssfwb.CreateCellStyle();
// style.SetFont(font);
// font.FontName = fontname;
// font.FontHeightInPoints = fontsize;
// return style;
// };
////表格设置
//sheet.SetColumnWidth(1, 30 * 256);//第二列较宽
//sheet.DefaultColumnWidth = 13;
//HSSFCellStyle style0 = setstyle(hssfworkbook, "微软雅黑", 14);
//HSSFCellStyle style1 = setstyle(hssfworkbook, "微软雅黑", 12);
//设置列名
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
Cell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
//cell.CellStyle = style0;
}
//单元格赋值
for (int i = 0; i < dt.Rows.Count; i++)
{
Row row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
Cell cell = row1.CreateCell(j);
var value = dt.Rows[i][j];
if (value != null && value != DBNull.Value)
{
var valueType = value.GetType();
if (valueType == typeof(double) || valueType == typeof(decimal) || valueType == typeof(float) ||
valueType == typeof(double?) || valueType == typeof(decimal?) || valueType == typeof(float?) ||
valueType == typeof(byte) || valueType == typeof(int) || valueType == typeof(long) ||
valueType == typeof(byte?) || valueType == typeof(int?) || valueType == typeof(long?)
)
{
cell.SetCellValue(Convert.ToDouble(value));
}
else
{
cell.SetCellValue(value.ToString());
}
}
else
{
cell.SetCellValue(string.Empty);
}
//cell.CellStyle = style1;
}
}
hssfworkbook.Write(stream);
return stream.ToArray();
}
}
//使用
DataTable dt = GetDataTable();
dt.TableName = "导出的Excel";
DataTableToExcel(dt);
/// <summary>
/// 获取带数据的模版表格
/// </summary>
/// <param name="columns">Key为数据库的列名，Value为表格中显示的列名称。</param>
/// <param name="templetePath">模版文件的路径</param>
/// <param name="data">数据库中的数据</param>
/// <returns></returns>
public static byte[] GetDataTempleteExcel(Dictionary<string, string> columns, string templetePath, DataTable data)
{
using (FileStream fs = new FileStream(templetePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
using (HSSFWorkbook workbook = new HSSFWorkbook(fs))
using (HSSFSheet sheet1 = workbook.GetSheetAt(0) as HSSFSheet)
using (MemoryStream stream = new MemoryStream())
{
HSSFRow headerRow = sheet1.GetRow(0) as HSSFRow;//获取sheet的首行
int cellCount = headerRow.LastCellNum; //一行最后一个方格的编号 即总的列数
int cellRowsCount = sheet1.LastRowNum + 1;//这个好像是获取的下标，所以加一。
if (cellCount != columns.Count)
{
throw new ArgumentException(nameof(columns));
}
if (cellRowsCount < 1)
{
throw new ArgumentNullException(nameof(cellRowsCount));
}
//赋值前清除下数据
for (int i = 1; i < cellRowsCount; i++) if (sheet1.GetRow(i) == null) continue; else sheet1.RemoveRow(sheet1.GetRow(i));
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();//配置文件属性
dsi.Company = string.Empty;
dsi.Category = string.Empty;//类别
dsi.Manager = string.Empty;//管理者
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = string.Empty;//主题
si.Title = string.Empty;//标题
si.ApplicationName = string.Empty;//不知道是啥
si.Author = string.Empty;//作者
si.LastAuthor = string.Empty;//上一次保存者
si.Comments = string.Empty;//备注
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
DataRow[] dataRows = data.AsEnumerable().ToArray();
string[] columnNames = columns.Keys.ToArray();
//数据库中的列名,按照Excel中的列顺序排列。
List<string> dbColumnNames = new List<string>();
var columnRow = sheet1.GetRow(0);
for (int i = 0; i < cellCount; i++)
{
var excelColumnName = columnRow.GetCell(i).ToString().Trim();
foreach (var colKey in columnNames)
{
if (string.Equals(columns[colKey], excelColumnName, StringComparison.OrdinalIgnoreCase))
{
dbColumnNames.Add(colKey);
break;
}
}
}
//转成数组使用，因为感觉这样后面使用快一点。
string[] dbColumnNamesArray = dbColumnNames.ToArray();
if (dbColumnNamesArray.Length != cellCount)
{
throw new ArgumentException(nameof(dbColumnNamesArray));
}
//单元格赋值
for (int i = 0, c = dataRows.Length; i < c; i++)
{
Row row1 = sheet1.CreateRow(i + 1);
for (int j = 0; j < cellCount; j++)
{
Cell cell = row1.CreateCell(j);
var value = dataRows[i][dbColumnNamesArray[j]];
if (value != null && value != DBNull.Value)
{
cell.SetCellValue(value.ToString());
}
else
{
cell.SetCellValue(string.Empty);
}
}
}
workbook.Write(stream);
return stream.ToArray();
}
}
//使用
var columns = new Dictionary<string, string>()
{
{ "Name","名称" },
{ "Age","年龄" },
};
DataTable dt = GetDataTable();
dt.TableName = "导出的Excel";
DataTableToExcel(columns,Server.MapPath("~/template/test.xls"),dt);
/// <summary>
/// 获取excel单元格中的值
/// </summary>
/// <param name="cellObj"></param>
/// <returns></returns>
public static string GetExcelCellValue(Cell cellObj, NPOI.SS.UserModel.Workbook workbook)
{
if (cellObj == null) return string.Empty;
string cellValue = null;
switch (cellObj.CellType)
{
case CellType.Unknown:
break;
case CellType.NUMERIC:
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (DateUtil.IsCellDateFormatted(cellObj))//日期类型
{
var date = cellObj.DateCellValue;
if (date != default(DateTime) && date != DateTime.MinValue)
{
cellValue = date.ToString("yyyy/MM/dd HH:mm:ss");
}
}
else//其他数字类型
{
cellValue = cellObj.NumericCellValue.ToString();
}
break;
case CellType.STRING:
cellValue = cellObj.StringCellValue;
break;
case CellType.FORMULA:
var eva = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workbook);
cellValue = eva.Evaluate(cellObj).StringValue;
break;
case CellType.BLANK:
break;
case CellType.BOOLEAN:
cellValue = cellObj.BooleanCellValue.ToString();
break;
case CellType.ERROR:
cellValue = cellObj.ErrorCellValue.ToString();
break;
default:
break;
}
cellValue = string.IsNullOrWhiteSpace(cellValue) ? string.Empty : cellValue.Trim();
return cellValue;
}
/*下面的代码虽然是我写的，但是我不是很清楚啥作用了。而且是和业务以及asp的控件交织在一起的，所以这个也就记录下，不需要看懂。*/
/// <summary>
/// 上传Excel并且返回Excel中的数据
/// </summary>
/// <param name="columns">表列名和Excel中的列名字典</param>
/// <param name="fuInvoiceFile">上传文件的控件</param>
/// <returns>1:是否上传成功；2:上传成功后的数据；3:上传失败的错误消息；4:当前Excel的对象；5.文件上传后的地址</returns>
protected Tuple<bool, DataTable, string, Workbook, string> GetExcelDataTable(Dictionary<string, string> columns, FileUpload fuInvoiceFile)
{
#region 文件校验、上传到服务器
string fileName = fuInvoiceFile.ShortFileName.Replace(":", "_").Replace(" ", "_").Replace("\", "_").Replace("/", "_");
if (fileName.LastIndexOf('.') <= 0)
{
return new Tuple<bool, DataTable, string, Workbook, string>(false, null, "- 文件错误！", null, null);
}
string fileExt = fileName.Substring(fileName.LastIndexOf("."));
// 文件验证
if (fileExt != ".xls" && fileExt != ".xlsx")
{
return new Tuple<bool, DataTable, string, Workbook, string>(false, null, "- 只能选择Excel文件导入！", null, null);
}
string onlyFileName = fileName.Substring(0, fileName.LastIndexOf(".") + 1);
string dir = $@"{Server.MapPath(@"upfile")}{DateTime.Now:yyyy-MM-dd}";
if (!Directory.Exists(dir)) Directory.CreateDirectory(dir); // 创建日期目录
string filePath = $"{dir}{onlyFileName}{Guid.NewGuid().ToString().Replace("-", string.Empty)}{fileExt}"; // 获得文件保存在服务器上的路径
// 保存文件到服务器
fuInvoiceFile.PostedFile.SaveAs(filePath);
#endregion
#region 实例化一个Excel
FileStream fs = new FileStream(filePath, FileMode.Open);
NPOI.SS.UserModel.Workbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
fs.Dispose();
NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(0);
if (sheet == null)
{
sheet = workbook.CreateSheet("Sheet1");
}
#endregion
#region 文件校验
if (sheet.PhysicalNumberOfRows < 2)
{
return new Tuple<bool, DataTable, string, Workbook, string>(false, null, "- 未获取到数据，请检查数据文件！", null, null);
}
var headerRow = sheet.GetRow(0) as NPOI.HSSF.UserModel.HSSFRow;//获取sheet的首行
int cellCount = headerRow.LastCellNum; //一行最后一个方格的编号 即总的列数
//int cellRowsCount = sheet.LastRowNum + 1;//这个好像是获取的下标，所以加一。
if (cellCount != columns.Count)
{
return new Tuple<bool, DataTable, string, Workbook, string>(false, null, "- 列数不正确，请检查Excel文件！", null, null);
}
DataTable dt = new DataTable();
var columnsKeys = columns.Keys.ToArray();
for (int j = 0, clos = columnsKeys.Length; j < clos; j++)
{
var headerText = headerRow.GetCell(j).ToString();
bool isNotExits = true;
foreach (var headerKey in columnsKeys)
{
var columnName = columns[headerKey];
if (columnName == headerText)
{
dt.Columns.Add(headerKey, typeof(string));
isNotExits = false;
break;
}
}
if (isNotExits)
{
return new Tuple<bool, DataTable, string, Workbook, string>(false, null, $"- 列名称[{headerText}]无法匹配，请使用系统提供的Excel模版导入！", null, null);
}
}
#endregion
#region 读取数据
for (int i = 1, rows = sheet.PhysicalNumberOfRows; i < rows; i++)
{
var dtRow = dt.NewRow();
for (int j = 0, clos = columnsKeys.Length; j < clos; j++)
{
Row excelRow = sheet.GetRow(i);
if (excelRow == null) excelRow = sheet.CreateRow(i);
Cell cellObj = excelRow.GetCell(j);
if (cellObj == null) cellObj = excelRow.CreateCell(j);
dtRow[j] = DataTableRenderToExcel.GetExcelCellValue(cellObj, workbook);
}
dt.Rows.Add(dtRow);
}
#endregion
#region 数据处理
/// <summary>
/// 删除DataTable下方的空数据行
/// </summary>
/// <param name="dt">要删除下方空数据行的数据列表</param>
/// <param name="startIndex">有效列开始下标</param>
/// <param name="endIndex">有效列结束下标</param>
void DeleteDataTableBelowNullData(DataTable dtd, int startIndex, int endIndex)
{
if (dtd == null) return;
int colLength = endIndex - startIndex + 1;
for (int i = dtd.Rows.Count - 1; i >= 0; i--)
{
var row = dtd.Rows[i].ItemArray;
int j = 0;
for (int y = startIndex; y <= endIndex; y++)
{
if (string.IsNullOrWhiteSpace(row?[y]?.ToString()))
{
j++;
}
else
{
break;
}
}
if (j == colLength)
{
dtd.Rows.RemoveAt(i);
}
else
{
break;
}
}
}
DeleteDataTableBelowNullData(dt, 1, dt.Columns.Count - 1);
#endregion
return new Tuple<bool, DataTable, string, Workbook, string>(true, dt, null, workbook, filePath);
}
/// <summary>
/// 执行Excel导入错误校验
/// </summary>
/// <param name="workbook">需要写入的Excel对象</param>
/// <param name="messages">错误消息集合</param>
/// <param name="filePath">Excel文件地址</param>
/// <returns></returns>
protected Tuple<bool, string> ExeExcelErrorMessage(Workbook workbook, List<string> messages, string filePath)
{
var sheet = workbook.GetSheetAt(0);
Row excelRowHead = sheet.GetRow(0);
int errorColIndex = excelRowHead.LastCellNum + 1;
if (excelRowHead == null) excelRowHead = sheet.CreateRow(0);
Cell excelCellErrorTipHead = excelRowHead.GetCell(errorColIndex);
if (excelCellErrorTipHead == null) excelCellErrorTipHead = excelRowHead.CreateCell(errorColIndex);
excelCellErrorTipHead.SetCellValue("导入提示");
bool hasError = false;
for (int i = 0, count = messages.Count; i < count; i++)
{
string errorMessage = messages[i];
Row excelRow = sheet.GetRow(i + 1);
if (errorMessage.Length > 0)
{
Cell cell = excelRow.GetCell(errorColIndex);
if (cell == null) cell = excelRow.CreateCell(errorColIndex);
cell.SetCellValue(errorMessage);
hasError = true;
}
}
string navigateUrl = null;
if (hasError)
{
navigateUrl = string.Format("~/upfile/{0}/{1}", DateTime.Now.ToString("yyyy-MM-dd"), filePath.Substring(filePath.LastIndexOf('\') + 1));
var fs = new FileStream(Server.MapPath(navigateUrl), FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
}
else
{
if (File.Exists(filePath)) File.Delete(filePath);
}
workbook.Dispose();
return new Tuple<bool, string>(hasError, navigateUrl);
}