选取POI在Excel文书档案大肆单元格写入数据,可控

亟需引进dll文件  

using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.DDF;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.SS;  
#region 导出
        protected void btnexcel_Click(object sender, EventArgs e)
        {
            QQT_BLL.gg_content bll = new QQT_BLL.gg_content();
            DataSet ds = bll.getds("");
            DataTable dt = ds.Tables[0];
            string excelname = System.DateTime.Now.ToString().Replace(":", "").Replace("-", "").Replace(" ", "");
            string filePath = System.Web.HttpContext.Current.Server.MapPath("ReadExcel") "\" excelname ".xls";
            MemoryStream ms = RenderDataTableToExcel(dt) as MemoryStream;
            FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write);
            byte[] data = ms.ToArray();
            fs.Write(data, 0, data.Length);
            fs.Flush();
            fs.Close();
            data = null;
            ms = null;
            fs = null;

在大家实际上的花销中,表现层的施工方案纵然有一而再连续串,可是IE浏览器已产生最几个人利用的浏览器,因为大家都用Windows。在店堂办公系统中,常有顾客那样子供给:你要把大家的表格间接用Excel打开(邮电通讯系统、银行系统)。或然是:我们曾经何足为奇用Excel打字与印刷。

也能够在NuGet里面管理(推荐卡塔尔 相比较方便 。

            #region 导出到客商端
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.AppendHeader("content-disposition", "attachment;filename=" System.Web.HttpUtility.UrlEncode(excelname, System.Text.Encoding.UTF8) ".xls");
            Response.ContentType = "Application/excel";
            Response.WriteFile(filePath);
            Response.End();
            #endregion
        }
        public Stream RenderDataTableToExcel(DataTable SourceTable)
        {
         
            MemoryStream ms = new MemoryStream();
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
            NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);

    Apache的Jakata项目的POI子项目,前段时间可比早熟的是HSSF接口,管理MSExcel对象。它不象大家独有是用csv生成的还未格式的能够由Excel转变的东西,而是真正的Excel对象,你能够操纵一些性情如sheet,cell等等。

 图片 1

          //设置7列宽为100
            sheet.SetColumnWidth(7, 100);
         //加标题

