搜索到
3
篇与
的结果
-
vue使用xlsx修改样式导出excel 安装依赖npm install --save xlsx npm install --save xlsx-style //如果需要修改样式，则需要安装解决引入xlsx-style报错问题找到：node_modules\xlsx-style\dist\xlsx.full.min.js 并把这个js文件拷贝到static文件夹下再index.html中引入 新建downExcel.js文件import XLSX from "xlsx" // 配置 const wopts = { bookType: "xlsx", bookSST: true, type: "binary", cellStyles: true }; function downloadExl(json, config, type) { var tmpdata = json[0]; json.unshift({}); var keyMap = []; //获取keys for (var k in tmpdata) { keyMap.push(k); json[0][k] = k; } var tmpdata = []; //用来保存转换好的json let border = { bottom: { style: "thin", color: { rgb: "000000" } }, top: { style: "thin", color: { rgb: "000000" } }, left: { style: "thin", color: { rgb: "000000" } }, right: { style: "thin", color: { rgb: "000000" } } }; let style0 = { border: border, alignment: { horizontal: "center", wrapText: true, vertical: "center" }, font: { sz: 18, bold: true, color: { rgb: "000000" }, outline: true }, fill: { bgColor: { indexed: 64 } } }; let style1 = { border: border, alignment: { horizontal: "center", wrapText: true, vertical: "center" }, font: { sz: 12, bold: true, color: { rgb: "000000" }, outline: true }, fill: { bgColor: { indexed: 64 } } }; json .map((v, i) => keyMap.map((k, j) => Object.assign({}, { v: v[k], position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1) }) ) ) .reduce((prev, next) => prev.concat(next)) .forEach((v, i) => { // 在遍历时修改样式 let s = { font: { sz: 10, name: "微软雅黑" }, alignment: { horizontal: "center", wrapText: true, vertical: "center" } }; // 判断是否是标题 if (config._this.title.includes(v.v) && v.position.length == 2 && v.position[1] == "1") { s.fill = { fgColor: { rgb: "008000" } }; s.border = { right: { style: "thin", color: { rgb: "000000" } } }; // 判断是否是需要红色字体的标题字段 if (config._this.redTitle.includes(v.v)) { s.font.color = { rgb: "ff0000" }; } } tmpdata[v.position] = { v: v.v || "", s }; }); var outputPos = Object.keys(tmpdata); //设置区域,比如表格从A1到D10 //设置每列对应的宽度 tmpdata["!margins"] = [{ //工作表单元格合并配置项 可选 s: { //s start 开始 c: 0, //cols 开始列 r: 0 //rows 开始行 }, //开始 A1 e: { //e end 结束 c: 4, //cols 结束列 r: 0 //rows 结束行 } }, { s: { c: 0, r: 1 }, //开始 A2 e: { c: 1, r: 1 } //结束B2 }, { s: { c: 2, r: 1 }, //开始 C2 e: { c: 3, r: 1 } //结束D2 }, ] // !cols工作表列宽配置项 可选 tmpdata["!cols"] = config.colwidth; // 第一二行样式 tmpdata["A1"].s = style0; tmpdata["A2"].s = style1; tmpdata["C2"].s = style0; var tmpWB = { SheetNames: ["mySheet"], //工作表名数组 Sheets: { mySheet: Object.assign({}, tmpdata, //!ref设置工作表的范围 如 必须 否则不显示 { "!ref": outputPos[0] + ":" + outputPos[outputPos.length - 1] //设置填充区域 } ) } }; const tmpDown = new Blob( [ s2ab( XLSX.write( tmpWB, { bookType: type == undefined ? "xlsx" : type, bookSST: false, type: "binary" } //这里的数据是用来定义导出的格式类型 ) ) ], { type: "" } ); // 数据处理完后传入下载 saveAs( tmpDown, config._this.fileName + "." + (wopts.bookType == "biff2" ? "xls" : wopts.bookType) ); } // 获取26个英文字母用来表示excel的列 function getCharCol(n) { let temCol = "", s = "", m = 0; while (n > 0) { m = (n % 26) + 1; s = String.fromCharCode(m + 64) + s; n = (n - m) / 26; } return s; } function s2ab(s) { if (typeof ArrayBuffer !== "undefined") { var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff; return buf; } else { var buf = new Array(s.length); for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xff; return buf; } } // 下载功能 function saveAs(obj, fileName) { var tmpa = document.createElement("a"); tmpa.download = fileName || "未命名"; // 兼容ie 火狐 下载文件 if ("msSaveOrOpenBlob" in navigator) { window.navigator.msSaveOrOpenBlob(obj, fileName); } else if (window.navigator.userAgent.includes("Firefox")) { var a = document.createElement("a"); a.href = URL.createObjectURL(obj); a.download = fileName; document.body.appendChild(a); a.click(); } else { tmpa.href = URL.createObjectURL(obj); } tmpa.click(); setTimeout(function () { URL.revokeObjectURL(obj); }, 100); } export default downloadExl;使用import downloadfn from "@/config/downExcel data(){ return{ fileName:"testExcel", xmindJSON: [ { Code: 1, Name: "里斯", GBPerfix: "ABC-F", stop: "测试" }, { Code: 2, Name: "里斯1", GBPerfix: "ABC-B", stop: "测试1" }, { Code: 3, Name: "里斯2", GBPerfix: "ABC-D", stop: "测试1" } ], config: { colwidth: [ { wpx: 169 }, { wpx: 63 }, { wpx: 203 }, { wpx: 128 }, ], _this: this }, title: ["功能模块与路径", "用例编号", "前置条件"] } }methods:{ export() { downloadfn(this.xmindJSON, this.config); } } -
使用NPOI操作Excel时设置超链接和文字颜色 HSSFWorkbook hssfworkbook = new HSSFWorkbook();//创建HSSFWorkbook ISheet sheet = hssfworkbook.CreateSheet("sheet1");//创建工作表 ICell cell = sheet.CreateRow(0).CreateCell(0);//创建单元格 #region 设置超链接 cell.SetCellValue("链接");//设置显示文本 HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.URL);//建一个HSSFHyperlink实体指明链接类型为URL（这里是枚举，可以根据需求自行更改） link.Address = "http://blog.csdn.net/shiershilian";//给HSSFHyperlink的地址赋值 cell.Hyperlink = link;//将链接方式赋值给单元格的Hyperlink即可将链接附加到单元格上 #endregion #region 设置字体 IFont font = hssfworkbook.CreateFont();//创建字体样式 font.Color = HSSFColor.BLUE.Index;//设置字体颜色 ICellStyle style = hssfworkbook.CreateCellStyle();//创建单元格样式 style.SetFont(font);//设置单元格样式中的字体样式 cell.CellStyle = style;//为单元格设置显示样式 #endregion
-
NPOI读取、写入Excel 简介使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。代码封装using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; namespace Genersoft.GS.ZDB.Development.Controller.RO { public class NPOIHelper : IDisposable { private string fileName = null; //文件名 private IWorkbook workbook = null; private FileStream fs = null; private bool disposed; public NPOIHelper(string fileName) { this.fileName = fileName; disposed = false; } #region 导出Excel 根据datatable的格式导出对应的格式 /// <summary> /// 导出Excel 根据datatable的格式导出对应的格式 /// </summary> /// <param name="fileName">保存路径</param> /// <param name="dtSource">导出的数据源</param> /// <param name="sheetName">创建的sheet表名称</param> /// <param name="isColumnWritten">是否写入列名作为Excel头</param> /// <returns></returns> public int DataTableToExcel( DataTable dtSource, string sheetName, bool isColumnWritten) { FileStream fs = null; int i = 0; int j = 0; int count = 0; IWorkbook workbook = null; ISheet sheet = null; try { using (fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(); if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } else { return -1; } ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy/mm/dd"); ICellStyle dateStyleStr = workbook.CreateCellStyle(); IDataFormat formatStr = workbook.CreateDataFormat(); dateStyleStr.DataFormat = formatStr.GetFormat("@"); if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < dtSource.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(dtSource.Columns[j].ColumnName); } count = 1; } else { count = 0; } foreach (DataRow dr in dtSource.Rows) { IRow row = sheet.CreateRow(count); foreach (DataColumn column in dtSource.Columns) { ICell newCell = row.CreateCell(column.Ordinal); string drValue = dr[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); newCell.SetCellType(CellType.String); newCell.CellStyle = dateStyleStr; break; case "System.DateTime"://日期类型 DateTime dateV; if (!string.IsNullOrEmpty(drValue)) { DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); } newCell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); newCell.SetCellType(CellType.Boolean); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": if (!string.IsNullOrEmpty(drValue)) { int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); newCell.SetCellType(CellType.Numeric); } break; case "System.Decimal"://浮点型 case "System.Double": if (!string.IsNullOrEmpty(drValue)) { double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); newCell.SetCellType(CellType.Numeric); } break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } ++count; } for ( i = 0; i <= sheet.LastRowNum; i++) { sheet.AutoSizeColumn(i,true); } sheet.SetColumnWidth(4, 10 * 256); sheet.SetColumnWidth(5, 10 * 256); if (dtSource.Columns.Count >= 13) { sheet.SetColumnWidth(13, 10 * 256); } workbook.Write(fs); //写入到excel} return count; } } catch (Exception ex) { fs.Dispose(); fs.Close(); throw; } } #endregion #region 导出Excel 根据datatable的格式导出对应的格式 /// <summary> /// 导出Excel 根据datatable的格式导出对应的格式 /// </summary> /// <param name="fileName">保存路径</param> /// <param name="dtSource">导出的数据源</param> /// <param name="sheetName">创建的sheet表名称</param> /// <param name="isColumnWritten">是否写入列名作为Excel头</param> /// <returns></returns> public int DataTableToExcelString(DataTable dtSource, string sheetName, bool isColumnWritten) { FileStream fs = null; int i = 0; int j = 0; int count = 0; IWorkbook workbook = null; ISheet sheet = null; try { using (fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(); if (workbook != null) { sheet = workbook.CreateSheet(sheetName); } else { return -1; } ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy/mm/dd hh:mm:ss"); ICellStyle dateStyleStr = workbook.CreateCellStyle(); IDataFormat formatStr = workbook.CreateDataFormat(); dateStyleStr.DataFormat = formatStr.GetFormat("@"); if (isColumnWritten == true) //写入DataTable的列名 { IRow row = sheet.CreateRow(0); for (j = 0; j < dtSource.Columns.Count; ++j) { row.CreateCell(j).SetCellValue(dtSource.Columns[j].ColumnName); } count = 1; } else { count = 0; } foreach (DataRow dr in dtSource.Rows) { IRow row = sheet.CreateRow(count); foreach (DataColumn column in dtSource.Columns) { ICell newCell = row.CreateCell(column.Ordinal); string drValue = dr[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); newCell.CellStyle = dateStyleStr; break; case "System.DateTime"://日期类型 DateTime dateV; if (!string.IsNullOrEmpty(drValue)) { DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); } newCell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": if (!string.IsNullOrEmpty(drValue)) { int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); newCell.SetCellType(CellType.Numeric); } break; case "System.Decimal"://浮点型 case "System.Double": if (!string.IsNullOrEmpty(drValue)) { double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); newCell.SetCellType(CellType.Numeric); } break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } ++count; } for (i = 0; i < sheet.LastRowNum; i++) { sheet.AutoSizeColumn(i, true); } workbook.Write(fs); //写入到excel} return count; } } catch (Exception ex) { fs.Dispose(); fs.Close(); throw; } } #endregion #region 将excel中的数据导入到DataTable中 /// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param name="sheetName">excel工作薄sheet的名称</param> /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) { ISheet sheet = null; DataTable data = new DataTable(); 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); throw; } } #endregion public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } protected virtual void Dispose(bool disposing) { if (!this.disposed) { if (disposing) { if (fs != null) fs.Close(); } fs = null; disposed = true; } } } }
