easyui datagrid导出到Excel

最新推荐文章于 2020-06-15 12:50:43 发布

宋小寻

最新推荐文章于 2020-06-15 12:50:43 发布

阅读量357

点赞数 1

文章标签： easyui

记录一下datagrid导出功能

参考自：https://www.cnblogs.com/kingdudu/p/4863980.html

//导出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 });
}
}
});
}

这是C#后台：

/// <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：

参考自：https://blog.csdn.net/lbx_15887055073/article/details/82194414

在管理NuGet程序包中搜索 NPOI，安装 NPOI 到要项目中。

/// <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;
}
}

---------------------
作者：lbx_15887055073
来源：CSDN
原文：https://blog.csdn.net/lbx_15887055073/article/details/82194414
版权声明：本文为博主原创文章，转载请附上博文链接！

可参考博主详细了解。