NOPI操作Excel
来源：互联网 发布：tf卡写入数据错误 编辑：程序博客网 时间：2024/06/05 10:44
//自定义颜色 ，将颜色转换成NOPI的颜色private static short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour) { short s = 0; HSSFPalette XlPalette = workbook.GetCustomPalette(); HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B); if (XlColour == null) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64) { //NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64; //NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 65; XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B); } else { //XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B); } s = XlColour.Indexed; } } else s = XlColour.Indexed; return s; } /// <summary> /// 向Excel中插入图片,可设置图片大小,2.0的版本 /// </summary> /// <param name="sheet"></param> /// <param name="workbook"></param> /// <param name="fileurl"></param> /// <param name="row"></param> /// <param name="col"></param> private static void AddPieChart(ISheet sheet, HSSFWorkbook workbook, HSSFPatriarch patriarch, string fileurl, int row, int col, int size) { try { byte[] bytes = Picture.ReadPictureBytes(size, fileurl); //POI只支持DIB,EMF,JPEG,PICT,PNG,WMF格式 int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); //dx2最大值1023,dy2最大值255，刚好填充一个Cell //HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2) HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1020, 255, col, row + 1, col, row + 1); IPicture pict = patriarch.CreatePicture(anchor, pictureIdx); if (size != 0) pict.Resize();////图片显示原始大小 } catch (Exception ex) { LogInfo.Log(ex); throw ex; } }------------------------------------------------------------------NOPI操作Excel-----------------------------------------------------------------------------
NPOI教程
相关DLL
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
#region 读取Excel public static DataTable ImportExcel(string filePath) { try { //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档 FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); HSSFWorkbook workbook = new HSSFWorkbook(file); //获取excel的第一个sheet HSSFSheet sheet = workbook.GetSheetAt(0); DataTable table = new DataTable(); //获取sheet的首行 HSSFRow headerRow = sheet.GetRow(0); if (headerRow == null) headerRow = sheet.GetRow(1); //一行最后一个方格的编号 即总的列数 int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { HSSFCell cell = headerRow.GetCell(i); if (cell != null) { DataColumn column = new DataColumn(cell.StringCellValue); table.Columns.Add(column); } } //最后一列的标号 即总的行数 // int rowCount = sheet.LastRowNum; cellCount = table.Columns.Count; for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { HSSFRow row = sheet.GetRow(i); if (row == null) continue; DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { HSSFCell cell = row.GetCell(j); if (cell == null) continue; //读取Excel格式，根据格式读取数据类型 switch (cell.CellType) { case HSSFCellType.BLANK: //空数据类型处理 dataRow[j] = ""; break; case HSSFCellType.STRING: //字符串类型 dataRow[j] = cell.StringCellValue; break; case HSSFCellType.NUMERIC: //数字类型 if (HSSFDateUtil.IsCellDateFormatted(cell))//是否是日期类型 { dataRow[j] = cell.DateCellValue; } else { //Excel中内容为科学计数法解决方案 DecimalFormat df = new DecimalFormat("0"); System.Globalization.CultureInfo cf = new System.Globalization.CultureInfo("ZH-CN", true); dataRow[j] = df.Format(cell.NumericCellValue, cf); } break; case HSSFCellType.FORMULA: HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook); dataRow[j] = e.Evaluate(cell).StringValue; break; default: dataRow[j] = ""; break; } } table.Rows.Add(dataRow); } workbook = null; sheet = null; return table; } catch (Exception ex) { LogInfo.Log(ex); throw ex; return null; } } #endregion----------------------------------------------------------------------------------用模板导出Excel--------------------------------------------------------------------------------
/// <summary> /// 用模板导出Excel /// </summary> /// <param name="table"></param> /// <param name="strFileName">导出路径</param> /// <param name="templetPath">模板路径</param> /// <param name="startRow">从第几行开始写数据,从1开始</param> public static void ExportExcelByTemple(System.Data.DataTable dtSource, string strFileName, string templetPath, int rowHeight, int startRow, int size) { try { HSSFWorkbook workbook = getWorkBook(templetPath); HSSFSheet sheet = getSheet(workbook); writeData(workbook, sheet, dtSource, strFileName, rowHeight, startRow, size); saveData(workbook, strFileName); } catch (Exception ex) { LogInfo.Log(ex); throw ex; } } /// <summary> /// 解析Excel模板，返回WorkBook /// </summary> /// <param name="templetPath"></param> /// <returns></returns> private static HSSFWorkbook getWorkBook(string templetPath) { FileStream file = new FileStream(templetPath, FileMode.Open, FileAccess.Read); HSSFWorkbook workbook = new HSSFWorkbook(file); return workbook; } /// <summary> /// 返回Sheet /// </summary> /// <param name="workbook"></param> /// <returns></returns> private static HSSFSheet getSheet(HSSFWorkbook workbook) { return workbook.GetSheetAt(0); } /// <summary> /// 保存数据 /// </summary> /// <param name="workbook"></param> /// <param name="strFileName"></param> private static void saveData(HSSFWorkbook workbook, string strFileName) { //保存 using (MemoryStream ms = new MemoryStream()) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } } } /// <summary> /// /// </summary> /// <param name="workbook"></param> /// <param name="sheet"></param> /// <param name="dtSource"></param> /// <param name="strFileName"></param> /// <param name="rowHeight"></param> /// <param name="startRow"></param> /// <param name="size"></param> private static void writeData(HSSFWorkbook workbook, HSSFSheet sheet, System.Data.DataTable dtSource, string strFileName, int rowHeight, int startRow, int size) { // //填充表头 HSSFRow dataRow = new HSSFRow(); //不管你插入多少图片，都只要生成一个HSSFPatriarch 的对象,一定要放在循环外,只能声明一次,不然不能循环插入图片 HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); //填充内容 for (int i = 0; i < dtSource.Rows.Count; i++) { dataRow = sheet.CreateRow(i + startRow - 1); dataRow.Height = (short)(rowHeight * 20); for (int j = 0; j < dtSource.Columns.Count; j++) { if (dtSource.Columns[j].Caption.ToLower() == "picturePath") { string picurl = dtSource.Rows[i][j].ToString(); if (File.Exists(picurl)) { AddPieChart(sheet, workbook, patriarch, picurl, i + startRow - 2, j, size); } } else { string drValue = dtSource.Rows[i][j].ToString(); if (JXType.IsDecimal(drValue) && drValue.Length < 10) { double doubV = 0; double.TryParse(drValue, out doubV); dataRow.CreateCell(j).SetCellValue(doubV); } else { dataRow.CreateCell(j).SetCellValue(drValue); } } } } }-----------------------------------------------------------------------------------不用模板导出Excel--------------------------------------------------------------------------------
/// <summary> /// 不用模板导出Excel /// </summary> /// <param name="table"></param> /// <param name="strFileName"></param> public static void ExportExcel(System.Data.DataTable dtSource, string strFileName) { try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet(); sheet.DefaultColumnWidth = 20; sheet.DefaultRowHeight = 20 * 20; // //填充表头 HSSFRow dataRow = sheet.CreateRow(0); dataRow.Height = 20 * 20; HSSFCellStyle headStyle = workbook.CreateCellStyle(); SetCellStyle(headStyle, true); headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont(); font.FontHeightInPoints = 12; font.Boldweight = 50; // font.Color = NPOI.HSSF.Util.HSSFColor.GREEN.index; headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index; headStyle.SetFont(font); int c = 0; foreach (DataColumn column in dtSource.Columns) { sheet.AutoSizeColumn(c);//列宽自适应，不支持中文 dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); dataRow.GetCell(column.Ordinal).CellStyle = headStyle; c++; } //填充内容 for (int i = 0; i < dtSource.Rows.Count; i++) { dataRow = sheet.CreateRow(i + 1); dataRow.Height = 20 * 20; for (int j = 0; j < dtSource.Columns.Count; j++) { dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString()); } } //保存 using (MemoryStream ms = new MemoryStream()) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { workbook.Write(fs); } } workbook.Dispose(); } catch (Exception ex) { LogInfo.Log(ex); throw ex; } } /// <summary> /// 合并单元格 /// </summary> /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> /// <param name="isBorder">是否加边框</param> private static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend, bool isBorder){CellRangeAddress region = new CellRangeAddress(rowstart, rowend, colstart, colend); sheet.AddMergedRegion(region); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.DOTTED, NPOI.HSSF.Util.HSSFColor.RED.index);}//将自定颜色装换成NPOI的颜色private static short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour) { short s = 0; HSSFPalette XlPalette = workbook.GetCustomPalette(); HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B); if (XlColour == null) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64) { //NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64; //NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 65; XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B); } else { //XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B); } s = XlColour.Indexed; } } else s = XlColour.Indexed; return s; } 3 0
- NOPI操作Excel
- Nopi Excel下载功能
- NOPI导出excel
- nopi导入导出excel
- C# NOPI读取Excel
- NOPI\\Excel导入数据
- NOPI的操作
- NOPI用模板生成excel
- NOPI实现生成Excel数据表
- NOPI导出标准格式Excel
- C#利用NOPI处理Excel的代码
- Nopi导出excel 设置字体颜色
- C#利用NOPI处理Excel的代码
- 使用NOPI实现Excel导入导出类
- NOPI更新Excel的函数运算结果
- C#利用NOPI导出到Excel
- 使用NOPI读取Word、Excel文档内容
- 通过Nopi实现对execl的操作
- 在windows下编译chromium
- 进一步学习Android
- 好东西从来不会消失
- 表达式树的构建与显示，C＋＋沉思录第八章
- Oracle等待事件DFS lock handle
- NOPI操作Excel
- 推荐！国外程序员整理的 C++ 资源大全
- R：矩阵初识
- Redis和Memcache的区别
- EasyUI 兼容 IE6 方法总结
- 解决hibernate向mysql插入中文乱码问题
- 动态规划总结
- Redis和memcached
- 日语学习之沪江整理 20141027
