亟需引进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);
}
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文书档案大肆单元格写入数据,可控