Excel 导入并导出结果集
2024-04-20 06:20:42
1、controler代码：
/// <summary>
/// 导入预归类意见书
/// </summary>
/// <param name="uploadfile"></param>
/// <returns></returns>
[HttpPost]
[ValidateAntiForgeryToken]
public FileResult INPUTExcel(IEnumerable<HttpPostedFileBase> uploadfile)
{
string strvalue = string.Empty;
byte[] fileContents = Encoding.Default.GetBytes("暂无数据!");
SsoIdentity identity = this.User.Identity as SsoIdentity;
try
{
string SavePath = string.Empty;
bool bResult = false;
#region<<将Excel文件保存到服务器>>
foreach (var file in uploadfile)
{
string FileName = Path.GetFileName(file.FileName);
string PathR = Server.MapPath("~/UpLoadFile/PreClassified/");
bResult = FileHelper.IsExistDirectory(PathR);
if (!bResult)
{
FileHelper.CreateDirectory(PathR);
}
SavePath = Path.Combine(PathR, FileName);
file.SaveAs(SavePath);
}
#endregion
if (!string.IsNullOrWhiteSpace(SavePath))
{
LoginInfo loginInfo = new LoginInfo();
UserHelper.GetUserInfo(false, ref loginInfo, identity);
//如果存在已存在数据选择是否覆盖的预归类意见书列表
bResult = _ibll.InputClassificationLabDecision(SavePath, ref fileContents,loginInfo.CustomCode); //FileHelper.UnLockFile(SavePath);
System.IO.File.Delete(SavePath);//添加导入完成之后在服务器端删除上传到服务器的EXCEL文件 strvalue = string.Format("三统一帐号{0} 执行预归类意见书管理子系统中预归类意见书 ", loginInfo.LoginName);
if (!bResult)
{
UserOperateLogHelper.WriteOperateLog(strvalue, EnumActionOperatonType.InPut, (int)EnumActionExecutedStatus.Error, identity,
(int)EnumSysFlags.PreClassification);
}
UserOperateLogHelper.WriteOperateLog(strvalue, EnumActionOperatonType.InPut, (int)EnumActionExecutedStatus.Success, identity,
(int)EnumSysFlags.PreClassification);
}
}
catch(Exception ex)
{
LogHelper.Write(ex);
}
return File(fileContents, "application/ms-excel", "ImportPerBooksResult.xls");
}
2.BLL层代码
（1）处理Excel文件：
/// <summary>
/// 导入预归类意见书
/// </summary>
/// <param name="fileName"></param>
/// <param name="btyBytes"></param>
/// <returns></returns>
public bool InputClassificationLabDecision(string fileName, ref byte[] btyBytes, string CGAC_CO)
{
IWorkbook workbook = null;
List<PreClassificationOpBook> lst = new List<PreClassificationOpBook>();
FileStream fs = null;
List<string> lstresult = null;
//string sheetName = null;
bool bResult = true;
ISheet sheet = null;
string filename = string.Format("MyClassificationLabDecision{0}.xls", DateTime.Now.ToString("HHmmssfff"));
int startRow = ;
try
{
using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
if (fileName.IndexOf(".xlsx") > ) // 2007版本
{
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls") > ) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
if (workbook != null)
{
sheet = workbook.GetSheetAt();
if (sheet != null)
{
IRow firstRow = sheet.GetRow();
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
startRow = sheet.FirstRowNum + ;
//最后一列的标号
int rowCount = sheet.LastRowNum;
List<bool> lstdate = new List<bool>();
for (int i = startRow; i <= rowCount; ++i)
{
bool IsNullRow = true;
PreClassificationOpBook mdl = new PreClassificationOpBook();
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
bool ibresult = false;
var cellValue=row.GetCell(j);
var rowValue = cellValue == null?"":cellValue.ToString();
if (!string.IsNullOrWhiteSpace(rowValue) || !IsNullRow)
{
mdl = AddModelField(j, mdl, rowValue, ref ibresult);
if (j == )//如果列为生效日期列
{
lstdate.Add(ibresult);
}
IsNullRow = false;
}
}
if (!IsNullRow)
{
lst.Add(mdl);
}
}
//不采取全部一起提交的方式 每一条执行一次SaveChanage
AddModelList(lst, lstdate, ref lstresult,CGAC_CO);
}
}
}
}
catch (IOException ex)
{
LogHelper.Write(ex);
bResult = false;
}
catch (SqlException ex)
{
LogHelper.Write(ex);
bResult = false;
}
finally
{
if (fs != null)
{
fs.Dispose();
}
//导出结果集
Dictionary<string, string> Dic = GETMATCHUP();
Dic["CGAC_CO"] = "关区代码";
Dic.Remove("IsEffect");
FileHelper.ExExcelDouble<PreClassificationOpBook>(lst, filename, ref btyBytes, Dic, lstresult);
}
return bResult;
}
（2）Excel列存入model实体类
/// <summary>
/// 向Model中添加字段值
/// </summary>
/// <param name="i"></param>
/// <param name="mdl"></param>
/// <param name="row"></param>
/// <param name="ibresult"></param>
/// <returns></returns>
private PreClassificationOpBook AddModelField(int i, PreClassificationOpBook mdl, string rowValue, ref bool ibresult)
{ switch (i)
{
//case 0://第一行为流水号
// {
// mdl.SEQ_NO = rowValue;
// break;
// }
case ://预归类意见书编号
{
mdl.PCS_NO_S = rowValue;
break;
}
case ://经营单位（委托方）名称
{
mdl.TRADE_NAME = rowValue;
break;
}
case ://经营单位(委托方)企业代码
{
mdl.TRADE_CO = rowValue;
break;
}
case ://商品名称(中文)
{
mdl.G_NAME = rowValue;
break;
}
case ://规格型号
{
mdl.G_MODEL = rowValue;
break;
}
case ://商品名称(英文名称)
{
mdl.G_NAME_ENG = rowValue;
break;
}
case ://商品名称(其他名称)
{
mdl.G_NAME_OTR = rowValue;
break;
}
case ://商品描述(货物型号、规格、成份及用途)
{
mdl.G_DESCRIPTION = rowValue;
break;
}
case ://归类结论
{
mdl.CODE_TS = rowValue;
break;
}
case ://归类依据或理由
{
mdl.NOTE = rowValue;
break;
}
case ://预归类单位名称
{
mdl.AGENT_NAME = rowValue;
break;
}
//case 11://预归类数据导入单位名称
// {
// mdl.AGENT_INPUT_NAME = rowValue;
// break;
// }
case ://联系电话
{
mdl.AGENT_TEL = rowValue;
break;
}
//case 13://关区代码
// {
// mdl.CGAC_CO = rowValue;
// break;
// }
case ://生效日期
{
DateTime dt = DateTime.Now;
bool bResult = ToolsHelper.CheckTime(rowValue.Replace("月", ""), ref dt);
if (bResult)
{
mdl.EFFECTIVE_DATE = dt;
}
ibresult = bResult;
break;
}
}
return mdl;
}
3.导出导入结果集
（1）固定列最后加一列导入结果列
/// <summary>
/// 将一组对象导出成EXCEL
/// </summary>
/// <typeparam name="T">要导出对象的类型</typeparam>
/// <param name="objList">一组对象</param>
/// <param name="fileName">导出后的文件名</param>
/// <param name="columnInfo">列名信息</param>
/// <param name="btyBytes"></param>
public static bool ExExcelDouble<T>(List<T> objList, string fileName, ref byte[] btyBytes, Dictionary<string, string> columnInfo = null,
List<string> lststring = null) where T : class
{
bool bResult = false;
try
{
//if (objList.Count > 50000)
//{
// btyBytes = new byte[] { };
// bResult = ExExcel<T>(objList, fileName, ref btyBytes, columnInfo, lststring);
// return bResult;
//}
HSSFWorkbook workbook = new HSSFWorkbook();
//在工作薄中建立工作表
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
sheet.SetColumnWidth(, * );
if (columnInfo != null && columnInfo.Count == ) { return false; }
//生成EXCEL的HTML
string excelStr = "";
Type myType = null;
if (objList.Any())
{
myType = objList[].GetType();
//根据反射从传递进来的属性名信息得到要显示的属性
List<PropertyInfo> myPro = new List<PropertyInfo>();
PropertyInfo[] properties = myType.GetProperties();
int m = ;
if (columnInfo != null)
{
var rowheader = sheet.CreateRow();
rowheader.Height = * ;
foreach (string cName in columnInfo.Keys)
{
PropertyInfo p = myType.GetProperty(cName);
if (p != null)
{
myPro.Add(p);
rowheader.CreateCell(m).SetCellValue(columnInfo[cName]);
m++;
}
}
if (lststring != null)
{
rowheader.CreateCell(myPro.Count).SetCellValue("导入提示");
}
}
//如果没有找到可用的属性则结束
if (myPro.Count == ) { return bResult; }
int i = ;//lststring 计数
foreach (T obj in objList)
{
int n = ;
if (sheet != null)
{
var sheetrow = sheet.CreateRow(i);
sheetrow.Height = sheetrow.Height = * ;
foreach (PropertyInfo p in myPro)
{
dynamic val = p.GetValue(obj, null) ?? "";
string valtype = val.GetType().ToString();
if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -)
{
val = Convert.ToDouble(val);
}
else if (valtype.ToLower().IndexOf("datetime", StringComparison.Ordinal) > -)
{
val = val.ToString("yyyy-MM-dd HH:mm:ss");
if (val.Equals("0001-01-01 00:00:00"))
{
val = "";
}
}
sheetrow.CreateCell(n).SetCellValue(val);
n++;
} if (lststring != null)
{
sheetrow.CreateCell(n).SetCellValue(lststring[i - ]);
}
} Thread.Sleep();
i++;
}
}
else
{
if (sheet != null) sheet.CreateRow().CreateCell().SetCellValue("暂无数据！");
} using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
if (btyBytes != null)
{
btyBytes = ms.ToArray();
}
else
{
btyBytes = ms.ToArray(); HttpResponse rs = System.Web.HttpContext.Current.Response;
rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
rs.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName,System.Text.Encoding.UTF8));
rs.ContentType = "application/ms-excel";
rs.BinaryWrite(btyBytes);
rs.End();
}
ms.Flush();
ms.Position = ;
workbook = null;
}
//输出EXCEL
bResult = true;
}
catch (Exception ex)
{
LogHelper.Write(ex);
}
return bResult;
}
（2）动态列（不固定列）导入导出结果集
/// <summary>
/// 同义词导入
/// </summary>
/// <typeparam name="T">要导出对象的类型</typeparam>
/// <param name="objList">一组对象</param>
/// <param name="fileName">导出后的文件名</param>
/// <param name="columnInfo">列名信息</param>
/// <param name="MaxColNum">最大列数</param>
/// <param name="btyBytes"></param>
public static bool ExRandomColExcel<T>(List<T> objList, string fileName, ref byte[] btyBytes, Dictionary<string, string> columnInfo = null,
List<string> lststring = null, int MaxColNum = ) where T : class
{
bool bResult = false;
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
//在工作薄中建立工作表
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
sheet.SetColumnWidth(, * ); if (columnInfo != null && columnInfo.Count == ) { return false; }
//生成EXCEL的HTML
Type myType = null;
if (objList.Any())
{
CellType cellType = new CellType(); myType = objList[].GetType();
//根据反射从传递进来的属性名信息得到要显示的属性
List<PropertyInfo> myPro = new List<PropertyInfo>();
PropertyInfo[] properties = myType.GetProperties();
int m = ;
if (columnInfo != null)
{
var rowheader = sheet.CreateRow();
rowheader.Height = * ;
if (lststring != null)
{
rowheader.CreateCell(myPro.Count).SetCellValue("导入提示");
}
for (int i = ; i < MaxColNum; i++)
{
m++;
PropertyInfo p = myType.GetProperty(columnInfo.ElementAtOrDefault(i > columnInfo.Count - ? columnInfo.Count - : i).Key);
if (p != null)
{
myPro.Add(p);
rowheader.CreateCell(m).SetCellValue(
columnInfo.ElementAtOrDefault(i > columnInfo.Count - ? columnInfo.Count - : i).Value);
}
}
}
//如果没有找到可用的属性则结束
if (myPro.Count == ) { return bResult; }
int rowCount = ;//lststring 计数
foreach (T obj in objList)
{
PropertyInfo[] pro = obj.GetType().GetProperties();
int n = ;
if (sheet != null)
{
var sheetrow = sheet.CreateRow(rowCount);
sheetrow.Height = sheetrow.Height = * ;
//导入结果
if (lststring != null)
{
sheetrow.CreateCell(n).SetCellValue(lststring[rowCount - ]);
}
for (int j = ; j < MaxColNum; j++)
{
n++;
dynamic value = "";
dynamic val = pro[j > pro.Length - ? pro.Length - : j].GetValue(obj, null);
string listtype = val.GetType().ToString();
if (listtype.ToLower().IndexOf("list", StringComparison.Ordinal) > -)
{
foreach (var item in val)
{
j++;
value = item;
string valtype = value.GetType().ToString();
if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -)
{
value = Convert.ToDouble(value);
}
else if (valtype.ToLower().IndexOf("datetime", StringComparison.Ordinal) > -)
{
value = value.ToString("yyyy-MM-dd HH:mm:ss");
}
sheetrow.CreateCell(n).SetCellValue(value);
n++;
}
if (j > pro.Length - )
{
break;
}
}
else
{
value = pro[j].GetValue(obj, null);
string valtype = value.GetType().ToString();
if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -)
{
value = Convert.ToDouble(value);
}
else if (valtype.ToLower().IndexOf("datetime", StringComparison.Ordinal) > -)
{
value = value.ToString("yyyy-MM-dd HH:mm:ss");
}
sheetrow.CreateCell(n).SetCellValue(value);
}
}
}
Thread.Sleep();
rowCount++;
}
}
else
{
if (sheet != null) sheet.CreateRow().CreateCell().SetCellValue("暂无数据！");
} using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
if (btyBytes != null)
{
btyBytes = ms.ToArray();
}
else
{
btyBytes = ms.ToArray();
HttpResponse rs = System.Web.HttpContext.Current.Response;
rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
rs.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
rs.ContentType = "application/ms-excel";
rs.BinaryWrite(btyBytes);
rs.End();
}
ms.Flush();
ms.Position = ;
workbook = null;
}
//输出EXCEL
bResult = true;
}
catch (Exception ex)
{
LogHelper.Write(ex);
}
return bResult;
}
最新文章
- Alcatraz,
- 网络编程-socket
- Python 学习之进制与编码
- Weka算法Classifier-tree-J48源代码分析（一个）基本数据结构和算法
- 剑指offer系列17---顺时针打印矩阵（不是很懂）
- 为aps.net core项目加上全局异常捕捉和记录
- C语言--返回局部变量的地址
- 设置PlaceHolder的颜色
- StringUtils.isNumeric(String str) 的一个坑（转）
- Android编程中的5种数据存储方式
- 【Beta】阶段 第四次Daily Scrum Meeting
- kali使用
- 浅谈Android布局
- 在eclipse中配置Python环境
- Spring Cloud Feign Ribbon 配置
- 002_tmux详解
- (原创)composite模式和bridge模式是天生的好朋友
- 【每日scrum】NO.8
- 去除IE10+上文本框巨丑无比的删除图标以及显示密码图标
- kali linux：wireshark不能被root用户启用的解决方案