Csharp/C#教程：使用Npoi操作excel的解决办法分享

Npoi简介
——————————————————————————–

1.整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；单元格Cell。

2.Npoi下载地址：https://npoi.codeplex.com/releases/view/38113

4.忘了告诉大家npoi是做什么的了，npoi能够读写几乎所有的Office97-2003文件格式，至少能够支持Word,PowerPoint,Excel,Visio的格式。

——————————————————————————–

使用Npoi创建一个简单的xls文件

——————————————————————————–
代码如下:
//创建xls文件
       privatevoidbutton1_Click(objectsender,EventArgse)
       {
           //创建工作薄
           HSSFWorkbookwk=newHSSFWorkbook();
           //创建一个名称为mySheet的表
           ISheettb=wk.CreateSheet(“mySheet”);
           //创建一行，此行为第二行
           IRowrow=tb.CreateRow(1);
           for(inti=0;i<20;i++)   
           {
               ICellcell=row.CreateCell(i); //在第二行中创建单元格
               cell.SetCellValue(i);//循环往第二行的单元格中添加数据
           }
           using(FileStreamfs=File.OpenWrite(@”c:/myxls.xls”))//打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
           {
               wk.Write(fs);  //向打开的这个xls文件中写入mySheet表并保存。
               MessageBox.Show(“提示：创建成功！”);
           }
       }

使用Npoi读取一个简单的xls文件

——————————————————————————–
代码如下:
//读取xls文件
       privatevoidbutton2_Click(objectsender,EventArgse)
       {  StringBuildersbr=newStringBuilder();
           using(FileStreamfs=File.OpenRead(@”c:/myxls.xls”))  //打开myxls.xls文件
           {
               HSSFWorkbookwk=newHSSFWorkbook(fs);  //把xls文件中的数据写入wk中
               for(inti=0;i<wk.NumberOfSheets;i++) //NumberOfSheets是myxls.xls中总共的表数
               {
                   ISheetsheet=wk.GetSheetAt(i);  //读取当前表数据
                   for(intj=0;j<=sheet.LastRowNum;j++) //LastRowNum是当前表的总行数
                   {
                       IRowrow=sheet.GetRow(j); //读取当前行数据
                       if(row!=n ull)
                       {
                           sbr.Append(“————————————-rn”);//读取行与行之间的提示界限
                           for(intk=0;k<=row.LastCellNum;k++) //LastCellNum是当前行的总列数
                           {
                               ICellcell=row.GetCell(k); //当前表格
                               if(cell!=null)
                               {                                  
                                   sbr.Append(cell.ToString());  //获取表格中的数据并转换为字符串类型
                               }
                           }
                       }
                   }
               }              
           }
           sbr.ToString();
           using(StreamWriterwr=newStreamWriter(newFileStream(@”c:/myText.txt”,FileMode.Append))) //把读取xls文件的数据写入myText.txt文件中
           {
               wr.Write(sbr.ToString());
               wr.Flush();
           }
       }

——————————————————————————–

使用Npoi创建一个常用的xls文件

