利用Aspose.Cells完成easyUI中DataGrid数据的Excel导出功能

转载

mb5fcf3d80e40fa 2014-05-01 12:51:00

文章标签 json 数据文字居中客户端 excel导出 文章分类 代码人生

我准备在项目中实现该功能之前,google发现大部分代码都是利用一般处理程序 HttpHandler实现的服务器端数据的Excel导出,但是这样存在的问题是ashx读取的数据一般都是数据库中视图的数据，难免会含有方便操作的主键ID这列的记录。现在项目需要在easyUI的DataGrid中显示的数据能全部导出Excel，包括DataGrid中的中文标题，其他的统统不要。

完成该功能所需的工具和环境：Newtonsoft.Json序列化和反序列化类库、easyUI前端UI框架、HttpHandler一般处理程序、Aspose.Cells电子表格生成组件;.Net Framework4.0。

前端完成DataGrid中的题头和数据行信息遍历访问,并以JS中二维数组的形式返回后台供ashx程序处理.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30	`function``ExportExcel() {``// 返回grid的所有可见行给后端供导出Excel用``var``rows = $(``'#userlist'``).datagrid(``"getRows"``);``if``(rows.length == 0) {``msgShow(``"没有数据可供导出"``);``return``;``}``//返回grid的所有列的选项title、列宽等``// var columns = $('#userlist').datagrid("options").columns;``//定制DataGrid的columns信息,只返回{field:,title:}``var``columns =` `new``Array();``var``fields = $(``'#userlist'``).datagrid(``'getColumnFields'``);``for``(``var``i = 0; i < fields.length; i++) {``var``opts = $(``'#userlist'``).datagrid(``'getColumnOption'``, fields[i]);``var``column =` `new``Object();``column.field = opts.field;``column.title = opts.title;``columns.push(column);``}``var``excelWorkSheet =` `new``Object();``excelWorkSheet.rows = rows;``excelWorkSheet.columns = columns;``excelWorkSheet.sheetName =` `"设置导出的Excel工作表名"``;``var``filename =` `"/HttpHandlers/<span style="``color:``#ff0000;">FileHandler.ashx?action=exportexcel&excelWorkSheet</span>=" + JSON.stringify(excelWorkSheet);``location.href = filename;``}`

其中FileHandler.ashx是公共的文件处理程序,根据action=Exportexcel来实现Excel导出功能.

1 2 3 4 5 6 7 8 9 10 11 12	`public``void``ProcessRequest(HttpContext context)``{``var``action = context.Request[``"action"``];``switch``(action.ToLower())``{``case``"exportexcel"``:``<span style=``"color: #ff0000;"``>EasyUIGrid2Excel</span>(context);``break``;``}``}`

EasyUIGrid2Excel完成前台传递的关于Excel报表导出的参数,比如工作表名sheetName,题头标题和行记录。同时它完美解决了在各浏览器下文件名中文乱码问题，最后以弹窗的形式让用户选择直接打开还是下载。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39	`private``void``EasyUIGrid2Excel(HttpContext context)``{``var``jsonString = context.Request[``"excelWorkSheet"``];``//使用Newtonsoft.Json.Linq.JObject将json字符串转化成结构不固定的Class类``dynamic jsonObject =<span style=``"color: #ff0000;"``> JObject.Parse</span>(jsonString);``string``fileName = String.Concat(jsonObject.sheetName, DateTime.Now.ToString(``"yyyyMMdd-HHmmss"``),` `".xls"``) ;``//解决中文文件名乱码只在IE中有效``// filename = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8);``if``(context.Request.UserAgent.ToLower().IndexOf(``"msie"``) > -1)``{``//当客户端使用IE时，对其进行编码；``//使用 ToHexString 代替传统的 UrlEncode()；``fileName = <span style=``"color: #ff0000;"``>CommonHelper.ToHexString</span>(fileName);``}``if``(context.Request.UserAgent.ToLower().IndexOf(``"firefox"``) > -1)``{``//为了向客户端输出空格，需要在当客户端使用 Firefox 时特殊处理``context.Response.AddHeader(``"Content-Disposition"``,` `"attachment;filename=\""``+ fileName +` `"\""``);``}``else``context.Response.AddHeader(``"Content-Disposition"``,` `"attachment;filename="``+ fileName);``string``extension = Path.GetExtension(fileName);``context.Response.ContentType = <span style=``"color: #ff0000;"``>CommonHelper.GetMimeType</span>(extension);``context.Response.ContentEncoding = Encoding.UTF8;``Workbook workbook = <span style=``"color: #ff0000;"``>Object2Workbook</span>(jsonObject, context);``context.Response.Clear();``context.Response.Buffer =` `true``;``context.Response.BinaryWrite(workbook.SaveToStream().ToArray());``context.Response.End();``}`

