//导入数据
public ActionResult ExpressInfoImport()
{
var ptcp = new BaseResponse() { DoFlag = true, DoResult = "Success" };
var file = Request.Files["files"];
if (file == null)
{
ptcp.DoFlag = false;
ptcp.DoResult = "请选择上传文件";
goto ovr;
}
try
{
var filename = Path.GetFileName(file.FileName);
if (string.IsNullOrEmpty(filename))
{
ptcp.DoFlag = false;
ptcp.DoResult = "请选择上传文件"; goto ovr;
}
var filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
var fileEx = Path.GetExtension(filename);//获取上传文件的扩展名
var noFileName = Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
var maxSize = 4000 * 1024;//定义上传文件的最大空间大小为4M
var fileType = ".xls,.xlsx";//定义上传文件的类型字符串
var fileName = noFileName + "_" + System.Guid.NewGuid() + fileEx;
if (!fileType.Contains(fileEx))
{
ptcp.DoFlag = false;
ptcp.DoResult = "文件类型不对，只能导入xls和xlsx格式的文件"; goto ovr;
}
if (filesize >= maxSize)
{
ptcp.DoFlag = false;
ptcp.DoResult = "上传文件超过4M，不能上传"; goto ovr;
}
var url = Configurator.JsonServiceUrl("UploadFile");
if (!Directory.Exists(url))//如果不存在就创建file文件夹
{
Directory.CreateDirectory(url);
}
var virtualPath = string.Format("{0}{1}", url, fileName);
// 文件系统不能使用虚拟路径
file.SaveAs(virtualPath);
//读取文件内容，循环处理
var stream = new FileStream(virtualPath, FileMode.Open);
var dataTable = ExcelHelper.GetInstance().ReadExcelToDataTable(stream);
var list = new List<ExpressImportModel>();
try
{
foreach (DataRow dr in dataTable.Rows)
{
var sysNo = dr["SysNo"];
var orderCode = dr["订单号"];
var logiscticNo = dr["物流单号"];
var sugges = dr["处理建议"];
list.Add(new ExpressImportModel()
{
SysNo = Convert.ToInt32(sysNo),
OrderCode = orderCode.ToString(),
LogiscticNo = logiscticNo.ToString(),
Suggest = sugges.ToString()
});
}
}
catch (Exception e)
{
ptcp.DoFlag = false;
ptcp.DoResult = "导入格式不正确：e" + e.Message; goto ovr;
}
if (!list.Any())
{
ptcp.DoFlag = false;
ptcp.DoResult = "导入内容为空"; goto ovr;
}
var response = ExpressClient.Instance.ImportHandleInfo(list);
ptcp.DoFlag = response.DoFlag;
ptcp.DoResult = response.DoResult;
if (response.ErrDtos.Any())
{
var errStr = new StringBuilder();
foreach (var item in response.ErrDtos)
{
errStr.AppendFormat(@"SysNo_{0}_OrderCode{1}_LogiscticNo{2}导入错误:{3} <br/>", item.SysNo,
item.OrderCode, item.LogiscticNo, item.ErrReason);
}
ptcp.DoResult = "以下导入出现错误：<br/>" + errStr.ToString();
}
}
catch (Exception ex)
{
ptcp.DoFlag = false;
ptcp.DoResult = ex.ToString();
}
ovr:
ViewBag.Result = ptcp;
return View("Export/Export");
}
/// <summary>
/// 读取Excel文件转化为DataTable
/// </summary>
/// <param name="stream"></param>
/// <returns></returns>
public DataTable ReadExcelToDataTable(Stream stream)
{
DataTable dt = new DataTable();
workBook = new HSSFWorkbook(stream);
workSheet = workBook.GetSheetAt(0);
IEnumerator rows = workSheet.GetRowEnumerator();
rows.MoveNext();
HSSFRow row = (HSSFRow)rows.Current;
for (int i = 0; i < workSheet.GetRow(0).LastCellNum; i++)
{
dt.Columns.Add(row.GetCell(i).StringCellValue);
}
while (rows.MoveNext())
{
row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell != null)
{
dr[i] = cell.ToString();
}
else
{
dr[i] = null;
}
}
dt.Rows.Add(dr);
}
return dt;
}
导出：
[HttpPost]
[MultipleButton(Name = "action", Argument = "ExpressInfoExport")]
public ActionResult ExpressInfoExport(ExpressInfoRefer refer)
{
refer.PageIndex = 1;
refer.PageSize = int.MaxValue;
var result = ExpressClient.Instance.QueryExpressInfoPageList(refer);
if (result.List == null || !result.List.Any())
{
return View("Error");
}
var dicProperties = new Dictionary<string, string>();
dicProperties.Add("SysNo", "SysNo");
dicProperties.Add("OrderCode", "订单号");
dicProperties.Add("LogiscticNo", "物流单号");
dicProperties.Add("LogiscticId", "配送商ID");
dicProperties.Add("LogiscticCompanyName", "快递公司");
dicProperties.Add("OrderStatusContent", "订单扭转");
dicProperties.Add("LogiscticContent", "物流信息");
dicProperties.Add("LogisticOuterContent", "国际物流");
dicProperties.Add("CallTypeDesc", "对接方式");
dicProperties.Add("SubscribeStatusDesc", "订阅状态");
dicProperties.Add("SubscribeCount", "订阅次数");
dicProperties.Add("CallCount", "总调用(推送)次数");
dicProperties.Add("LastStatusDesc", "运单状态");
dicProperties.Add("PushRequestMessage", "快递100消息");
dicProperties.Add("RequestFaildReason", "订阅失败原因");
dicProperties.Add("OrderTypeDesc", "订单类型");
dicProperties.Add("RowCreateDate", "创建日期");
dicProperties.Add("Remark", "备注");
dicProperties.Add("HandleSuggest", "处理建议");
ExcelHelper.GetInstance().WriteListToExcel(result.List, dicProperties, "物流信息列表");
return null;
}
[AttributeUsage(AttributeTargets.Method, AllowMultiple = false, Inherited = true)]
public class MultipleButtonAttribute : ActionNameSelectorAttribute
{
public string Name { get; set; }
public string Argument { get; set; }
public override bool IsValidName(ControllerContext controllerContext, string actionName, MethodInfo methodInfo)
{
var isValidName = false;
var keyValue = string.Format("{0}:{1}", Name, Argument);
var value = controllerContext.Controller.ValueProvider.GetValue(keyValue);
if (value != null)
{
controllerContext.Controller.ControllerContext.RouteData.Values[Name] = Argument;
isValidName = true;
}
return isValidName;
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">数据结果List</param>
/// <param name="dicProperties">以字段-名称的形式</param>
/// <param name="fileName">导出文件名</param>
public void WriteListToExcel<T>(List<T> list, Dictionary<string, string> dicProperties, string fileName) where T : class
{
var size = 60000;
var count = list.Count / size;
if (list.Count%size != 0)
{
count += 1;
}
workBook = new HSSFWorkbook();
ms = new MemoryStream();
fileName = fileName == "" ? "导出Excel" : fileName;
//fileName不能包含 : / \ ? * [ ]等特殊字符
for (int a = 0; a < count; a++)
{
#region 创建sheet
workSheet = workBook.CreateSheet(fileName+(a+1));
DataTable dt = new DataTable();
if (dicProperties.Count > 0 && list.Count > 0)
{
Type type = typeof(T);
foreach (KeyValuePair<string, string> property in dicProperties)
{
//建立DataTable表头
dt.Columns.Add(new DataColumn(property.Value));
}
foreach (T t in list.Skip(size*a).Take(size))
{
DataRow dr = dt.NewRow();
int i = 0;
foreach (KeyValuePair<string, string> property in dicProperties)
{
PropertyInfo pi = type.GetProperty(property.Key);
object obj = pi.GetValue(t, null);
//为DataTable表内容赋值
dr[i] = obj == null ? "" : obj.ToString();
i++;
}
dt.Rows.Add(dr);
}
CreateExcel(dt, dicProperties);
}
#endregion
}
WriteToExcel(fileName);
}
private void CreateExcel(DataTable dt, Dictionary<string, string> dicProperties)
{
IRow rowExcel;
ICell cellExcel;
int rowIndex = 0;
rowExcel = workSheet.CreateRow(rowIndex);
int intColumn = 0;
foreach (KeyValuePair<string, string> dicProperty in dicProperties)
{
cellExcel = rowExcel.CreateCell(intColumn);
cellExcel.SetCellValue(dicProperty.Value);
++intColumn;
}
foreach (DataRow row in dt.Rows)
{
++rowIndex;
rowExcel = workSheet.CreateRow(rowIndex);
int intColumnContent = 0;
foreach (KeyValuePair<string, string> property in dicProperties)
{
cellExcel = rowExcel.CreateCell(intColumnContent);
cellExcel.SetCellValue(row[property.Value].ToString());
++intColumnContent;
}
}
}
/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="fileName"></param>
private void WriteToExcel(string fileName)
{
workBook.Write(ms);
ms.Flush();
ms.Position = 0;
workBook = null;
HttpContext current = HttpContext.Current;
current.Response.ContentType = "application/ms-excel";
current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
current.Response.BinaryWrite(ms.ToArray());
current.Response.End();
ms.Close();
ms = null;
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
