标签：date inf erro on() continue 维护 pen sts 路径
最近在维护公司的十年老项目。写了一些nopi的方法，记录一下。
///
/// DataTable转成Excel表格
///
///
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 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
///
/// 获取带数据的模版表格
///
/// Key为数据库的列名，Value为表格中显示的列名称。
/// 模版文件的路径
/// 数据库中的数据
///
public static byte[] GetDataTempleteExcel(Dictionary 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 dbColumnNames = new List();
var columnRow = sheet1.GetRow(0);
for (int i = 0; i ()
{
{ "Name","名称" },
{ "Age","年龄" },
};
DataTable dt = GetDataTable();
dt.TableName = "导出的Excel";
DataTableToExcel(columns,Server.MapPath("~/template/test.xls"),dt);
///
/// 获取excel单元格中的值
///
///
///
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的控件交织在一起的，所以这个也就记录下，不需要看懂。*/
///
/// 上传Excel并且返回Excel中的数据
///
/// 表列名和Excel中的列名字典
/// 上传文件的控件
/// 1:是否上传成功；2:上传成功后的数据；3:上传失败的错误消息；4:当前Excel的对象；5.文件上传后的地址
protected Tuple GetExcelDataTable(Dictionary columns, FileUpload fuInvoiceFile)
{
#region 文件校验、上传到服务器
string fileName = fuInvoiceFile.ShortFileName.Replace(":", "_").Replace(" ", "_").Replace("\\", "_").Replace("/", "_");
if (fileName.LastIndexOf(‘.‘) (false, null, "- 文件错误！", null, null);
}
string fileExt = fileName.Substring(fileName.LastIndexOf("."));
// 文件验证
if (fileExt != ".xls" && fileExt != ".xlsx")
{
return new Tuple(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 (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(false, null, "- 列数不正确，请检查Excel文件！", null, null);
}
DataTable dt = new DataTable();
var columnsKeys = columns.Keys.ToArray();
for (int j = 0, clos = columnsKeys.Length; j (false, null, $"- 列名称[{headerText}]无法匹配，请使用系统提供的Excel模版导入！", null, null);
}
}
#endregion
#region 读取数据
for (int i = 1, rows = sheet.PhysicalNumberOfRows; i
/// 删除DataTable下方的空数据行
///
/// 要删除下方空数据行的数据列表
/// 有效列开始下标
/// 有效列结束下标
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 (true, dt, null, workbook, filePath);
}
///
/// 执行Excel导入错误校验
///
/// 需要写入的Excel对象
/// 错误消息集合
/// Excel文件地址
///
protected Tuple ExeExcelErrorMessage(Workbook workbook, List 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 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(hasError, navigateUrl);
}
C# NOPI常用方法（记录老项目的方法）
标签：date inf erro on() continue 维护 pen sts 路径
原文地址：https://www.cnblogs.com/cluyun/p/14752228.html