1.添加nuget包管理NPOI引用。
2.前台页面 AssetsListNew.aspx
div class="search_style">
<ul class="search_content clearfix" style="padding-left: 2rem;">
<li>
<label class="l_f">导入：</label>
<span class="add_name" style="float: right;">
<a href="AssetsListNew.aspx?action=down" id="anone1" class=" layui-btn ">下载模板</a>
</span>
<li>
<input type="file" id="fileUpload" runat="server" class="btn btn-warning" style="background-color: #abbac3!important; border-color: #abbac3;" />
</li>
<li>
<button type="submit" class=" layui-btn" runat="server" onserverclick="btnImport_Click">导入</button>
</li>
</ul>
</div>3.公共工具类操作excel Common ExcelHelper.cs
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace Common
{
public class ExcelHelper
{
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static void Export(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
if (strSheetName == "")
{
strSheetName = "Sheet";
}
using (MemoryStream ms = Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// DataTable导出到Excel文件（无表头）另外的是有表头的
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static void MyExport(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
if (strSheetName == "")
{
strSheetName = "Sheet";
}
MemoryStream getms = new MemoryStream();
#region 为getms赋值
if (oldColumnNames.Length != newColumnNames.Length)
{
getms = new MemoryStream();
}
HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet(strSheetName);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://....../";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
if (HttpContext.Current.Session["realname"] != null)
{
si.Author = HttpContext.Current.Session["realname"].ToString();
}
else
{
if (HttpContext.Current.Session["username"] != null)
{
si.Author = HttpContext.Current.Session["username"].ToString();
}
} //填加xls文件作者信息
si.ApplicationName = "NPOI"; //填加xls文件创建程序信息
si.LastAuthor = "OA系统"; //填加xls文件最后保存者信息
si.Comments = "OA系统自动创建文件"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = strHeaderText; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
int[] arrColWidth = new int[oldColumnNames.Length];
for (int i = 0; i < oldColumnNames.Length; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
}
/*
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
* */
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < oldColumnNames.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
/*
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
* */
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
}
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
/*
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
* */
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
//foreach (DataColumn column in dtSource.Columns)
for (int i = 0; i < oldColumnNames.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[oldColumnNames[i]].ToString();
switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
sheet = null;
workbook = null;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
getms = ms;
}
#endregion
using (MemoryStream ms = getms)
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
if (oldColumnNames.Length != newColumnNames.Length)
{
return new MemoryStream();
}
//IWorkbook workbook;
//string fileExt = Path.GetExtension(strSheetName).ToLower();
//if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
//if (workbook == null) { return null; }
//ISheet sheet = string.IsNullOrEmpty(dtSource.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dtSource.TableName);
//HSSFWorkbook workbook = new HSSFWorkbook();
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(strSheetName);// workbook.CreateSheet();
//ISheet sheet = workbook.CreateSheet(strSheetName);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://....../";
//workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
if (HttpContext.Current.Session["realname"] != null)
{
si.Author = HttpContext.Current.Session["realname"].ToString();
}
else
{
if (HttpContext.Current.Session["username"] != null)
{
si.Author = HttpContext.Current.Session["username"].ToString();
}
} //填加xls文件作者信息
si.ApplicationName = "NPOI"; //填加xls文件创建程序信息
si.LastAuthor = "OA系统"; //填加xls文件最后保存者信息
si.Comments = "OA系统自动创建文件"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = strHeaderText; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
int[] arrColWidth = new int[oldColumnNames.Length];
for (int i = 0; i < oldColumnNames.Length; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
}
/*
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
* */
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < oldColumnNames.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
/*
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
* */
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
/*
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
* */
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
//foreach (DataColumn column in dtSource.Columns)
for (int i = 0; i < oldColumnNames.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[oldColumnNames[i]].ToString();
switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
//sheet.Dispose();
sheet = null;
workbook = null;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return ms;
}
}
/// <summary>
/// WEB导出DataTable到Excel
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">文件名</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
{
ExportByWeb(dtSource, strHeaderText, strFileName, "sheet");
}
/// <summary>
/// WEB导出DataTable到Excel
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">输出文件名，包含扩展名</param>
/// <param name="oldColumnNames">要导出的DataTable列数组</param>
/// <param name="newColumnNames">导出后的对应列名</param>
public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string[] oldColumnNames, string[] newColumnNames)
{
ExportByWeb(dtSource, strHeaderText, strFileName, "sheet", oldColumnNames, newColumnNames);
}
/// <summary>
/// WEB导出DataTable到Excel
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">输出文件名</param>
/// <param name="strSheetName">工作表名称</param>
public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName)
{
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
//生成列
string columns = "";
for (int i = 0; i < dtSource.Columns.Count; i++)
{
if (i > 0)
{
columns += ",";
}
columns += dtSource.Columns[i].ColumnName;
}
curContext.Response.BinaryWrite(Export(dtSource, strHeaderText, strSheetName, columns.Split(','), columns.Split(',')).GetBuffer());
curContext.Response.End();
}
/// <summary>
/// 导出DataTable到Excel
/// </summary>
/// <param name="dtSource">要导出的DataTable</param>
/// <param name="strHeaderText">标题文字</param>
/// <param name="strFileName">文件名，包含扩展名</param>
/// <param name="strSheetName">工作表名</param>
/// <param name="oldColumnNames">要导出的DataTable列数组</param>
/// <param name="newColumnNames">导出后的对应列名</param>
public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
curContext.Response.BinaryWrite(Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames).GetBuffer());
curContext.Response.End();
}
/// <summary>读取excel
/// 默认第一行为表头，导入第一个工作表
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable Import(string strFileName)
{
DataTable dt = new DataTable();
//HSSFWorkbook hssfworkbook;
XSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
//XSSFRow row;
//hssfworkbook = new HSSFWorkbook(file);
}
ISheet sheet = hssfworkbook.GetSheetAt(0);
//ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(0);
int cellCount = 0;
if (headerRow!=null)
{
cellCount= headerRow.LastCellNum;
}
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
return dt;
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="strFileName">Excel文件全路径(服务器路径)</param>
/// <param name="SheetName">要获取数据的工作表名称</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(string strFileName, string SheetName, int HeaderRowIndex)
{
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
//IWorkbook workbook = new HSSFWorkbook(file);
IWorkbook workbook = new XSSFWorkbook(file);
ISheet sheet = workbook.GetSheet(SheetName);
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
}
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="strFileName">Excel文件全路径(服务器路径)</param>
/// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex)
{
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = new HSSFWorkbook(file);
string SheetName = workbook.GetSheetName(SheetIndex);
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
}
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="ExcelFileStream">Excel文件流</param>
/// <param name="SheetName">要获取数据的工作表名称</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
{
IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
ExcelFileStream.Close();
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="ExcelFileStream">Excel文件流</param>
/// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
{
IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
ExcelFileStream.Close();
string SheetName = workbook.GetSheetName(SheetIndex);
return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
}
/// <summary>
/// 从Excel中获取数据到DataTable
/// </summary>
/// <param name="workbook">要处理的工作薄</param>
/// <param name="SheetName">要获取数据的工作表名称</param>
/// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
/// <returns></returns>
public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)
{
ISheet sheet = workbook.GetSheet(SheetName);
DataTable table = new DataTable();
try
{
IRow headerRow = sheet.GetRow(HeaderRowIndex);
int cellCount = 0;
if (headerRow!=null)
{
cellCount= headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
}
int rowCount = sheet.LastRowNum;
#region 循环各行各列,写入数据到DataTable
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = null;
}
else
{
//dataRow[j] = cell.ToString();
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = null;
break;
case CellType.Boolean:
dataRow[j] = cell.BooleanCellValue;
break;
case CellType.Numeric:
dataRow[j] = cell.ToString();
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
case CellType.Error:
dataRow[j] = cell.ErrorCellValue;
break;
case CellType.Formula:
default:
dataRow[j] = "=" + cell.CellFormula;
break;
}
}
}
table.Rows.Add(dataRow);
//dataRow[j] = row.GetCell(j).ToString();
}
#endregion
}
catch (System.Exception ex)
{
table.Clear();
table.Columns.Clear();
table.Columns.Add("出错了");
DataRow dr = table.NewRow();
dr[0] = ex.Message;
table.Rows.Add(dr);
return table;
}
finally
{
//sheet.Dispose();
workbook = null;
sheet = null;
}
#region 清除最后的空行
for (int i = table.Rows.Count - 1; i > 0; i--)
{
bool isnull = true;
for (int j = 0; j < table.Columns.Count; j++)
{
if (table.Rows[i][j] != null)
{
if (table.Rows[i][j].ToString() != "")
{
isnull = false;
break;
}
}
}
if (isnull)
{
table.Rows[i].Delete();
}
}
#endregion
return table;
}
}
}4.前台页面对应的后台代码 AssetsListNew.aspx.cs 调用Common的excel工具类实现导入excel操作
using Common;
using DAL;
using DQPA.BLL;
using DQPA.IBLL;
using DQPA.MODEL;
using Gma.QrCodeNet.Encoding;
using Gma.QrCodeNet.Encoding.Windows.Render;
using Maticsoft.DBUtility;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace DQPA.PAManager
{
public partial class AssetsListNew : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Auxiliary aux = new Auxiliary();
if (Session["UId"] == null || string.IsNullOrEmpty(Server.UrlDecode(Session["UId"].ToString())))
{
Response.Redirect("UserLogin.aspx");
return;
}
if (!IsPostBack)
{
var action = Request.QueryString["action"];
var id = Request.QueryString["id"];
switch (action)
{
case "list":
loadAssets();
break;
case "delete":
DelAssets();
break;
case "upload":
//var fileup = test8.PostedFile;
//Upload(fileup);
break;
case "make":
MakeORCode(id);
break;
case "down":
DownloadOperation();
break;
default:
break;
}
}
//var a = hidSearch.Value;
//var b = txtCondition.Value;
//var download = Request.QueryString["down"];
//if (download != null)
//{
// var downtype = download.ToString();
// switch (downtype)
// {
// case "1":
// DownloadOperation();
// break;
// default:
// break;
// }
//}
}
protected void loadAssets()
{
try
{
//DataTable dt = new DataTable();
int count;
StringBuilder strwhere = new StringBuilder();
IAssetsBll assetsBll = new AssetsBll();
strwhere.Append(" 1=1");//and ylyid in (select id from M_yanglaoyuan where type ='" + type + "')
strwhere.Append(" and ISNULL(IsDelete,0) <> 1 ");
//if (!string.IsNullOrEmpty(txtCondition.Value))
//{
// where.Append(" and Number like '%" + txtCondition.Value + "%' or type like '%" + txtCondition.Value + "%' or brand like '%" + txtCondition.Value + "%' ");
//}
var searchC = Request.QueryString["selectc"];
var txtsearch = Request.QueryString["txtselect"];
var startTime = Request.QueryString["start"];
var endTime = Request.QueryString["end"];
if (searchC != null && !string.IsNullOrEmpty(searchC))
{
if (txtsearch != null && !string.IsNullOrEmpty(txtsearch))
{
var whereSearch = string.Format(@" and {0} like '%" + txtsearch + "%'", searchC);
strwhere.Append(whereSearch);
}
}
if (!string.IsNullOrEmpty(startTime))
{
if (!string.IsNullOrEmpty(endTime))
{
DateTime start1 = DateTime.ParseExact(startTime, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture);
//DateTime fStart = start1.AddDays(1);
DateTime fStart = DateTime.ParseExact(endTime, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture).AddDays(1);
//if (start1 <= fStart)
//{
strwhere.Append(" and PurchaseTime >= '" + start1 + "' and PurchaseTime < '" + fStart + "'");
//}
}
}
//if (!string.IsNullOrEmpty(start.Value))
//{
// if (!string.IsNullOrEmpty(end.Value))
// {
// DateTime start1 = DateTime.ParseExact(start.Value, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture);
// //DateTime fStart = start1.AddDays(1);
// DateTime fStart = DateTime.ParseExact(end.Value, "yyyy-MM-dd", System.Globalization.CultureInfo.CurrentCulture).AddDays(1);
// strwhere.Append(" and PurchaseTime >= '" + start1 + "' and PurchaseTime < '" + fStart + "'");
// }
//}
//if (hidSearch.Value != null && !string.IsNullOrEmpty(hidSearch.Value))
//{
// if (txtCondition.Value != null && !string.IsNullOrEmpty(txtCondition.Value))
// {
// var whereSearch = string.Format(@" and {0} like '%" + txtCondition.Value + "%'", hidSearch.Value, txtCondition.Value);
// strwhere.Append(whereSearch);
// }
//}
int page = Request.Form["page"] != "" ? Convert.ToInt32(Request.Form["page"]) : 0;
int size = Request.Form["rows"] != "" ? Convert.ToInt32(Request.Form["rows"]) : 0;
string sort = Request.Form["sort"] != "" ? Request.Form["sort"] : "";
string order = Request.Form["order"] != "" ? Request.Form["order"] : "";
var dt = assetsBll.DataPage("Assets", "*,CONVERT(varchar(100), PurchaseTime, 23) as PurchaseTime1", "addtime", "desc", size, page, strwhere.ToString(), out count);
string json = string.Empty;
//if (dt != null && dt.Rows.Count > 0 && dt.Rows[0] != null)
//{
json = JsonHelper.CreateJsonParameters(dt, true, count);
//}
//else
//{
// json = JsonHelper.CreateJsonParameters(null, false, count);
//}
Response.Write(json);
Response.End();
}
catch (Exception ex)
{
throw ex;
}
}
public void DelAssets()
{
var id = Request.QueryString["id"];
if (id != null)
{
string msg = "";
IAssetsBll bll = new AssetsBll();
if (!string.IsNullOrEmpty(id))
{
bool res = bll.DeleteDetail(Convert.ToInt32(id), out msg);
if (res)
{
Response.Write("{\"code\": 0,\"msg\": \"\",\"data\": {\"src\": \"\"}}");
Response.End();
//Response.Write("{\"code\": 0,\"msg\": \"\",\"data\": {\"src\": \"\"}}");
}
else
{
Response.Write("{\"code\": 1,\"msg\": \"\",\"data\": {\"src\": \"\"}}");
Response.End();
}
}
}
else
{
Response.Write("{\"code\": 1,\"msg\": \"\",\"data\": {\"src\": \"\"}}");
Response.End();
}
}
#region 下载上传模板
protected void DownloadOperation()
{
//string filePath = Server.MapPath("../excel") + @"\" + "电脑盘点导入模板.xls" + "";
string filePath = Server.MapPath("../excel") + @"\" + "电脑盘点导入模板.xlsx" + "";
byte[] data = File.ReadAllBytes(filePath);
MemoryStream stream = new MemoryStream(data);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", "电脑盘点导入模板.xlsx"));
Response.BinaryWrite(stream.ToArray());
stream.Close();
stream.Dispose();
//File.Delete(filePath);
Response.End();
}
#endregion
#region 导入excel .xlsx
protected void btnImport_Click(object sender, EventArgs e)
{
var fileup = fileUpload.PostedFile;
//InsetData(Upload(fileup));
//InsetData(fileup);
// 说明：导入的方法
if (fileUpload == null)
{
Response.Write("<script>alert('请先选择Excel文件！');window.location.href='AssetsListNew.aspx'</script>");
}
else
{
string fileUrl = "";
#region 文件上传
//try
//{
//}
//catch
//{
// Response.Write("<script>alert('数据上传失败，请重新导入');window.location.href='table.aspx'</script>");
// res = false;
//}
//全名
string excelFile = this.fileUpload.PostedFile.FileName;
//获取文件名（不包括扩展名）
string fileName = Path.GetFileNameWithoutExtension(fileup.FileName);
if (fileName == "" || fileName == null)
{
Response.Write("<script>alert('请先选择Excel文件！');window.location.href='AssetsListNew.aspx'</script>");
}
else
{
//扩展名
string extentionName = excelFile.Substring(excelFile.LastIndexOf(".") + 1);
if (extentionName != "xlsx")
{
Response.Write("<script>alert('您上传的不是.xlsx文件！');window.location.href='AssetsListNew.aspx'</script>");
}
else
{
//浏览器安全性限制 无法直接获取客户端文件的真实路径，将文件上传到服务器端 然后获取文件源路径
#region 设置上传路径将文件保存到服务器
string dateTime = DateTime.Now.Date.ToString("yyyyMMdd");
string time = DateTime.Now.ToShortTimeString().Replace(":", "");
string newFileName = dateTime + time + DateTime.Now.Millisecond.ToString() + ".xlsx";
//自己创建的文件夹 位置随意 合理即可
fileUrl = Server.MapPath("..\\excel") + "\\" + newFileName;
//fileUrl = Path.Combine(Request.MapPath("~/excel"), Path.GetFileName(fileup.FileName));
fileup.SaveAs(fileUrl);
//DataTable dtData = ExcelHelper.Import(fileUrl);
//得到EXCEL的第二种方法(第一个参数是文件流,第二个是excel标签名,第三个是第几行开始读0算第一行)
DataTable dt = ExcelHelper.RenderDataTableFromExcel(fileUrl, "Sheet1", 0);
//Response.Write("<script>alert('已经上传到服务器文件夹')</script>");
//return fileUrl;
//3.删除服务器上的excel文件 获取路径并且删除
//string FilePath = Server.MapPath(fileUrl); // 必须转化以下文件路径，不能直接delete("image/4jpg");
File.Delete(fileUrl);
#endregion
#region dt导入数据库
//3：从System.Data.DataTable导入数据到数据库
//@param System.Data.DataTable dt
IAssetsBll assetBll = new AssetsBll();
IUserBll userBll = new UserBll();
int i = 0;
int num = 1;
string numList = string.Empty;
bool result = false;
var addTime = DateTime.Now;
var updateTime = DateTime.Now;
if (dt != null && dt.Rows.Count > 0 && dt.Rows[0] != null)
{
//查找现在数据表数据
var assetsList = assetBll.SearchList(string.Format(@"select * from assets withnolck"));
string msg = "";
foreach (DataRow dr in dt.Rows)
{
try
{
num += 1;
if (dr != null)
{
Assets assetmodel = new Assets();
if (dr[0] != null)
{
assetmodel.Number = dr[0].ToString().Trim();
if (dr[1] != null)
{
assetmodel.Type = dr[1].ToString().Trim();
}
if (dr[2] != null)
{
assetmodel.Brand = dr[2].ToString().Trim();
}
if (dr[3] != null)
{
assetmodel.IsMac = dr[3].ToString().Trim();
}
if (dr[4] != null)
{
assetmodel.VideoCard = dr[4].ToString().Trim();
}
if (dr[5] != null)
{
assetmodel.RAM = dr[5].ToString().Trim();
}
if (dr[6] != null)
{
assetmodel.RigidDisk = dr[6].ToString().Trim();
}
if (dr[7] != null)
{
assetmodel.CPU = dr[7].ToString().Trim();
}
if (!(dr[8] is DBNull))
{
DateTime purchaseTime = DateTime.Now;
if (DateTime.TryParse(dr[8].ToString(), out purchaseTime))
{
assetmodel.PurchaseTime = Convert.ToDateTime(dr[8].ToString());
}
}
if (!(dr[9] is DBNull))
{
assetmodel.Monetary = Convert.ToDecimal(dr[9]);
}
if (dr[10] != null)
{
assetmodel.Size = dr[10].ToString().Trim();
}
if (dr[11] != null)
{
assetmodel.Department = dr[11].ToString().Trim();
}
if (dr[12] != null)
{
assetmodel.BelongName = dr[12].ToString().Trim();
string sql = string.Format(@"select top 1 id from [user] where account = '{0}'", assetmodel.BelongName);
var dtUser = userBll.SearchAll(sql);
if (dtUser != null && dtUser.Rows.Count > 0 && dtUser.Rows[0] != null)
{
assetmodel.BelongUser = Convert.ToInt32(dtUser.Rows[0]["id"]);
}
}
if (dr[13] != null)
{
assetmodel.Position = dr[13].ToString().Trim();
}
if (dr[14] != null)
{
assetmodel.ProDirection = dr[14].ToString().Trim();
}
if (!(dr[15] is DBNull))
{
assetmodel.SellingPrice = Convert.ToDecimal(dr[15]);
}
if (dr[16] != null)
{
assetmodel.Remark = dr[16].ToString().Trim();
}
assetmodel.AddTime = addTime;
assetmodel.UpdateTime = updateTime;
var assetsId = assetsList.Where(t => t.Number == assetmodel.Number).Select(m => m.Id).FirstOrDefault();
if (assetsId > 0)//存在就修改
{
assetmodel.AddTime = assetsList.Where(t => t.Number == assetmodel.Number).Select(m => m.AddTime).FirstOrDefault();
assetmodel.UpdateTime = updateTime;
assetmodel.Id = assetsId;
result = assetBll.Update(assetmodel, out msg);
}
else//不存在就添加
{
result = assetBll.Add(assetmodel, out msg);
}
}
}
if (result)
{
i++;
}
else
{
numList = numList + num + ',';
continue;
//Response.Write("<script>alert(' 导入失败，数据格式出错！');window.location.href='AssetsList.aspx'</script>");
}
}
catch (Exception ex)
{
numList = numList + num + ',';
//continue;
throw ex;
//Response.Write("<script>alert(' 未完全导入：共导入" + i + "组数据！ 未完全导入数据为第" + numList.Trim(',') + "行！');window.location.href='AssetsList.aspx'</script>");
}
}
}
else
{
Response.Write("<script>alert('EXCEL文件为空文件!');window.location.href='AssetsListNew.aspx'</script>");
}
if (numList == string.Empty)
{
Response.Write("<script>alert(' 导入成功：共导入" + i + "组数据！');window.location.href='AssetsListNew.aspx'</script>");
//res = true;
}
else
{
Response.Write("<script>alert(' 未完全导入：共导入" + i + "组数据！ 未完全导入数据为第" + numList.Trim(',') + "行！');window.location.href='AssetsListNew.aspx'</script>");
//res = true;
}
}
}
#endregion
#endregion
}
}
#endregion
protected Assets loadAssets(string id)
{
var assets = new Assets();
try
{
IAssetsBll assetsBll = new AssetsBll();
string sql = string.Format(@"select * from Assets withnolock where ISNULL(IsDelete,0)<>1 and id='{0}'", id);
var assetsList = assetsBll.SearchList(sql);
if (assetsList.Any())
{
assets = assetsList.FirstOrDefault();
}
return assets;
}
catch (Exception ex)
{
throw ex;
}
}
protected void MakeORCode(string id)
{
try
{
var asset = loadAssets(id);
if (asset.Id > 0)
{
var url = Server.MapPath("..\\qrcode") + "\\" + "bg.jpg";
//var urlHtml = Server.MapPath("UserLogin.aspx");
string urlHtml = string.Format(@"AssetDetailsShow.aspx?id='{0}'", asset.Id);
var img = CreateORCode.GenerateQrCodeWithLogo(urlHtml, 400, 400, url, "资产编号：" + asset.Number);
System.IO.MemoryStream MStream = new System.IO.MemoryStream();
img.Save(MStream, System.Drawing.Imaging.ImageFormat.Png);
Response.ClearContent();
//Response.ContentType = "image/Png";
//Response.BinaryWrite(MStream.ToArray());
//Response.End();
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", asset.Number + ".png"));
Response.BinaryWrite(MStream.ToArray());
//string dateTime = DateTime.Now.Date.ToString("yyyyMMdd");
//string time = DateTime.Now.ToShortTimeString().Replace(":", "");
//string newFileName = dateTime + time + DateTime.Now.Millisecond.ToString() + ".png";
//string fileUrl = Server.MapPath("..\\qrcode") + "\\" + newFileName;
string fileUrl = Server.MapPath("..\\qrcode") + "\\" + asset.Number + ".png";
if (System.IO.File.Exists(fileUrl))
{
//存在文件
FileInfo file = new FileInfo(fileUrl);
file.Delete();
}
//不存在文件
FileStream fs = new FileStream(fileUrl, FileMode.CreateNew, FileAccess.ReadWrite);
BinaryWriter bw = new BinaryWriter(fs, UTF8Encoding.UTF8);
byte[] by = MStream.ToArray();
for (int i = 0; i < MStream.ToArray().Length; i++)
{
bw.Write(by[i]);
}
fs.Close();
MStream.Close();
MStream.Dispose();
//Response.End();
//Response.Write("<script>alert('成功生成二维码!');</script>");
}
else
{
Response.Write("<script>window.location.href='AssetsListNew.aspx';alert('生成二维码失败，不存在该条记录!');</script>");
}
}
catch (Exception ex)
{
throw ex;
}
}
}
}
1756
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
