作者：乔山办公网日期：
返回目录：excel表格制作
导出时自由选择路径的代码如下：
1、后台输出Excel文件代码：
OutputStream output = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=" + path);
response.setContentType("Content-Type:application/vnd.ms-excel ");
wb.write(output);
output.close();
2、前端代码：
window.open("getExcelList","_blank");
package com.aerolink.aocs.util.fileUtil;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
//import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcelNew {
/**
* Excel文件
*/
private XSSFWorkbook wb = null;
/**
* 输出Excel文件中的表对象
*/
private XSSFSheet sheet = null;
/**
* 输出文件流
*/
private FileOutputStream fileOut = null;
/**
* 输出文件名用户自定义
*/
private String outputFilename = null;
/**
* 单元格样式
*/
private XSSFCellStyle cellStyle = null;
// private String newsheet = null; //输出Excel文件中的表名用户自定义
/**
* 行
*/
private XSSFRow row=null;
/**
*
*/
private int rowNumber=-1;
/**
* @param outputFilename
* @param newsheet
*/
public WriteExcelNew(String outputFilename, String newsheet) {
wb = new XSSFWorkbook();
//wb.setSheetName(1, "qwe");//设置第一张表的名称
sheet = wb.createSheet(newsheet);
//sheet.setColumnWidth(1, 40);//第一行 列宽
this.outputFilename = outputFilename;
// this.newsheet = newsheet;
}
/**
* <p>
* Description:exportToExcelFile(short rownum,short cellnum,int value)方法:
* </p>
* <p>
* 将int数据写入Execl文件的表中
* </p>
*
* @param rownum
* @param cellnum
* @param value
*/
public void exportToExcelFile(int rownum, int cellnum, int value) {
if(rowNumber==-1){ //在poi3.8版本7a64e59b9ee7ad94338中，不这样做，只能写入最后一个单元格
rowNumber=rownum;
row = sheet.createRow(rownum);
}else if(rowNumber!=rownum){
rowNumber=rownum;
row = sheet.createRow(rownum);
}
//row.setHeight((short)50);//行高
XSSFCell cell = row.createCell(cellnum);
cell.setCellValue(value);
if(cellStyle==null){
setCellStyle("center","center","",false);
}
cell.setCellStyle(cellStyle);
}
/**
* <p>
* Description:exportToExcelFile(short rownum,short cellnum,String value)方法:
* </p>
* <p>
* 将String数据写入Execl文件的表中
* </p>
*
* @param rownum
* @param cellnum
* @param value
*/
public void exportToExcelFile(int rownum, int cellnum, String value) {
if(rowNumber==-1){ //在poi3.8版本中，不这样做，只能写入最后一个单元格
rowNumber=rownum;
row = sheet.createRow(rownum);
}else if(rowNumber!=rownum){
rowNumber=rownum;
row = sheet.createRow(rownum);
}
XSSFCell cell = row.createCell(cellnum);
cell.setCellValue(value);
if(cellStyle==null){
setCellStyle("center","center","",false);
}
cell.setCellStyle(cellStyle);
}
/**
* <p>
* Description:exportToExcelFile(short rownum,short cellnum,double value)方法:
* </p>
* <p>
* 将double数据写入Execl文件的表中
* </p>
*
* @param rownum
* @param cellnum
* @param value
*/
public void exportToExcelFile(int rownum, int cellnum, double value) {
if(rowNumber==-1){ //在poi3.8版本中，不这样做，只能写入最后一个单元格
rowNumber=rownum;
row = sheet.createRow(rownum);
}else if(rowNumber!=rownum){
rowNumber=rownum;
row = sheet.createRow(rownum);
}
XSSFCell cell = row.createCell(cellnum);
cell.setCellValue(value);
if(cellStyle==null){
setCellStyle("center","center","",false);
}
cell.setCellStyle(cellStyle);
}
/**
* <p>
* Description:exportToExcelFile(short rownum,short cellnum,boolean
* value)方法:
* </p>
* <p>
* 将boolean数据写入Execl文件的表中
* </p>
*
* @param rownum
* @param cellnum
* @param value
*/
public void exportToExcelFile(int rownum, int cellnum, boolean value) {
if(rowNumber==-1){ //在poi3.8版本中，不这样做，只能写入最后一个单元格
rowNumber=rownum;
row = sheet.createRow(rownum);
}else if(rowNumber!=rownum){
rowNumber=rownum;
row = sheet.createRow(rownum);
}
XSSFCell cell = row.createCell(cellnum);
cell.setCellValue(value);
if(cellStyle==null){
setCellStyle("center","center","",false);
}
cell.setCellStyle(cellStyle);
}
/**
* <p>
* Description:exportToExcelFile(short rownum,short cellnum,Date value)方法:
* </p>
* <p>
* 将Date数据写入Execl文件的表中
* </p>
*
* @param rownum
* @param cellnum
* @param value
*/
public void exportToExcelFile(int rownum, int cellnum, Date value) {
if(rowNumber==-1){ //在poi3.8版本中，不这样做，只能写入最后一个单元格
rowNumber=rownum;
row = sheet.createRow(rownum);
}else if(rowNumber!=rownum){
rowNumber=rownum;
row = sheet.createRow(rownum);
}
XSSFCell cell = row.createCell(cellnum);
cell.setCellValue(value);
if(cellStyle==null){
setCellStyle("center","center","",false);
}
cell.setCellStyle(cellStyle);
}
/**
* <p>
* Description:exportToExcelFile(short rownum,short cellnum,Calendar
* value)方法:
* </p>
* <p>
* 将Calendar数据写入Execl文件的表中
* </p>
*
* @param rownum
* @param cellnum
* @param value
*/
public void exportToExcelFile(int rownum, int cellnum, Calendar value) {
if(rowNumber==-1){ //在poi3.8版本中，不这样做，只能写入最后一个单元格
rowNumber=rownum;
row = sheet.createRow(rownum);
}else if(rowNumber!=rownum){
rowNumber=rownum;
row = sheet.createRow(rownum);
}
XSSFCell cell = row.createCell(cellnum);
cell.setCellValue(value);
if(cellStyle==null){
setCellStyle("center","center","",false);
}
cell.setCellStyle(cellStyle);
}
/**
* 合并单元格 2012－3－30 孙贵春 add
* @param startRow
* @param startCol
* @param endRow
* @param endCol
*/
public void setMergedRegion(int startRow,int startCol,int endRow,int endCol){
sheet.addMergedRegion(new CellRangeAddress(startRow,endRow,startCol,endCol));//这与1.5版本明显不同
}
/**
* 设置单元格样式
* @param a_pos
* @param v_pos
* @param border
* @param wrap
*/
public void setCellStyle(String a_pos,String v_pos,String border,Boolean wrap){
cellStyle=wb.createCellStyle();
//水平位置
if(a_pos.equals("center")){
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
}else if(a_pos.equals("left")){
cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
}else{
cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
}
//垂直位置
if(v_pos.equals("center")){
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}else if(v_pos.equals("top")){
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
}else{
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
}
//边框
if(border.equals("thin")){
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
}else if(border.equals("double")){
cellStyle.setBorderBottom(CellStyle.BORDER_DOUBLE);
cellStyle.setBorderLeft(CellStyle.BORDER_DOUBLE);
cellStyle.setBorderRight(CellStyle.BORDER_DOUBLE);
cellStyle.setBorderTop(CellStyle.BORDER_DOUBLE);
}else if(border.equals("thick")){
cellStyle.setBorderBottom(CellStyle.BORDER_THICK);
cellStyle.setBorderLeft(CellStyle.BORDER_THICK);
cellStyle.setBorderRight(CellStyle.BORDER_THICK);
cellStyle.setBorderTop(CellStyle.BORDER_THICK);
}else if(border.equals("medium")){
cellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM);
cellStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);
cellStyle.setBorderRight(CellStyle.BORDER_MEDIUM);
cellStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
}else if(border.equals("none")){
cellStyle.setBorderBottom(CellStyle.BORDER_NONE);
cellStyle.setBorderLeft(CellStyle.BORDER_NONE);
cellStyle.setBorderRight(CellStyle.BORDER_NONE);
cellStyle.setBorderTop(CellStyle.BORDER_NONE);
}else if(border.equals("dotted")){
cellStyle.setBorderBottom(CellStyle.BORDER_DOTTED);
cellStyle.setBorderLeft(CellStyle.BORDER_DOTTED);
cellStyle.setBorderRight(CellStyle.BORDER_DOTTED);
cellStyle.setBorderTop(CellStyle.BORDER_DOTTED);
}
//设置自动换行
cellStyle.setWrapText(wrap);
/*
cellStyle.setRotation((short)90);//设置单元格内文字旋转角度
XSSFFont font=wb.createFont();
font.setFamily(1);
font.setBold(true);
cellStyle.setFont(font);
*/
}
/**
* <p>
* Description: closeFileOut()方法:关闭文件输出流
* </p>
*
* @throws IOException
*/
public void closeFileOut() throws IOException {
fileOut = new FileOutputStream(outputFilename);
wb.write(fileOut);
fileOut.close();
}
/**
* <p>
* 测试方法
* </p>
*
* @param arg
*/
public static void main(String arg[]) {
try {
WriteExcelNew writeExcel = new WriteExcelNew("bak\\new.xls", "newsheet");
writeExcel.exportToExcelFile((short) 0, (short) 0, 99.99);
writeExcel.closeFileOut();
} catch (Exception e) {
System.out.println("Something Happen");
e.printStackTrace();
}
}
}
Java 中，调用 Apache 的 POI 操作 Excel，往 Excel 中插入一张图片e69da5e887aae799bee5baa6e997aee7ad94365。
举例如下：
//create a new workbook
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
//add picture data to this workbook.
// 打开图片
InputStream is = new FileInputStream("image1.jpeg");
byte[] bytes = IOUtils.toByteArray(is);
// 增加图片到 Workbook
int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
is.close();
CreationHelper helper = wb.getCreationHelper();
//create sheet
Sheet sheet = wb.createSheet();
// Create the drawing patriarch. This is the top level container for all shapes.
Drawing drawing = sheet.createDrawingPatriarch();
//add a picture shape
ClientAnchor anchor = helper.createClientAnchor();
//set top-left corner of the picture,
//subsequent call of Picture#resize() will operate relative to it
// 设置图片位置
anchor.setCol1(3);
anchor.setRow1(2);
Picture pict = drawing.createPicture(anchor, pictureIdx);
//auto-size picture relative to its top-left corner
pict.resize();
//save workbook
String file = "picture.xls";
if(wb instanceof XSSFWorkbook) file += "x";
// 输出文件
FileOutputStream fileOut = new FileOutputStream(file);
wb.write(fileOut);
fileOut.close();
/**
* 生成excel表格
* @param dataList 数据集合
* @param col 有多少列
* @param size 有几个对象
* @param col_name 每列的名称
* @return 返回excel表
* @throws Exception
*/
public HSSFWorkbook exportexcel(List dataList,int col,int size,String[] col_name) {
HSSFWorkbook workbook = null;
try {
// 这里的数据即是你要从后台取得的数据
// 创建工作簿实例
workbook = new HSSFWorkbook();
// 创建工作表实例
HSSFSheet sheet = workbook.createSheet("TscExcel");
// 设置列宽
this.setSheetColumnWidth(sheet,col);
// 获取样式
HSSFCellStyle style = this.createTitleStyle(workbook);
if (dataList != null && dataList.size() > 0) {
// 创建第一行标题,标题名字的本地信息通过resources从资源文件中获取
HSSFRow row = sheet.createRow((short) 0);// 建立新行e799bee5baa6e58685e5aeb9332
for(int c=0;c<col;c++){
this.createCell(row, c, style, HSSFCell.CELL_TYPE_STRING, col_name[c]);//填充每列的对应名称
}
int j=0;
// 给excel填充数据
for (int i = 0; i < size; i++) {
HSSFRow row1 = sheet.createRow((short) (i + 1));// 建立新行
this.createCell(row1, 0, style, HSSFCell.CELL_TYPE_STRING, i+1);
for(int t=1;t<col;t++){
this.createCell(row1, t, style, HSSFCell.CELL_TYPE_STRING, dataList.get(j++));
}
}
}else{
this.createCell(sheet.createRow(0), 0, style, HSSFCell.CELL_TYPE_STRING, "查无资料");
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
private void setSheetColumnWidth(HSSFSheet sheet , int t) {
// 根据你数据里面的记录有多少列，就设置多少列
for(int i=0;i<t;i++){
if(i==0){
sheet.setColumnWidth((short) i, (short) 2000);
}else{
sheet.setColumnWidth((short) i, (short) 5000);
}
}
}