选取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等等。

 

          //设置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 代码

 

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

253. } 

     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文书档案大肆单元格写入数据，可控