【第十四篇】easyui datagrid导出excel
<a class="btn btn-app" onclick="exportExcel()"><i class="fa fa-edit"></i>导出Excel</a>
贴下面的代码之前，我想说一下
我的数据是主外键关系，有多张表关联，所以在做数据的时候，发现很多问题，读取的时候，它会自动读取所有的数据。
当然这不能满足我导出之后的要求，我只需要导出我要的字段即可，所以我进行了数据处理。
//导出Excel function exportExcel() { var rows = $("#saleGrid").datagrid("getRows"); for (var i = 0; i < rows.length; i++) { //进行数据处理 if (isArray(rows[i].OrganizedId)) { rows[i].OrganizedId = rows[i].OrganizedId[0]; } if (isArray(rows[i].CustomerId)) { rows[i].CustomerId = rows[i].CustomerId[0]; } if (rows[i].AdvanceDate != null) { var unix = rows[i].AdvanceDate.replace("/Date(", "").replace(")/", ""); var un = unix.substring(0, 10); var newDate = new Date(); newDate.setTime(un * 1000); rows[i].AdvanceDate = newDate.toLocaleString(); } if (rows[i].OrderDate != null) { var unix = rows[i].OrderDate.replace("/Date(", "").replace(")/", ""); var un = unix.substring(0, 10); var newDate = new Date(); newDate.setTime(un * 1000); rows[i].OrderDate = newDate.toLocaleString(); } if (rows[i].RetainageDate != null) { var unix = rows[i].RetainageDate.replace("/Date(", "").replace(")/", ""); var un = unix.substring(0, 10); var newDate = new Date(); newDate.setTime(un * 1000); rows[i].RetainageDate = newDate.toLocaleString(); } //移除不要的字段 delete rows[i].SaleAtts; delete rows[i].SaleOrderId; delete rows[i].SaleOrderItems; delete rows[i].SaleStatus; delete rows[i].UserName; delete rows[i].Customer; delete rows[i].AddDate; } var bodyData = JSON.stringify(rows); //转成json字符串 //替换中文标题 var a = bodyData.replace(/SaleOrderNo/g, "订单编号").replace(/OrderType/g, "订单类型").replace(/FromWhere/g, "订单来源") .replace(/OrganizedId/g, "机构").replace(/SaleUser/g, "销售员").replace(/SaleTc/g, "销售提成").replace(/OrderDate/g, '订单日期') .replace(/ContractNo/g, "合同编号").replace(/Amount/g, "总额").replace(/Advance/g, "首付款").replace(/AdvanceDate/g, "首付款日期") .replace(/PayMethod/g, "支付方式").replace(/Retainage/g, "尾款").replace(/RetainageDate/g, "尾款日期").replace(/InlayPrice/g, "镶嵌款") .replace(/CustManager/g, "客户经理").replace(/EquityNo/g, "认股书编号").replace(/LogisticsTotal/g, "物流费用") .replace(/Remarks/g, "备注").replace(/CompletedStatus/g, "状态").replace(/CustomerId/g, "终端客户"); var postData = { data: a }; $.ajax({ type: "POST", url: "ExportExcel", data: postData, success: function (data) { if (data == "1") { layer.msg("操作成功,文件在桌面！", { icon: 6, time: 2000, }); } else if (data == "-1") { layer.msg("操作失败！", { icon: 2 }); } } }); }
/// <summary> /// 导出Excel /// </summary> /// <returns></returns> public ActionResult ExportExcel() { string json = Request.Params["data"]; try { DataTable dt = ExcelHelper.JsonToDataTable(json); string pathDestop = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); ExcelHelper.GridToExcelByNPOI(dt, pathDestop + "\\" + "销售订单-" + DateTime.Now.ToString("yyyy-MM-dd") + "导出" + ".xls"); return Content("1"); } catch (Exception) { return Content("-1"); } }
效果图
导出之后
帮助类贴下面
/// <summary> /// 将json转换为DataTable /// </summary> /// <param name="strJson">得到的json</param> /// <returns></returns> public static DataTable JsonToDataTable(string strJson) { //转换json格式 strJson = strJson.Replace(",\"", "*\"").Replace("\":", "\"#").ToString(); //取出表名 var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase); string strName = rg.Match(strJson).Value; DataTable tb = null; //去除表名 strJson = strJson.Substring(strJson.IndexOf("[") + 1); strJson = strJson.Substring(0, strJson.IndexOf("]")); //获取数据 rg = new Regex(@"(?<={)[^}]+(?=})"); MatchCollection mc = rg.Matches(strJson); for (int i = 0; i < mc.Count; i++) { string strRow = mc[i].Value; string[] strRows = strRow.Split('*'); //创建表 if (tb == null) { tb = new DataTable(); tb.TableName = strName; foreach (string str in strRows) { var dc = new DataColumn(); string[] strCell = str.Split('#'); if (strCell[0].Substring(0, 1) == "\"") { int a = strCell[0].Length; dc.ColumnName = strCell[0].Substring(1, a - 2); } else { dc.ColumnName = strCell[0]; } tb.Columns.Add(dc); } tb.AcceptChanges(); } //增加内容 DataRow dr = tb.NewRow(); for (int r = 0; r < strRows.Length; r++) { try { string a = strRows[r].Split('#')[1].Trim(); if (a.Equals("null")) { dr[r] = ""; } else { dr[r] = strRows[r].Split('#')[1].Trim().Replace("，", ",").Replace("：", ":").Replace("\"", ""); } } catch (Exception e) { throw e; } } tb.Rows.Add(dr); tb.AcceptChanges(); } try { if (tb != null) { return tb; } else { throw new Exception("解析错误"); } } catch (Exception e) { throw e; } }
在网上找到一个GridToExcelByNPOI 你们将就用用吧
/// <summary> /// DataTable写入Excel /// </summary> /// <param name="dt"></param> /// <param name="strExcelFileName"></param> /// <returns></returns> public bool GridToExcelByNPOI(DataTable dt, string strExcelFileName) { try { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.ColumnName); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); return true; } catch (Exception ex) { return false; } } /// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="fileName">fileName</param> /// <param name="sheetName">excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn) { ISheet sheet = null; DataTable data = new DataTable(); IWorkbook workbook = null; FileStream fs = null; int startRow = 0; try { fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(fs); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(fs); if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow = sheet.FirstRowNum; } //最后一列的标号 int rowCount = sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null DataRow dataRow = data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null dataRow[j] = row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } } return data; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } } ———————————————— 版权声明：本文为CSDN博主「lbx_15887055073」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/lbx_15887055073/article/details/82194414
---------------------------------------------------------------------------------------------------------
转载请记得说明作者和出处哦-.-
作者：KingDuDu
原文出处：https://www.cnblogs.com/kingdudu/articles/4863980.html
---------------------------------------------------------------------------------------------------------
