NPOI小结

1. 准备工作

(1)下载

http://npoi.codeplex.com/releases/下载stable包,最好不要下载Beta包,解包。

(2)添加引用

.net4为例,项目-添加引用-浏览,进入解压后的文件夹中的dotnet4文件夹,选ICsharpcode.SharpZipLib.dll,NPOI.dll,NPOI.00XML.dll,NPOI.OpenXml4Net.dll,NPOI.OpenXmlFormats.dll全部共五个dll,添加,确定。

(3)范例

在上述网站其他网页另行下载 ,以 xlsx为例,范例在examples\xssf文件夹。

(4)使用空间

xlsx为例。

using NPOI.SS.UserModel;

usingNPOI.XSSF.UserModel;

2. 创建一个xlsx,创建表,创建行列,列宽,行高,写入数据保存。

using System;

using System.Collections.Generic;

using System.Text;

using NPOI.XSSF.UserModel;

using NPOI.SS.UserModel;

using NPOI.SS.Util;

using System.IO;

……

 

IWorkbook wb =newXSSFWorkbook();

ISheet sheet = wb.CreateSheet("Sheet1");

sheet.SetColumnWidth(0,50 * 256);

sheet.CreateRow(0).Height= 100 * 20;

for (int i = 1; i <= 15; i++) {

  IRow row =sheet.CreateRow(i);

  for (int j = 0; j < 15; j++) {

    row.CreateCell(j, CellType.Numeric).SetCellValue(i+ j);

    //或row.CreateCell(j).SetCellValue(i+j);

    }

  }

  FileStream fs =File.Create("test.xlsx");

  wb.Write(fs);

  fs.Close();

 

CellType的取值可为:Blank,Boolean,Error,Formula,Numeric,String,Unknown中的一种

 

 

3. 读单元格

String str2=sheet.GetRow(1).GetCell(2).ToString();   

4. 样式

(1)创建样式

            IWorkbook workbook = newXSSFWorkbook();

            ISheet sheet =workbook.CreateSheet("Sheet A1");

            IRow row = sheet.CreateRow(1);

            // Create a cell and put a value init.

            ICell cell = row.CreateCell(1);

            ICellStyle style =workbook.CreateCellStyle();

(2)样式的边框

            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;

            style.BottomBorderColor =IndexedColors.Black.Index;

            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.DashDotDot;

            style.LeftBorderColor =IndexedColors.Green.Index;

            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Hair;

            style.RightBorderColor =IndexedColors.Blue.Index;

            style.BorderTop = NPOI.SS.UserModel.BorderStyle.MediumDashed;

            style.TopBorderColor =IndexedColors.Orange.Index;

(3)对齐

            style.Alignment =NPOI.SS.UserModel.HorizontalAlignment.Center;//因HorizontalAlignment有二义性,故必须加此空间

            style.VerticalAlignment =NPOI.SS.UserModel.HorizontalAlignment.Center;

(4)背景色    

           style.FillPattern=FillPatternType.SOLID_FOREGROUND;

           style.FillForegroundColor=IndexedColors.BrightGreen.Index;

            style.FillForeGroundColor=26;

(5)自动换行

 

            style.WrapText=true;//自动换行

(6)字体

          IFont font = workbook.CreateFont();

          font.Boldweight = (short)FontBoldWeight.Bold;;

          font.FontName="宋体";

          font.fontHeightInPoints=18;

          font.Color=10;

          style.SetFont(font);

           

(7)统一初始化样式

void InitialStyle(ref ICellStyle style)

        {

            style.VerticalAlignment =NPOI.SS.UserModel.VerticalAlignment.Center;

            style.Alignment =NPOI.SS.UserModel.HorizontalAlignment.Center;

            style.BorderBottom =NPOI.SS.UserModel.BorderStyle.Thin;

            style.BorderTop =NPOI.SS.UserModel.BorderStyle.Thin;

            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

            style.BorderLeft =NPOI.SS.UserModel.BorderStyle.Thin;

            style.WrapText=true;

        }

(8)使用样式

 

            cell.CellStyle = style;

(9)克隆样式

ICellStyle newCellStyle =wk.CreateCellStyle(); 

new CellStyle.CloneStyleFrom(Style);

          

5. 颜色

以下是颜色索引号。设置背景色和字体颜色时可直接引用,如style.FillForeGroundColor=26、font.Color=10等。

6. 同时给单元格赋值和样式

要给一个单元格赋值和指定样式,一般需要如下语句

sheet.GetRow(1).GetCell(1).SetCellValue(14);

sheet.GetRow(1).GetCell(1).CellStyle=style;

其中第二句中的sheet.GetRow(1).GetCell(1)与第一句中的相同,属于重要录入,效率很低。为解决这个问题,可为ICell对象增加一个扩展方法,使方法同时实现赋值和指定样式,这样可大幅提高效率,以下说明包含该扩展方法的辅助类及其用法。