第风流洒脱,理解一下三个Excel的公文的团伙方式,多个Excel文件对应于三个workbook(HSSFWorkbook),二个workbook能够有八个sheet(HSSFSheet卡塔 尔(英语:State of Qatar)组成,二个sheet是由三个row(HSSFRow卡塔尔国组成,叁个row是由多少个cell(HSSFCell卡塔 尔(英语:State of Qatar)组成。

 

            headerRow.CreateCell(0).SetCellValue("广告编号");
            headerRow.CreateCell(1).SetCellValue("广告标题");
            headerRow.CreateCell(2).SetCellValue("广告内容");
            headerRow.CreateCell(3).SetCellValue("所属广告商");
            headerRow.CreateCell(4).SetCellValue("广告电话");
            headerRow.CreateCell(5).SetCellValue("广告网站");
            headerRow.CreateCell(6).SetCellValue("广告小图片");
            headerRow.CreateCell(7).SetCellValue("图片展现");
            headerRow.CreateCell(8).SetCellValue("广告大图片");
            headerRow.CreateCell(9).SetCellValue("图片展示");
            headerRow.CreateCell(10).SetCellValue("检查核对情形");

    那么,怎样采取POI在Excel文档任意单元格写入数据?近年来做了个连串,一人钻探了下,今后把代码拿出来和大家大饱眼福下!白璧微瑕请前辈们多多关照!

 

            int rowIndex = 1;
            foreach (DataRow row in SourceTable.Rows)
            {
                NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
                dataRow.Height = 50;

Reportbuilder 代码 图片 2 图片 3图片 4

 

               //列高50
                
                dataRow.CreateCell(0).SetCellValue(row["gg_id"].ToString());
                dataRow.CreateCell(1).SetCellValue(row["gg_title"].ToString());
                dataRow.CreateCell(2).SetCellValue(row["gg_memo"].ToString());
                dataRow.CreateCell(3).SetCellValue(row["ggs_name"].ToString());
                dataRow.CreateCell(4).SetCellValue(row["gg_tel"].ToString());
                dataRow.CreateCell(5).SetCellValue(row["gg_add"].ToString());
                dataRow.CreateCell(6).SetCellValue(row["p_name"].ToString());
                string picurl = row["p_url"].ToString();  //图片存款和储蓄路线             
                dataRow.CreateCell(8).SetCellValue(row["gg_check"].ToString());
                AddPieChart(sheet,  workbook, picurl,rowIndex ,7);
                rowIndex ;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            headerRow = null;
            workbook = null;
            return ms;
        }
      
        ///
        /// 向sheet插入图片
        ///
        ///
        ///
        private void AddPieChart(ISheet sheet,   HSSFWorkbook  workbook, string fileurl,int row,int col)
        {
            try
            {
                //add picture data to this workbook.
                string path = Server.MapPath("~/html/");
                if (fileurl.Contains("/"))
                {
                    path = fileurl.Substring( fileurl.IndexOf ('/'));
                }
                string FileName = path;
                byte[] bytes = System.IO.File.ReadAllBytes(FileName);

  1. import java.awt.image.BufferedImage; 
  2. import java.io.ByteArrayOutputStream; 
  3. import java.io.File; 
  4. import java.io.FileInputStream; 
  5. import java.io.FileOutputStream; 
  6. import java.io.IOException; 
  7.  
  8. import javax.imageio.ImageIO; 
  9. import javax.imageio.stream.ImageInputStream; 
  10.  
  11. import org.apache.poi.hssf.record.PageBreakRecord.Break; 
  12. import org.apache.poi.hssf.usermodel.HSSFCell; 
  13. import org.apache.poi.hssf.usermodel.HSSFClientAnchor; 
  14. import org.apache.poi.hssf.usermodel.HSSFPatriarch; 
  15. import org.apache.poi.hssf.usermodel.HSSFRow; 
  16. import org.apache.poi.hssf.usermodel.HSSFSheet; 
  17. import org.apache.poi.hssf.usermodel.HSSFWorkbook; 
  18. import org.apache.poi.poifs.filesystem.POIFSFileSystem; 
  19.  
  20. public class ReportBuilder  
  21.     public static String outFile_Erro = "Load Template Erro,文件加载失利!!"; 
  22.      
  23.     FileOutputStream  fileOutputStream  =  null; 
  24.      
  25.     HSSFWorkbook workbook = null; 
  26.     HSSFSheet   sheet = null; 
  27.  
  28.     HSSFPatriarch  patriarch  =  null; 
  29.      
  30.     /** 
  31.      * @用项:加载叁个早就存在的模板,将转换的内容保留到 workbook中 
  32.      * @参数:String templateFile:指索要加载的沙盘的门路,如:"C:/Tamplates/texting-1.xls" 
  33.      * @用法:templateFile:  String  templateFile_Name1 = "C:/Tamplates/texting-1.xls"          
  34.      * @author Yangcl 
  35.      */  
  36.     public  void loadTemplate(String templateURL) 
  37.     { 
  38.         // TODO Auto-generated method stub 
  39.         boolean a = templateURL.trim().indexOf(".xls") == -1; 
  40.         boolean b = templateURL.trim().indexOf(".XLS") == -1; 
  41. //      boolean c = templateURL.trim().indexOf(".xlsx") == -1; 
  42. //      boolean d = templateURL.trim().indexOf(".XLSX") == -1; 
  43.         if(templateURL == null  || templateURL.trim().equals("") ) 
  44.         { 
  45.             //文件不能为空提醒 
  46.             System.out.println("文件无法为空提示"); 
  47.         } 
  48.         else if(a&&b)// && c&&d) 
  49.         { 
  50.             System.out.println("文件格式不许确!"); 
  51.              
  52.         } 
  53.         else{ 
  54.             try{ 
  55.                 FileInputStream templateFile_Input = new FileInputStream(templateURL);           
  56.                 POIFSFileSystem fs = new POIFSFileSystem(templateFile_Input); 
  57.                  
  58.                 workbook = new  HSSFWorkbook(fs); 
  59.                 sheet =  workbook.getSheetAt(0); 
  60.                  
  61.                 System.out.println("========" templateUCRUISERL "文件加载已形成========"); 
  62.             }catch(Exception e){ 
  63.                 System.err.println(outFile_Erro); 
  64.             }    
  65.         } 
  66.                   
  67.     } 
  68.      
  69.     /** 
  70.      * 写入非图片格式新闻 
  71.      * @描述:那是叁个实体类,提供了相应的接口,用于操作Excel,在随性所欲坐标处写入数据。 
  72.      * @参数:String newContent:你要输入的开始和结果 
  73.      *               int beginRow :行坐标,Excel从 0 算起 
  74.      *               int beginCol   :列坐标,Excel从 0 算起 
  75.      * @author Yangcl 
  76.      */ 
  77.     public void writeInTemplate( String newContent, int beginRow, int beginCell) 
  78.     {    
  79.         HSSFRow  row  = sheet.getRow(beginRow);  
  80.         if(null == row ){ 
  81.             //借使不做空判别,你必须要令你的模板文件画好边框,beginRow和beginCell必需在边框最大值以内 
  82.             //不然会并发空指针相当 
  83.             row = sheet.createRow(beginRow); 
  84.         } 
  85.         HSSFCell   cell   = row.getCell(beginCell); 
  86.         if(null == cell){ 
  87.             cell = row.createCell(beginCell); 
  88.         } 
  89.         //设置存入内容为字符串 
  90.         cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
  91.         //向单元格中归入值 
  92.         cell.setCellValue(newContent);     
  93.     } 
  94.      
  95.      
  96.     /** 
  97.      * 写入图片格式新闻 
  98.      * @描述:那是八个实体类,提供了相应的接口,用于操作Excel,在自由坐标处写入数据。 
  99.      * @参数: 
  100.      *               String  imageFileUPAJEROL:他担任叁个外场盛传的图纸路线,图片以  *.jpeg 格局存在。 
  101.      *  
  102.      * @用法: 
  103.      *               ReportBuilder twi = new ReportBuilder(); 
  104.      *               String imageFileURL = "D:/workspace/Tamplates/1.jpeg";  
  105.      *               twi.writeInTemplate(imageFileURL , 0,0, 0, 0, (short)6, 5, (short)8, 8); 
  106.      *  
  107.      * @param dx1 :第一个cell开始的X坐标 
  108.      * @param dy1 :第一个cell开始的Y坐标 
  109.      * @param dx2 :第二个cell开始的X坐标 
  110.      * @param dy2 :第二个cell开始的Y坐标 
  111.      * @param col1   :图片的左上角放在第多少个列cell (the column(o based); of the first cell) 
  112.      * @param row1  :图片的左上角放在第几个行cell (the row(o based); of the first cell) 
  113.      * @param col2   :图片的右下角放在第多少个列cell (the column(o based); of the second cell) 
  114.      * @param row2  :图片的右下角放在第多少个行cell (the row(o based); of the second cell) 
  115.      *  
  116.      * @author Yangcl 
  117.      */ 
  118.     public  void writeInTemplate(String  imageFileURL , int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2  ) 
  119.     {    
  120.         BufferedImage  bufferImage = null; 
  121.          
  122.         //写入图片格式音信 
  123.         try 
  124.         {            
  125.             ByteArrayOutputStream     byteArrayOutputStream = new ByteArrayOutputStream(); 
  126.              
  127.             //先把读入的图样放到第四个 ByteArrayOutputStream 中,用于发生ByteArray    
  128.             File fileImage = new File(imageFileURL);     
  129.              
  130.             bufferImage = ImageIO.read(fileImage);       
  131.             ImageIO.write(bufferImage, "JPG", byteArrayOutputStream); 
  132.             System.out.println("ImageIO 写入完成"); 
  133.              
  134.             //希图插入图片 
  135.             HSSFPatriarch  patriarch  =  sheet.createDrawingPatriarch(); 
  136.             HSSFClientAnchor  anchor  =   new  HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);     
  137.              
  138.             //插入图片   
  139.             byte[] pictureData = byteArrayOutputStream.toByteArray(); 
  140.             int pictureFormat   = HSSFWorkbook.PICTURE_TYPE_JPEG; 
  141.             int pictureIndex = workbook.addPicture(pictureData, pictureFormat); 
  142.             patriarch.createPicture(anchor, pictureIndex); 
  143.              
  144.         }catch(Exception e){ 
  145.             e.printStackTrace(); 
  146.             System.out.println("IO Erro:" e.getMessage()); 
  147.         }finally 
  148.         { 
  149.             if(fileOutputStream != null) 
  150.             { 
  151.                 try{ 
  152.                     fileOutputStream.close(); 
  153.                 }catch(IOException io){ 
  154.                     io.printStackTrace(); 
  155.                 } 
  156.             } 
  157.         } 
  158.     } 
  159.      
  160.     /** 
  161.      * 写入图片格式音讯 
  162.      * @描述:那是三个实体类,提供了对应的接口,用于操作Excel,在自便坐标处写入数据。 
  163.      * @参数: 
  164.      *               ImageInputStream imageInputStream:他经受四个外场流传的图形流,图片以流情势存在。 
  165.      *  
  166.      * @用法: 
  167.      *  
  168.      *  
  169.      *  
  170.      *  
  171.      * @param dx1 :第一个cell开始的X坐标 
  172.      * @param dy1 :第一个cell开始的Y坐标 
  173.      * @param dx2 :第二个cell开始的X坐标 
  174.      * @param dy2 :第二个cell开始的Y坐标 
  175.      * @param col1   :图片的左上角放在第多少个列cell (the column(o based); of the first cell) 
  176.      * @param row1  :图片的左上角放在第多少个行cell (the row(o based); of the first cell) 
  177.      * @param col2   :图片的右下角放在第多少个列cell (the column(o based); of the second cell) 
  178.      * @param row2  :图片的右下角放在第多少个行cell (the row(o based); of the second cell) 
  179.      *  
  180.      * @author Yangcl 
  181.      */ 
  182.     public  void writeInTemplate(ImageInputStream imageInputStream , int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2  ) 
  183.     { 
  184.         BufferedImage  bufferImage = null;       
  185.         //写入图片格式新闻 
  186.         try 
  187.         {            
  188.             ByteArrayOutputStream     byteArrayOutputStream = new ByteArrayOutputStream();       
  189.             //先把读入的图样放到叁个 ByteArrayOutputStream 中,用于发生ByteArray          
  190.             bufferImage = ImageIO.read(imageInputStream);        
  191.             ImageIO.write(bufferImage, "JPG", byteArrayOutputStream); 
  192.             System.out.println("ImageIO 写入完毕"); 
  193.              
  194.             //计划插入图片 
  195.             HSSFPatriarch  patriarch  =  sheet.createDrawingPatriarch(); 
  196.             HSSFClientAnchor  anchor  =   new  HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);     
  197.              
  198.             //插入图片   
  199.             byte[] pictureData = byteArrayOutputStream.toByteArray(); 
  200.             int pictureFormat   = HSSFWorkbook.PICTURE_TYPE_JPEG; 
  201.             int pictureIndex = workbook.addPicture(pictureData, pictureFormat); 
  202.             patriarch.createPicture(anchor, pictureIndex); 
  203.         }catch(Exception e){ 
  204.             e.printStackTrace(); 
  205.             System.out.println("IO Erro:" e.getMessage()); 
  206.         }finally 
  207.         { 
  208.             if(fileOutputStream != null) 
  209.             { 
  210.                 try{ 
  211.                     fileOutputStream.close(); 
  212.                 }catch(IOException io){ 
  213.                     io.printStackTrace(); 
  214.                 } 
  215.             } 
  216.         } 
  217.     } 
  218.     /** 
  219.      * 保存模板 
  220.      * @描述:这一个格局用于保存workbook(职业薄)中的内容,并写入到三个Excel文件中 
  221.      * @参数:String templateFile:得到已经保存的类模板 路线名称 
  222.      * @用法:templateFile:String  templateFile_Name1 = "C:/Tamplates/texting-1.xls" 
  223.      *                TemplateAdapter ta  = new TemplateAdapter(); 
  224.      *                ta.SaveTemplate(templateFile_Name1); 
  225.      * @param templateFile 
  226.      */ 
  227.     public void SaveTemplate(String templateFile) 
  228.     { 
  229.         try{ 
  230.              
  231.             //创立输出流 
  232.             fileOutputStream = new FileOutputStream(templateFile); 
  233.             workbook.write(fileOutputStream); 
  234.          
  235.         }catch(Exception e){ 
  236.             e.printStackTrace(); 
  237.             System.out.println("IO Erro" e.getMessage()); 
  238.         }finally 
  239.         { 
  240.             if(fileOutputStream != null) 
  241.             { 
  242.                 try{ 
  243.                     fileOutputStream.close(); 
  244.                 }catch(IOException io){ 
  245.                     io.printStackTrace(); 
  246.                 } 
  247.             } 
  248.         } 
  249.     } 
  250.  
  251.      
  252. import java.awt.image.BufferedImage; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException;

    import javax.imageio.ImageIO; import javax.imageio.stream.ImageInputStream;

    import org.apache.poi.hssf.record.PageBreakRecord.Break; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem;

    public class ReportBuilder { public static String outFile_Erro = "Load Template Erro,文件加载战败!!";

    FileOutputStream fileOutputStream = null;

    HSSFWorkbook workbook = null; HSSFSheet sheet = null;

    HSSFPatriarch patriarch = null;

    /**

    * @用途:加载一个已经存在的模板,将生成的内容保存到 workbook中
    * @参数:String templateFile:指索要加载的模板的路径,如:"C:/Tamplates/texting-1.xls"
    * @用法:templateFile:  String  templateFile_Name1 = "C:/Tamplates/texting-1.xls"         
    * @author Yangcl
    */ 
    

    public void loadTemplate(String templateURL) {

       // TODO Auto-generated method stub
       boolean a = templateURL.trim().indexOf(".xls") == -1;
       boolean b = templateURL.trim().indexOf(".XLS") == -1;
    

    // boolean c = templateURL.trim().indexOf(".xlsx") == -1; // boolean d = templateURL.trim().indexOf(".XLSX") == -1;

       if(templateURL == null  || templateURL.trim().equals("") )
       {
           //文件不能为空提示
           System.out.println("文件不能为空提示");
       }
       else if(a&&b)// && c&&d)
       {
           System.out.println("文件格式不正确!");
    
       }
       else{
           try{
               FileInputStream templateFile_Input = new FileInputStream(templateURL);          
               POIFSFileSystem fs = new POIFSFileSystem(templateFile_Input);
    
               workbook = new  HSSFWorkbook(fs);
               sheet =  workbook.getSheetAt(0);
    
               System.out.println("========" templateURL "文件加载已完成========");
           }catch(Exception e){
               System.err.println(outFile_Erro);
           }   
       }
    

    }

    /**

    * 写入非图片格式信息
    * @描述:这是一个实体类,提供了相应的接口,用于操作Excel,在任意坐标处写入数据。
    * @参数:String newContent:你要输入的内容
    *               int beginRow :行坐标,Excel从 0 算起
    *               int beginCol   :列坐标,Excel从 0 算起
    * @author Yangcl
    */
    

    public void writeInTemplate( String newContent, int beginRow, int beginCell) {

       HSSFRow  row  = sheet.getRow(beginRow); 
       if(null == row ){
           //如果不做空判断,你必须让你的模板文件画好边框,beginRow和beginCell必须在边框最大值以内
           //否则会出现空指针异常
           row = sheet.createRow(beginRow);
       }
       HSSFCell   cell   = row.getCell(beginCell);
       if(null == cell){
           cell = row.createCell(beginCell);
       }
       //设置存入内容为字符串
       cell.setCellType(HSSFCell.CELL_TYPE_STRING);
       //向单元格中放入值
       cell.setCellValue(newContent);    
    

    }

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CSR_Web.Common
{
    public   class NPOIExport
    {
        public static NPOI.HSSF.UserModel.HSSFWorkbook DoExport(System.Data.DataTable dt, string notile)
        {

            //创建工作簿
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //创建表
            NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(notile);
            //自适应列宽
          //  sheet.AutoSizeColumn(1, true);
            //标题行合并单元格
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count-1));




            NPOI.SS.UserModel.IRow firstrow = sheet.CreateRow(0);
            NPOI.SS.UserModel.ICell firstcell = firstrow.CreateCell(0);

            //表名样式
            NPOI.SS.UserModel.ICellStyle styleHeader = book.CreateCellStyle();
            NPOI.SS.UserModel.IFont fontHeader = book.CreateFont();
            styleHeader.Alignment =NPOI.SS.UserModel.HorizontalAlignment.Center;
            styleHeader.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            fontHeader.FontHeightInPoints =20;

            styleHeader.SetFont(fontHeader);
            firstcell.CellStyle = styleHeader;
            firstcell.SetCellValue(notile);

            try
            {
                //列名样式
                NPOI.SS.UserModel.ICellStyle styleColName = book.CreateCellStyle();
                NPOI.SS.UserModel.IFont fontColName = book.CreateFont();
                styleColName.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                styleColName.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                fontColName.FontHeightInPoints = 14;
                styleColName.SetFont(fontColName);


                //数据的样式、字体大小
                NPOI.SS.UserModel.ICellStyle styleBody = book.CreateCellStyle();
                NPOI.SS.UserModel.IFont fontBody = book.CreateFont();

                styleBody.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                styleBody.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                fontBody.FontHeightInPoints = 12;
                styleBody.SetFont(fontBody);




                //创建具体单元格数据
                int rowCount = dt.Rows.Count;
                int colCount = dt.Columns.Count;

                NPOI.SS.UserModel.IRow colNameRow = sheet.CreateRow(1);
                for (int x = 0; x < colCount; x  ) { //将列名写入单元格
                    NPOI.SS.UserModel.ICell colNameCell = colNameRow.CreateCell(x);
                    colNameCell.SetCellValue(dt.Columns[x].ColumnName);
                    colNameCell.CellStyle = styleColName;
                }

                    for (int i = 0; i < rowCount; i  )
                    {
                        NPOI.SS.UserModel.IRow row = sheet.CreateRow(i   2);//数据从第三行开始 第一行表名 第二行列名

                        for (int j = 0; j < colCount; j  )
                        {
                            //填充数据
                            NPOI.SS.UserModel.ICell cell = row.CreateCell(j);

                            if (dt.Rows[i][j] != null)
                            {

                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            }
                            else
                            {
                                cell.SetCellValue("");
                            }




                            cell.CellStyle = styleBody;
                        }
                    }
                //自适应列宽
                for (int x = 0; x < colCount; x  )
                {
                    sheet.AutoSizeColumn(x, true);


   }

                if (!string.IsNullOrEmpty(FileName))
                {
                    int pictureIdx = workbook.AddPicture(bytes,NPOI .SS .UserModel .PictureType.JPEG);                  
                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 100, 50, col , row , col 1, row 1);
                    //##管理照片地点,【图片左上角为(col, row卡塔 尔(阿拉伯语:قطر‎第row 1行col 1列,右下角为( col 1, row 1卡塔尔第 col 1 1行row 1 1列,宽为100,高为50

    /**
     * 写入图片格式信息
     * @描述:这是一个实体类,提供了相应的接口,用于操作Excel,在任意坐标处写入数据。
     * @参数:
     *               String  imageFileURL:他接受一个外界传入的图片路径,图片以  *.jpeg 形式存在。
     * 
     * @用法:
     *               ReportBuilder twi = new ReportBuilder();
     *               String imageFileURL = "D:/workspace/Tamplates/1.jpeg"; 
     *               twi.writeInTemplate(imageFileURL , 0,0, 0, 0, (short)6, 5, (short)8, 8);
     * 
     * @param dx1 :第一个cell开始的X坐标
     * @param dy1 :第一个cell开始的Y坐标
     * @param dx2 :第二个cell开始的X坐标
     * @param dy2 :第二个cell开始的Y坐标
     * @param col1   :图片的左上角放在第几个列cell (the column(o based); of the first cell)
     * @param row1  :图片的左上角放在第几个行cell (the row(o based); of the first cell)
     * @param col2   :图片的右下角放在第几个列cell (the column(o based); of the second cell)
     * @param row2  :图片的右下角放在第几个行cell (the row(o based); of the second cell)
     * 
     * @author Yangcl
     */
    public  void writeInTemplate(String  imageFileURL , int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2  )
    {   
        BufferedImage  bufferImage = null;

        //写入图片格式信息
        try
        {           
            ByteArrayOutputStream     byteArrayOutputStream = new ByteArrayOutputStream();

            //先把读入的图片放到第一个 ByteArrayOutputStream 中,用于产生ByteArray   
            File fileImage = new File(imageFileURL);    

            bufferImage = ImageIO.read(fileImage);      
            ImageIO.write(bufferImage, "JPG", byteArrayOutputStream);
            System.out.println("ImageIO 写入完成");

            //准备插入图片
            HSSFPatriarch  patriarch  =  sheet.createDrawingPatriarch();
            HSSFClientAnchor  anchor  =   new  HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);    

            //插入图片  
            byte[] pictureData = byteArrayOutputStream.toByteArray();
            int pictureFormat   = HSSFWorkbook.PICTURE_TYPE_JPEG;
            int pictureIndex = workbook.addPicture(pictureData, pictureFormat);
            patriarch.createPicture(anchor, pictureIndex);

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("IO Erro:"   e.getMessage());
        }finally
        {
            if(fileOutputStream != null)
            {
                try{
                    fileOutputStream.close();
                }catch(IOException io){
                    io.printStackTrace();
                }
            }
        }
    }

    /**
     * 写入图片格式信息
     * @描述:这是一个实体类,提供了相应的接口,用于操作Excel,在任意坐标处写入数据。
     * @参数:
     *               ImageInputStream imageInputStream:他接受一个外界传入的图片流,图片以流形式存在。
     * 
     * @用法:
     * 
     * 
     * 
     * 
     * @param dx1 :第一个cell开始的X坐标
     * @param dy1 :第一个cell开始的Y坐标
     * @param dx2 :第二个cell开始的X坐标
     * @param dy2 :第二个cell开始的Y坐标
     * @param col1   :图片的左上角放在第几个列cell (the column(o based); of the first cell)
     * @param row1  :图片的左上角放在第几个行cell (the row(o based); of the first cell)
     * @param col2   :图片的右下角放在第几个列cell (the column(o based); of the second cell)
     * @param row2  :图片的右下角放在第几个行cell (the row(o based); of the second cell)
     * 
     * @author Yangcl
     */
    public  void writeInTemplate(ImageInputStream imageInputStream , int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2  )
    {
        BufferedImage  bufferImage = null;      
        //写入图片格式信息
        try
        {           
            ByteArrayOutputStream     byteArrayOutputStream = new ByteArrayOutputStream();      
            //先把读入的图片放到一个 ByteArrayOutputStream 中,用于产生ByteArray          
            bufferImage = ImageIO.read(imageInputStream);       
            ImageIO.write(bufferImage, "JPG", byteArrayOutputStream);
            System.out.println("ImageIO 写入完成");

            //准备插入图片
            HSSFPatriarch  patriarch  =  sheet.createDrawingPatriarch();
            HSSFClientAnchor  anchor  =   new  HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);    

            //插入图片  
            byte[] pictureData = byteArrayOutputStream.toByteArray();
            int pictureFormat   = HSSFWorkbook.PICTURE_TYPE_JPEG;
            int pictureIndex = workbook.addPicture(pictureData, pictureFormat);
            patriarch.createPicture(anchor, pictureIndex);
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("IO Erro:"   e.getMessage());
        }finally
        {
            if(fileOutputStream != null)
            {
                try{
                    fileOutputStream.close();
                }catch(IOException io){
                    io.printStackTrace();
                }
            }
        }
    }
    /**
     * 保存模板
     * @描述:这个方法用于保存workbook(工作薄)中的内容,并写入到一个Excel文件中
     * @参数:String templateFile:取得已经保存的类模板 路径名称
     * @用法:templateFile:String  templateFile_Name1 = "C:/Tamplates/texting-1.xls"
     *                TemplateAdapter ta  = new TemplateAdapter();
     *                ta.SaveTemplate(templateFile_Name1);
     * @param templateFile
     */
    public void SaveTemplate(String templateFile)
    {
        try{

            //建立输出流
            fileOutputStream = new FileOutputStream(templateFile);
            workbook.write(fileOutputStream);

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("IO Erro"   e.getMessage());
        }finally
        {
            if(fileOutputStream != null)
            {
                try{
                    fileOutputStream.close();
                }catch(IOException io){
                    io.printStackTrace();
                }
            }
        }
    }


}

            

                    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                
                   // pict.Resize();那句话分明毫无,那是用图片原始大小来显示
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        这段代码是欧洲经济共同体的。笔者开始找了相当久关于怎么样行使POI在Excel文书档案狂妄单元格写入数据的篇章,进而达成一个以面向服务为对象的表格系统,但很缺憾,在境内的网址上未曾找到。何况介绍的比较基本功,对于必要使用POI,但又从申时间精研的人的话,痛苦急了。上边的代码有相比较详细的批注,相信大家看的懂。假设你要转发那篇小说,请注明出处。开源万岁~祝你成功。文书档案重视的三个jar包:commons-codec-1.5.jar和poi-3.8-20110326.jar见附属类小零件。那字体真令人蛋疼菊紧!

//插入图片
byte[] bytes = System.IO.File.ReadAllBytes(FileName);
if (!string.IsNullOrEmpty(FileName))
{
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 100, 50, col, row, col 1, row 1);
//##管理照片地点,【图片左上角为(col, row卡塔 尔(阿拉伯语:قطر‎第row 1行col 1列,右下角为( col 1, row 1卡塔 尔(阿拉伯语:قطر‎第 col 1 1行row 1 1列,宽为100,高为50

HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

// pict.Resize();那句话断定不要,那是用图片原始大小来展现
}

//此处代码是将 xls文件发到页面通过浏览器直接下载到本地  可以放到 界面调用的地方
                //System.IO.MemoryStream ms = new System.IO.MemoryStream();
                //book.Write(ms);
                //Response.AddHeader("Content-Disposition", string.Format("attachment; filename=绩效统计.xls"));
                //Response.BinaryWrite(ms.ToArray());
                //book = null;
                //ms.Close();
                //ms.Dispose();  


                return book;
            }
            catch { 
            throw new Exception();
            }finally{
                book=null;
            }


        }







    }
}

  /// <summary>
        ///导出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnExport_Click(object sender, EventArgs e)
      {



            DataTable dt = cmbll.getdt();



            NPOI.HSSF.UserModel.HSSFWorkbook book = NPOIExport.DoExport(dt, "xxx报表");
            //写入客户端
            try
            {
                WriteClient(book);
            }
            catch
            {

            }
            finally
            {

                book = null;
            }

        }

        public void WriteClient(NPOI.HSSF.UserModel.HSSFWorkbook book)
        {
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            Response.AddHeader("Content-Disposition", string.Format("attachment; filename=客户资料" DateTime.Now.ToString("yyyyMMddHHmmss") ".xls"));
            Response.BinaryWrite(ms.ToArray());
            book = null;
            ms.Close();
            ms.Dispose();
        }

 

本文由星彩网app下载发布于计算机编程,转载请注明出处:选取POI在Excel文书档案大肆单元格写入数据,可控

TAG标签: 星彩网app下载
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。