上面代码中的红色标注部分详见下载中的公共类库,其中Object2Workbook主要实现Aspose.cells中的Excel格式化输出定制,没有太多技巧,Aspose.cells还带有授权文件的哦,你懂的!

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92	`private``Workbook Object2Workbook(dynamic jsonObject, HttpContext context)``{``#region Aspose.Cell引用``Aspose.Cells.License licExcel =` `new``License();` `//Aspose.Cells申明``if``(File.Exists(context.Server.MapPath(``"~/Bin/cellLic.lic"``)))``licExcel.SetLicense(context.Server.MapPath(``"~/Bin/cellLic.lic"``));``#endregion``Workbook workbook =` `new``Workbook();``Worksheet sheet = workbook.Worksheets[0];``Styles styles = workbook.Styles;``int``styleIndex = styles.Add();``Aspose.Cells.Style borderStyle = styles[styleIndex];``borderStyle.Borders.DiagonalStyle = CellBorderType.None;``borderStyle.HorizontalAlignment = TextAlignmentType.Center;``//文字居中` `Cells cells = sheet.Cells;``sheet.FreezePanes(1, 1, 1, 0);``//冻结第一行``sheet.Name = jsonObject.sheetName;``//接受前台的Excel工作表名``//为标题设置样式` `Style styleTitle = workbook.Styles[workbook.Styles.Add()];``//新增样式` `styleTitle.HorizontalAlignment = TextAlignmentType.Center;``//文字居中` `styleTitle.Font.Name =` `"宋体"``;``//文字字体` `styleTitle.Font.Size = 18;``//文字大小` `styleTitle.Font.IsBold =` `true``;``//粗体` `//题头样式` `Style styleHeader = workbook.Styles[workbook.Styles.Add()];``//新增样式` `styleHeader.HorizontalAlignment = TextAlignmentType.Center;``//文字居中` `styleHeader.Font.Name =` `"宋体"``;``//文字字体` `styleHeader.Font.Size = 14;``//文字大小` `styleHeader.Font.IsBold =` `true``;``//粗体` `styleHeader.IsTextWrapped =` `true``;``//单元格内容自动换行` `styleHeader.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;``styleHeader.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;``styleHeader.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;``styleHeader.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;``//内容样式``Style styleContent = workbook.Styles[workbook.Styles.Add()];``//新增样式` `styleContent.HorizontalAlignment = TextAlignmentType.Center;``//文字居中` `styleContent.Font.Name =` `"宋体"``;``//文字字体` `styleContent.Font.Size = 12;``//文字大小` `styleContent.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;``styleContent.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;``styleContent.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;``styleContent.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;``var``rowCount = jsonObject.rows.Count;``//表格行数` `var``columnCount = jsonObject.columns.Count;``//表格列数` `//生成行1 标题行` `cells.Merge(0, 0, 1, columnCount);``//合并单元格` `cells[0, 0].PutValue(jsonObject.sheetName);``//填写内容` `cells[0, 0].Style = styleTitle;``cells.SetRowHeight(0, 25);``//生成题头列行` `for``(``int``i = 0; i < columnCount; i++)``{``cells[1, i].PutValue(jsonObject.columns[i][``"title"``]);``cells[1, i].Style = styleHeader;``cells.SetRowHeight(1, 23);``}``//生成内容行,第三行起始``//生成数据行` `for``(``int``i = 0; i < rowCount; i++)``{``for``(``int``k = 0; k < columnCount; k++)``{``var``currentColumnName = jsonObject.columns[k][``"field"``];``cells[2 + i, k].PutValue(jsonObject.rows[i][currentColumnName.Value]);``cells[2 + i, k].Style = styleContent;``}``cells.SetRowHeight(2 + i, 22);``}``//添加制表日期``cells[2 + rowCount, columnCount-1].PutValue(``"制表日期:"``+ DateTime.Now.ToShortDateString());``sheet.AutoFitColumns();``//让各列自适应宽度``sheet.AutoFitRows();``//让各行自适应宽度``return``workbook;``}`

