java导出表格vsd_java导出Excel表格

最新推荐文章于 2022-05-08 18:52:13 发布

大一一新生

最新推荐文章于 2022-05-08 18:52:13 发布

阅读量132

点赞数

文章标签： java导出表格vsd

本文链接：https://blog.csdn.net/weixin_32099703/article/details/114572265

版权

去百度下载一个poi包，在Myeclipse中右键点击项目名称找到Build path 添加包

package cn.com.yitong.app.dbank.vo;

public class LogInfoOut {

private int id;

private String tranDate;

private String crDrMaintInd;

private String tranAmt;

private String actualBalAmt;

private String tfrAcctNo;

private String tfrAcctName;

private String tranDesc;

public LogInfoOut(){}

public LogInfoOut(int id, String tranDate, String crDrMaintInd,String tranAmt, String actualBalAmt, String tfrAcctNo,String tfrAcctName, String tranDesc) {

this.id = id;

this.tranDate = tranDate;

this.crDrMaintInd = crDrMaintInd;

this.tranAmt = tranAmt;

this.actualBalAmt = actualBalAmt;

this.tfrAcctNo = tfrAcctNo;

this.tfrAcctName = tfrAcctName;

this.tranDesc = tranDesc;

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getTranDate() {

return tranDate;

}

public void setTranDate(String tranDate) {

this.tranDate = tranDate;

}

public String getCrDrMaintInd() {

return crDrMaintInd;

}

public void setCrDrMaintInd(String crDrMaintInd) {

this.crDrMaintInd = crDrMaintInd;

}

public String getTranAmt() {

return tranAmt;

}

public void setTranAmt(String tranAmt) {

this.tranAmt = tranAmt;

}

public String getActualBalAmt() {

return actualBalAmt;

}

public void setActualBalAmt(String actualBalAmt) {

this.actualBalAmt = actualBalAmt;

}

public String getTfrAcctNo() {

return tfrAcctNo;

}

public void setTfrAcctNo(String tfrAcctNo) {

this.tfrAcctNo = tfrAcctNo;

}

public String getTfrAcctName() {

return tfrAcctName;

}

public void setTfrAcctName(String tfrAcctName) {

this.tfrAcctName = tfrAcctName;

}

public String getTranDesc() {

return tranDesc;

}

public void setTranDesc(String tranDesc) {

this.tranDesc = tranDesc;

}

/**

* EXCEL报表工具类.

* @author Jeelon

public class ExportExcel {

private HSSFWorkbook wb = null;

private HSSFSheet sheet = null;

/**

*无参构造器

**/

public ExportExcel(){}

/**

* @param wb

* @param sheet

public ExportExcel(HSSFWorkbook wb, HSSFSheet sheet) {

this.wb = wb;

this.sheet = sheet;

}

/**

* 创建通用EXCEL头部

* @param headString

* 头部显示的字符

* @param colSum

* 该报表的列数

@SuppressWarnings({ "deprecation", "unused" })

public void createNormalHead(String headString, int colSum) {

// 设置第一行、第一列

HSSFRow row = sheet.createRow(0);

HSSFCell cell = row.createCell(0);

// 定义单元格为字符串类型

cell.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理

cell.setCellValue(new HSSFRichTextString(headString));

// 指定合并区域

/**

* public Region(int rowFrom, short colFrom, int rowTo, short colTo)

sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) colSum));

// 定义单元格格式，添加单元格表样式，并添加到工作簿

HSSFCellStyle cellStyle = wb.createCellStyle();

// 设置单元格水平对齐类型

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐

cellStyle.setWrapText(true);// 指定单元格自动换行

// 设置单元格字体

HSSFFont font = wb.createFont();

// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// font.setFontName("宋体");

// font.setFontHeight((short) 600);

// cellStyle.setFont(font);

cell.setCellStyle(cellStyle);

}

/**

* 创建通用报表第二行

* @param params

* 统计条件数组

* @param colSum

* 需要合并到的列索引

@SuppressWarnings("deprecation")

public void createNormalTwoRow(String[] params, int colSum) {

// 创建第二行、第二列

HSSFRow row1 = sheet.createRow(1);

row1.setHeight((short) 400);

HSSFCell cell2 = row1.createCell(0);

cell2.setCellType(HSSFCell.ENCODING_UTF_16);

cell2.setCellValue(new HSSFRichTextString("时间：" + params[0] + "至" + params[1]));

// 指定合并区域

/**

* public Region(int rowFrom, short colFrom, int rowTo, short colTo)

sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) colSum));

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐

cellStyle.setWrapText(true);// 指定单元格自动换行

// 设置单元格字体

HSSFFont font = wb.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

font.setFontName("宋体");

font.setFontHeight((short) 250);

ellStyle.setFont(font);

cell2.setCellStyle(cellStyle);

}

/**

* 设置报表标题

* @param columHeader

* 标题字符串数组

public void createColumHeader(String[] columHeader) {

// 设置列头在第三行

HSSFRow row2 = sheet.createRow(2);

// 指定行高

row2.setHeight((short) 600);

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐

cellStyle.setWrapText(true);// 指定单元格自动换行

// 单元格字体

HSSFFont font = wb.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

font.setFontName("宋体");

font.setFontHeight((short) 250);

cellStyle.setFont(font);

// 设置单元格背景色

cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

HSSFCell cell3 = null;

for (int i = 0; i < columHeader.length; i++) {

cell3 = row2.createCell(i);

cell3.setCellType(HSSFCell.ENCODING_UTF_16);

cell3.setCellStyle(cellStyle);

cell3.setCellValue(new HSSFRichTextString(columHeader[i]));

}

/**

* 创建内容单元格

* @param wb

* HSSFWorkbook

* @param row

* HSSFRow

* @param col

* short型的列索引

* @param align

* 对齐方式

* @param val

* 列值

public void cteateCell(HSSFWorkbook wb, HSSFRow row, int col, short align, String val) {

HSSFCell cell = row.createCell(col);

cell.setCellType(HSSFCell.ENCODING_UTF_16);

cell.setCellValue(new HSSFRichTextString(val));

HSSFCellStyle cellstyle = wb.createCellStyle();

cellstyle.setAlignment(align);

cell.setCellStyle(cellstyle);

}

/**

* 创建合计行

* @param colSum

* 需要合并到的列索引

* @param cellValue

@SuppressWarnings("deprecation")

public void createLastSumRow(int colSum, String[] cellValue) {

HSSFCellStyle cellStyle = wb.createCellStyle();

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐

cellStyle.setWrapText(true);// 指定单元格自动换行

// 单元格字体

HSSFFont font = wb.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

font.setFontName("宋体");

font.setFontHeight((short) 250);

cellStyle.setFont(font);

// 获取工作表最后一行

HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));

HSSFCell sumCell = lastRow.createCell(0);

sumCell.setCellValue(new HSSFRichTextString("合计"));

sumCell.setCellStyle(cellStyle);

// 合并最后一行的第零列-最后一行的第一列

sheet.addMergedRegion(new Region(sheet.getLastRowNum(), (short) 0,

sheet.getLastRowNum(), (short) colSum));// 指定合并区域

for (int i = 2; i < (cellValue.length + 2); i++) {

// 定义最后一行的第三列

sumCell = lastRow.createCell(i);

sumCell.setCellStyle(cellStyle);

// 定义数组从0开始。

sumCell.setCellValue(new HSSFRichTextString(cellValue[i - 2]));

}

/**

* 输入EXCEL文件

* @param fileName

* 文件名

public void outputExcel(String fileName) {

FileOutputStream fos = null;

try {

fos = new FileOutputStream(new File(fileName));

wb.write(fos);

fos.close();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

/**

* @return the sheet

public HSSFSheet getSheet() {

return sheet;

}

/**

* @param sheet

* the sheet to set

public void setSheet(HSSFSheet sheet) {

this.sheet = sheet;

}

/**

* @return the wb

public HSSFWorkbook getWb() {

return wb;

}

/**

* @param wb

* the wb to set

public void setWb(HSSFWorkbook wb) {

this.wb = wb;

}

import java.io.BufferedOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.util.ArrayList;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

@SuppressWarnings("serial")

public class OutputExcel extends HttpServlet {

protected void doGet(HttpServletRequest request,

HttpServletResponse response) throws ServletException, IOException {

this.doPost(request, response);

}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

List list = new ArrayList();

for(int i = 0 ; i <10;

LogInfoOut logInfoOut = new LogInfoOut(i+1;"20150528","转入","100.00","2001.00","62202******5665","张三","转账");

list.add(logInfoOut);

}

String fileName = "导出Excel.xls";

fileName = new String(fileName.getBytes("GBK"), "iso8859-1");

response.reset();

response.setHeader("Content-Disposition", "attachment;filename=" + fileName);// 指定下载的文件名

response.setContentType("application/vnd.ms-excel");

response.setHeader("Pragma", "no-cache");

response.setHeader("Cache-Control", "no-cache");

response.setDateHeader("Expires", 0);

OutputStream output = response.getOutputStream();

BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);

// 定义单元格报头

String worksheetTitle = "Excel导出Student信息";

HSSFWorkbook wb = new HSSFWorkbook();

// 创建单元格样式

HSSFCellStyle cellStyleTitle = wb.createCellStyle();

// 指定单元格居中对齐

cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 指定单元格垂直居中对齐

cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

// 指定当单元格内容显示不下时自动换行

cellStyleTitle.setWrapText(true);

// ------------------------------------------------------------------

HSSFCellStyle cellStyle = wb.createCellStyle();

// 指定单元格居中对齐

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 指定单元格垂直居中对齐

cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

// 指定当单元格内容显示不下时自动换行

cellStyle.setWrapText(true);

// ------------------------------------------------------------------

// 设置单元格字体

HSSFFont font = wb.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

font.setFontName("宋体");

font.setFontHeight((short) 200);

cellStyleTitle.setFont(font);

// 工作表名

String id = "序号";

String tranDate="交易时间";

String crDrMaintInd="交易类型";

String tranAmt = "交易金额";

String actualBalAmt="账户余额";

String tfrAcctNo="对方账号";

String tfrAcctName="对方名称";

String tranDesc="摘要";

HSSFSheet sheet = wb.createSheet();

ExportExcel exportExcel = new ExportExcel(wb, sheet);

// 创建报表头部

exportExcel.createNormalHead(worksheetTitle, 6);

// 定义第一行

HSSFRow row1 = sheet.createRow(1);

HSSFCell cell1 = row1.createCell(0);

/第一行第一列