——————————————————————————–
代码如下:
//创建一个常用的xls文件
       privatevoidbutton3_Click(objectsender,EventArgse)
       {         
           IWorkbookwb=newHSSFWorkbook();
           //创建表 
           ISheetsh=wb.CreateSheet(“zhiyuan”);
           //设置单元的宽度 
           sh.SetColumnWidth(0,15*256);
           sh.SetColumnWidth(1,35*256);
           sh.SetColumnWidth(2,15*256);
           sh.SetColumnWidth(3,10*256);
           inti=0;
           #region练习合并单元格
           sh.AddMergedRegion(newNPOI.SS.Util.CellRangeAddress(0,0,0,3));

           //CellRangeAddress（）该方法的参数次序是：开始行号，结束行号，开始列号，结束列号。
           IRowrow0=sh.CreateRow(0);
           row0.Height=20*20;
           ICellicell1top0=row0.CreateCell(0);
           icell1top0.CellStyle=Getcellstyle(wb,stylexls.头);
           icell1top0.SetCellValue(“标题合并单元”);
           #endregion
           i++;
           #region设置表头
           IRowrow1=sh.CreateRow(1);
           row1.Height=20*20;

           ICellicell1top=row1.CreateCell(0);
           icell1top.CellStyle=Getcellstyle(wb,stylexls.头);
           icell1top.SetCellValue(“网站名”);

           ICellicell2top=row1.CreateCell(1);
           icell2top.CellStyle=Getcellstyle(wb,stylexls.头);
           icell2top.SetCellValue(“网址”);

           ICellicell3top=row1.CreateCell(2);
           icell3top.CellStyle=Getcellstyle(wb,stylexls.头);
           icell3top.SetCellValue(“百度快照”);

           ICellicell4top=row1.CreateCell(3);
           icell4top.CellStyle=Getcellstyle(wb,stylexls.头);
           icell4top.SetCellValue(“百度收录”);
           #endregion 
           using(FileStreamstm=File.OpenWrite(@”c:/myMergeCell.xls”))
           {
               wb.Write(stm);
               MessageBox.Show(“提示：创建成功！”);
           }
       }

       #region定义单元格常用到样式的枚举
       publicenumstylexls
       {
           头,
           url,
           时间,
           数字,
           钱,
           百分比,
           中文大写,
           科学计数法,
           默认
       }
       #endregion

       #region定义单元格常用到样式
       staticICellStyleGetcellstyle(IWorkbookwb,stylexlsstr)
       {
           ICellStylecellStyle=wb.CreateCellStyle();

           //定义几种字体 
           //也可以一种字体，写一些公共属性，然后在下面需要时加特殊的 
           IFontfont12=wb.CreateFont();
           font12.FontHeightInPoints=10;
           font12.FontName=”微软雅黑”;

           IFontfont=wb.CreateFont();
           font.FontName=”微软雅黑”;
           //font.Underline=1;下划线 

           IFontfontcolorblue=wb.CreateFont();
           fontcolorblue.Color=HSSFColor.OLIVE_GREEN.BLUE.index;
           fontcolorblue.IsItalic=true;//下划线 
           fontcolorblue.FontName=”微软雅黑”;

           //边框 
           cellStyle.BorderBottom=NPOI.SS.UserModel.BorderStyle.DOTTED;
           cellStyle.BorderLeft=NPOI.SS.UserModel.BorderStyle.HAIR;
           cellStyle.BorderRight=NPOI.SS.UserModel.BorderStyle.HAIR;
           cellStyle.BorderTop=NPOI.SS.UserModel.BorderStyle.DOTTED;
           //边框颜色 
           cellStyle.BottomBorderColor=HSSFColor.OLIVE_GREEN.BLUE.index;
           cellStyle.TopBorderColor=HSSFColor.OLIVE_GREEN.BLUE.index;

           //背景图形，我没有用到过。感觉很丑 
           //cellStyle.FillBackgroundColor=HSSFColor.OLIVE_GREEN.BLUE.index; 
           //cellStyle.FillForegroundColor=HSSFColor.OLIVE_GREEN.BLUE.index; 
           cellStyle.FillForegroundColor=HSSFColor.WHITE.index;
           //cellStyle.FillPattern=FillPatternType.NO_FILL; 
           cellStyle.FillBackgroundColor=HSSFColor.BLUE.index;

           //水平对齐 
           cellStyle.Alignment=NPOI.SS.UserModel.HorizontalAlignment.LEFT;

           //垂直对齐 
           cellStyle.VerticalAlignment=VerticalAlignment.CENTER;

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

           //缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对 
           cellStyle.Indention=0;

           //上面基本都是设共公的设置 
           //下面列出了常用的字段类型 
           switch(str)
           {
               casestylexls.头:
                   //cellStyle.FillPattern=FillPatternType.LEAST_DOTS; 
                   cellStyle.SetFont(font12);
                   break;
               casestylexls.时间:
                   IDataFormatdatastyle=wb.CreateDataFormat();

                   cellStyle.DataFormat=datastyle.GetFormat(“yyyy/mm/dd”);
                   cellStyle.SetFont(font);
                   break;
               casestylexls.数字:
                   cellStyle.DataFormat=HSSFDataFormat.GetBuiltinFormat(“0.00”);
                   cellStyle.SetFont(font);
                   break;
               casestylexls.钱:
                   IDataFormatformat=wb.CreateDataFormat();
                   cellStyle.DataFormat=format.GetFormat(“￥#,##0”);
                   cellStyle.SetFont(font);
                   break;
               casestylexls.url:
                   fontcolorblue.Underline=1;
                   cellStyle.SetFont(fontcolorblue);
                   break;
               casestylexls.百分比:
                   cellStyle.DataFormat=HSSFDataFormat.GetBuiltinFormat(“0.00%”);
                   cellStyle.SetFont(font);
                   break;
               casestylexls.中文大写:
                   IDataFormatformat1=wb.CreateDataFormat();
                   cellStyle.DataFormat=format1.GetFormat(“[DbNum2][$-804]0”);
                   cellStyle.SetFont(font);
                   break;
               casestylexls.科学计数法:
                   cellStyle.DataFormat=HSSFDataFormat.GetBuiltinFormat(“0.00E+00”);
                   cellStyle.SetFont(font);
                   break;
&nbsp ;              casestylexls.默认:
                   cellStyle.SetFont(font);
                   break;
           }
           returncellStyle;

       }
       #endregion

——————————————————————————–
提示：1.上述就是C#学习教程：使用Npoi操作excel的解决办法分享的全部内容，如果对大家有所用处且需要了解更多关于C#学习教程，希望大家多多关注—计算机技术网(www.ctvol.com)!