(1) 辅助类

    public static class NPOIHelper {

        public static void SetNumeric(this ICell cell, double value,ICellStyle style) {

           cell.SetCellValue(value);

            cell.CellStyle = style;

        }

        public static voidSetString(this ICell cell, string value,ICellStyle style) {

            cell.SetCellValue(value);

            cell.CellStyle = style;

        }

        public static voidSetFormula(this ICell cell, string value,ICellStyle style) {

           cell.SetCellFormula(value);

            cell.CellStyle = style;

        }

    }

注意:类和方法均需为static,参数中的ICell对象前需加this。

(2)用法示例

sheetClass.GetRow(5).GetCell(3).SetString("普通",styleDigit);

row.CreateCell(j).SetNumeric(123,styleHD);

row.CreateCell(j).SetFormula("D4+D5"styleWidthBBold);

 

7. 合并单元格

  sheet1.AddMergedRegion(newCellRangeAddress(0, 0, 0, 29));

CellRangeAddress的原型是CellRangeAddress(int firstRow,int lastRow,firstCol,lastCol)。

8. 冻结数据

      

// 只冻结一行

           sheet.CreateFreezePane(0, 1, 0, 1);

            // 只冻结一列

           heet.CreateFreezePane(1, 0, 1, 0);

            // 冻结列和行

           sheet.CreateFreezePane(2, 2);

            // Create a split with the lower left side being the activequadrant

        sheet.CreateSplitPane(2000, 2000, 0, 0,PanePosition.LowerLeft);

 

 

9. DataGridView写入xlsx

 

publicstaticvoidDataGridViewToXLSX(string FileName,DataGridView dataGridView1,string sheetName) {

 

            intMaxRows = 1048575;

            //不允许dataGridView显示添加行,负责导出时会报最后一行未实例化错误

            dataGridView1.AllowUserToAddRows =false;

 

            IWorkbookwb = new XSSFWorkbook();

            intsheetNum = dataGridView1.Rows.Count / MaxRows + 1;

            ISheet[]sheetArray = new ISheet[sheetNum];

            //填写表头

            for(int i = 0; i < sheetNum; i++) {

                ISheetsh = wb.CreateSheet(sheetName + i.ToString().Trim());

                sheetArray[i] = sh;

 

                IRowrow = sheetArray[i].CreateRow(0);

                for(int j = 0; j < dataGridView1.Columns.Count;j++) {

                    row.CreateCell(j,CellType.String).SetCellValue(dataGridView1.Columns[j].HeaderText.ToString());

                }

            }

           

            inth = 0;//表号

            intm = 1;//行号

            for(int k = 0; k < dataGridView1.Rows.Count;k++) {

                IRowrow = sheetArray[h].CreateRow(m);

                //n是列号

                for(int n = 0; n < dataGridView1.Columns.Count;n++) {

                    row.CreateCell(n,CellType.String).SetCellValue(dataGridView1.Rows[k].Cells[n].Value.ToString());

                }

                m++;

                if(m == MaxRows + 1) {

                    h++;

                    m = 1;

                }

            }

 

            using(FileStream stream = File.OpenWrite(FileName)) {

                wb.Write(stream);

                stream.Close();

            }

            GC.Collect();

        }

10. DataTable写入xlsx

publicvoid TableToSheet(DataTable table,ISheet sheet,intstartRow,int startCol)

        {

            IRowrowHead = sheet.CreateRow(startRow);

            for(int j = 0; j < table.Columns.Count; j++)

            {

                rowHead.CreateCell(j+ startCol,CellType.String).SetCellValue(table.Columns[j].Caption.ToString());

            }

 

            for(int i = 1; i < table.Rows.Count+1; i++)

            {

 

                IRow rows = sheet.CreateRow(i + startRow);

                for (int j = 0; j< table.Columns.Count; j++)

                {

                   rows.CreateCell(j+startCol).SetCellValue(table.Rows[i-1][j].ToString());

                }

            }

        }

11. 列号转换列名(列名转换)

(1) 号转名

 

using System.Text.RegularExpressions;

  public static string ToName(int index)

        {

            if (index < 0) { throw new Exception("invalidparameter"); }

            List<string> chars = new List<string>();

            do

            {

                if (chars.Count > 0)index--;

                chars.Insert(0, ((char)(index %26 + (int)'A')).ToString());

                index = (int)((index - index % 26) / 26);

            } while (index > 0);

            return String.Join(string.Empty,chars.ToArray());

        }

(2)名转号

public static intToIndex(string columnName)

        {

            if (!Regex.IsMatch(columnName.ToUpper(),@"[A-Z]+")) { throw new Exception("invalid parameter"); }

            int index = 0;

            char[] chars =columnName.ToUpper().ToCharArray();

            for (int i = 0; i <chars.Length; i++)

            {

                index += ((int)chars[i] - (int)'A'+ 1) * (int)Math.Pow(26, chars.Length - i - 1);

            }

            return index - 1;

        }

 

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页