该code snippet也能很好的移植,在需要实现Excel导出的js文件中调用ExportExcel函数,传递sheetName值即可

国际惯例,无图无真相

利用Aspose.Cells完成easyUI中DataGrid数据的Excel导出功能_数据

文件下载

btw:另外请教两个问题,知道的童鞋回答一下,谢谢

1,通过客户端导出Excel的方式,当打开文件的时候,提示:文件错误,数据可能丢失, 初步认为可能数字以文本形式存到Excel中出现了问题?如果直接服务器端导出却没有这个错误

2,一对多表中数据更新问题,比如三张表User,Role和对应的关联表UserRole,其中主键都是自动递增ID。如果我更改了用户角色，当前的做法是在UserRole中删掉该用户的所有旧角色，再新增。感觉这样麻烦也不合理，请教大家有好的做饭没？

前端完成DataGrid中的题头和数据行信息遍历访问,并以JS中二维数组的形式返回后台供ashx程序处理.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30	`function``ExportExcel() {``// 返回grid的所有可见行给后端供导出Excel用``var``rows = $(``'#userlist'``).datagrid(``"getRows"``);``if``(rows.length == 0) {``msgShow(``"没有数据可供导出"``);``return``;``}``//返回grid的所有列的选项title、列宽等``// var columns = $('#userlist').datagrid("options").columns;``//定制DataGrid的columns信息,只返回{field:,title:}``var``columns =` `new``Array();``var``fields = $(``'#userlist'``).datagrid(``'getColumnFields'``);``for``(``var``i = 0; i < fields.length; i++) {``var``opts = $(``'#userlist'``).datagrid(``'getColumnOption'``, fields[i]);``var``column =` `new``Object();``column.field = opts.field;``column.title = opts.title;``columns.push(column);``}``var``excelWorkSheet =` `new``Object();``excelWorkSheet.rows = rows;``excelWorkSheet.columns = columns;``excelWorkSheet.sheetName =` `"设置导出的Excel工作表名"``;``var``filename =` `"/HttpHandlers/<span style="``color:``#ff0000;">FileHandler.ashx?action=exportexcel&excelWorkSheet</span>=" + JSON.stringify(excelWorkSheet);``location.href = filename;``}`

其中FileHandler.ashx是公共的文件处理程序,根据action=Exportexcel来实现Excel导出功能.

1 2 3 4 5 6 7 8 9 10 11 12	`public``void``ProcessRequest(HttpContext context)``{``var``action = context.Request[``"action"``];``switch``(action.ToLower())``{``case``"exportexcel"``:``<span style=``"color: #ff0000;"``>EasyUIGrid2Excel</span>(context);``break``;``}``}`

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39	`private``void``EasyUIGrid2Excel(HttpContext context)``{``var``jsonString = context.Request[``"excelWorkSheet"``];``//使用Newtonsoft.Json.Linq.JObject将json字符串转化成结构不固定的Class类``dynamic jsonObject =<span style=``"color: #ff0000;"``> JObject.Parse</span>(jsonString);``string``fileName = String.Concat(jsonObject.sheetName, DateTime.Now.ToString(``"yyyyMMdd-HHmmss"``),` `".xls"``) ;``//解决中文文件名乱码只在IE中有效``// filename = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8);``if``(context.Request.UserAgent.ToLower().IndexOf(``"msie"``) > -1)``{``//当客户端使用IE时，对其进行编码；``//使用 ToHexString 代替传统的 UrlEncode()；``fileName = <span style=``"color: #ff0000;"``>CommonHelper.ToHexString</span>(fileName);``}``if``(context.Request.UserAgent.ToLower().IndexOf(``"firefox"``) > -1)``{``//为了向客户端输出空格，需要在当客户端使用 Firefox 时特殊处理``context.Response.AddHeader(``"Content-Disposition"``,` `"attachment;filename=\""``+ fileName +` `"\""``);``}``else``context.Response.AddHeader(``"Content-Disposition"``,` `"attachment;filename="``+ fileName);``string``extension = Path.GetExtension(fileName);``context.Response.ContentType = <span style=``"color: #ff0000;"``>CommonHelper.GetMimeType</span>(extension);``context.Response.ContentEncoding = Encoding.UTF8;``Workbook workbook = <span style=``"color: #ff0000;"``>Object2Workbook</span>(jsonObject, context);``context.Response.Clear();``context.Response.Buffer =` `true``;``context.Response.BinaryWrite(workbook.SaveToStream().ToArray());``context.Response.End();``}`

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92	`private``Workbook Object2Workbook(dynamic jsonObject, HttpContext context)``{``#region Aspose.Cell引用``Aspose.Cells.License licExcel =` `new``License();` `//Aspose.Cells申明``if``(File.Exists(context.Server.MapPath(``"~/Bin/cellLic.lic"``)))``licExcel.SetLicense(context.Server.MapPath(``"~/Bin/cellLic.lic"``));``#endregion``Workbook workbook =` `new``Workbook();``Worksheet sheet = workbook.Worksheets[0];``Styles styles = workbook.Styles;``int``styleIndex = styles.Add();``Aspose.Cells.Style borderStyle = styles[styleIndex];``borderStyle.Borders.DiagonalStyle = CellBorderType.None;``borderStyle.HorizontalAlignment = TextAlignmentType.Center;``//文字居中` `Cells cells = sheet.Cells;``sheet.FreezePanes(1, 1, 1, 0);``//冻结第一行``sheet.Name = jsonObject.sheetName;``//接受前台的Excel工作表名``//为标题设置样式` `Style styleTitle = workbook.Styles[workbook.Styles.Add()];``//新增样式` `styleTitle.HorizontalAlignment = TextAlignmentType.Center;``//文字居中` `styleTitle.Font.Name =` `"宋体"``;``//文字字体` `styleTitle.Font.Size = 18;``//文字大小` `styleTitle.Font.IsBold =` `true``;``//粗体` `//题头样式` `Style styleHeader = workbook.Styles[workbook.Styles.Add()];``//新增样式` `styleHeader.HorizontalAlignment = TextAlignmentType.Center;``//文字居中` `styleHeader.Font.Name =` `"宋体"``;``//文字字体` `styleHeader.Font.Size = 14;``//文字大小` `styleHeader.Font.IsBold =` `true``;``//粗体` `styleHeader.IsTextWrapped =` `true``;``//单元格内容自动换行` `styleHeader.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;``styleHeader.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;``styleHeader.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;``styleHeader.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;``//内容样式``Style styleContent = workbook.Styles[workbook.Styles.Add()];``//新增样式` `styleContent.HorizontalAlignment = TextAlignmentType.Center;``//文字居中` `styleContent.Font.Name =` `"宋体"``;``//文字字体` `styleContent.Font.Size = 12;``//文字大小` `styleContent.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;``styleContent.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;``styleContent.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;``styleContent.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;``var``rowCount = jsonObject.rows.Count;``//表格行数` `var``columnCount = jsonObject.columns.Count;``//表格列数` `//生成行1 标题行` `cells.Merge(0, 0, 1, columnCount);``//合并单元格` `cells[0, 0].PutValue(jsonObject.sheetName);``//填写内容` `cells[0, 0].Style = styleTitle;``cells.SetRowHeight(0, 25);``//生成题头列行` `for``(``int``i = 0; i < columnCount; i++)``{``cells[1, i].PutValue(jsonObject.columns[i][``"title"``]);``cells[1, i].Style = styleHeader;``cells.SetRowHeight(1, 23);``}``//生成内容行,第三行起始``//生成数据行` `for``(``int``i = 0; i < rowCount; i++)``{``for``(``int``k = 0; k < columnCount; k++)``{``var``currentColumnName = jsonObject.columns[k][``"field"``];``cells[2 + i, k].PutValue(jsonObject.rows[i][currentColumnName.Value]);``cells[2 + i, k].Style = styleContent;``}``cells.SetRowHeight(2 + i, 22);``}``//添加制表日期``cells[2 + rowCount, columnCount-1].PutValue(``"制表日期:"``+ DateTime.Now.ToShortDateString());``sheet.AutoFitColumns();``//让各列自适应宽度``sheet.AutoFitRows();``//让各行自适应宽度``return``workbook;``}`