POI操作EXCEL兑现插入数据完美打印
POI操作EXCEL实现插入数据完美打印
servlet DzdPrintExcel.java
OperateExcel.java 实现插入行操作，下面行自动下移
FontCellStyle.java 控制excel样式类
DzdInfo.java 这个是获得数据的类
servlet DzdPrintExcel.java
public class DzdPrintExcel extends HttpServlet {
public DzdPrintExcel() {
super();
}
public void destroy() {
super.destroy();
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.addHeader("Content-Type", "application/msexcel");
response.setCharacterEncoding("UTF-8");
String fileName=new String(request.getParameter("fileName").getBytes("ISO-8859-1"), "utf-8");
String vfID=request.getParameter("vfID");
String OBJID=request.getParameter("OBJID");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment; filename="+fileName);
System.out.println("vfID:"+vfID);
System.out.println("OBJID:"+OBJID);
System.out.println("fileName:"+fileName);
DzdInfo dzdInfo = new DzdInfo(OBJID);
URI fileServiceUrl = null;
try {
fileServiceUrl = new URI(PlatformServiceManager.getServiceUrl("PI3000.Service.File")).resolve("downloads/");
} catch (URISyntaxException e) {
e.printStackTrace();
}
String reqFileServiceUrl = fileServiceUrl.toString() + fileName + "?id=" + vfID + "&filename=" + fileName;
java.net.URL reqFileService =new java.net.URL(reqFileServiceUrl);
System.out.println("EXCEL路径："+reqFileServiceUrl);
try{
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(new BufferedInputStream(reqFileService.openStream())));
HSSFSheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum(); //读取EXCEL行数
int cellNum = sheet.getRow(0).getLastCellNum(); //读取EXCEL列数
System.out.println("EXCEL行数："+rowNum);
System.out.println("EXCEL列数："+cellNum);
HSSFPrintSetup hps = sheet.getPrintSetup();
hps.setPaperSize(hps.A4_PAPERSIZE);
sheet.getPrintSetup().setLandscape(true);
//增加的第一行
OperateExcel.insertRow(sheet, 0, 2);
HSSFRow rowOne = sheet.getRow(0);
rowOne.setHeightInPoints(35);
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (cellNum-1)));
HSSFCell cellOne=rowOne.getCell(0);
cellOne.setCellStyle(FontCellStyle.getAnyCellStyle(wb,FontCellStyle.getHdrFont(wb),HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, (short)-1, true));
sheet.setColumnWidth((short)1,800);
for(int i=1;i<cellNum;i++){
HSSFCell cell=rowOne.getCell(i);
System.out.println("i=:"+i);
try{
cell.setCellStyle(FontCellStyle.getBorderNun(wb));
}
catch(Exception e){
}
if(i!=0)
sheet.setColumnWidth((short)i,(short)(33000/(cellNum-1)));
}
cellOne.setCellValue(dzdInfo.getRowOneInfo());
//增加的第二行
HSSFRow rowTwo = sheet.getRow(1);
sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) (cellNum-1)));
HSSFCell cellTwo=rowTwo.getCell(0);
cellTwo.setCellStyle(FontCellStyle.getAnyCellStyle(wb,FontCellStyle.getFtrFont(wb),HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, (short)-1, true));
for(int j=1;j<cellNum;j++){
HSSFCell cell=rowTwo.getCell(j);
try{
cell.setCellStyle(FontCellStyle.getBorderNun(wb));
}
catch(Exception e){
}
}
cellTwo.setCellValue(dzdInfo.getRowTwoInfo());
//增加的第三行
HSSFRow rowThree = sheet.createRow(rowNum+3);
for(int j=0;j<cellNum;j++){
HSSFCell cell=rowThree.createCell(j);
try{
cell.setCellStyle(FontCellStyle.getBorderTrue(wb));
}
catch(Exception e){
}
}
HSSFCell cellThree = rowThree.getCell(0);
cellThree.setCellStyle(FontCellStyle.getBorderTrue(wb));
cellThree.setCellValue(dzdInfo.getRowThreeInfo());
sheet.addMergedRegion(new Region(rowNum+3, (short) 0, rowNum+3, (short) (cellNum-1)));
//增加的第四行
HSSFRow rowFour = sheet.createRow(rowNum+4);
for(int j=0;j<cellNum;j++){
HSSFCell cell=rowFour.createCell(j);
try{
cell.setCellStyle(FontCellStyle.getBorderTrue(wb));
}
catch(Exception e){
}
}
HSSFCell cellFour = rowFour.getCell(0);
cellFour.setCellStyle(FontCellStyle.getBorderTrue(wb));
cellFour.setCellValue(dzdInfo.getRowFourInfo());
sheet.addMergedRegion(new Region(rowNum+4, (short) 0, rowNum+4, (short) (cellNum-1)));
//增加的第五行
HSSFRow rowFive = sheet.createRow(rowNum+5);
for(int j=0;j<cellNum;j++){
HSSFCell cell=rowFive.createCell(j);
try{
cell.setCellStyle(FontCellStyle.getBorderNun(wb));
}
catch(Exception e){
}
}
HSSFCell cellFive = rowFive.getCell(0);
cellFive.setCellStyle(FontCellStyle.getBorderNun(wb));
cellFive.setCellStyle(FontCellStyle.getAnyCellStyle(wb,FontCellStyle.getFtrFont(wb),HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, (short)-1, true));
cellFive.setCellValue(dzdInfo.getRowFiveInfo());
sheet.addMergedRegion(new Region(rowNum+5, (short) 0, rowNum+5, (short) (cellNum-1)));
OutputStream fileOut = response.getOutputStream();
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}
catch (IOException e) {
e.printStackTrace();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
super.doGet(request, response);
}
public void init() throws ServletException {
}
}
OperateExcel.java 实现插入行操作，下面行自动下移
public class OperateExcel {
public static void insertRow(HSSFSheet sheet, int startRow, int rows) {
sheet.shiftRows(startRow, sheet.getLastRowNum(), rows, true, false);
for (int i = 0; i < rows; i++) {
HSSFRow sourceRow = null;//原始位置
HSSFRow targetRow = null;//移动后位置
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
sourceRow = sheet.createRow(startRow);
targetRow = sheet.getRow(startRow + rows);
sourceRow.setHeight(targetRow.getHeight());
for (int m = targetRow.getFirstCellNum(); m < targetRow.getPhysicalNumberOfCells(); m++) {
sourceCell = sourceRow.createCell(m);
targetCell = targetRow.getCell(m);
sourceCell.setCellStyle(targetCell.getCellStyle());
sourceCell.setCellType(targetCell.getCellType());
}
startRow++;
}
}
}
FontCellStyle.java 控制excel样式类
public class FontCellStyle {
private static HSSFFont fontStyle = null;
private static HSSFCellStyle cellStyle = null;
/*设置字体格式*/
public static HSSFFont getHdrFont(HSSFWorkbook wb) {
fontStyle = wb.createFont();
fontStyle.setFontName("黑体");
fontStyle.setFontHeightInPoints((short)20);
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
return fontStyle;
}
public static HSSFFont getFtrFont(HSSFWorkbook wb) {
fontStyle = wb.createFont();
fontStyle.setFontName("宋体");
fontStyle.setFontHeightInPoints((short)10);
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
return fontStyle;
}
public static HSSFFont getContentFont(HSSFWorkbook wb) {
fontStyle = wb.createFont();
fontStyle.setFontName("宋体");
fontStyle.setFontHeightInPoints((short)12);
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
return fontStyle;
}
public static HSSFFont getMergeConflictFont(HSSFWorkbook wb) {
fontStyle = wb.createFont();
fontStyle.setFontName("Arial");
fontStyle.setFontHeightInPoints((short)12);
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
return fontStyle;
}
/*设置Excel单元格格式,引用到字体格式*/
public static HSSFCellStyle getAnyCellStyle(HSSFWorkbook wb,HSSFFont font,short align,short valign,short indent,boolean wrapText) {
cellStyle =wb.createCellStyle();
if(font != null) cellStyle.setFont(font);
if(align > 0) cellStyle.setAlignment(align);
if(valign > 0) cellStyle.setVerticalAlignment(valign);
if(indent > 0) cellStyle.setIndention(indent);
cellStyle.setWrapText(wrapText);
return cellStyle;
}
public static HSSFCellStyle getBorderNun(HSSFWorkbook wb){
cellStyle =wb.createCellStyle();
cellStyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_NONE);
return cellStyle;
}
public static HSSFCellStyle getBorderTrue(HSSFWorkbook wb){
cellStyle =wb.createCellStyle();
cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
fontStyle = wb.createFont();
fontStyle.setFontName("Arial");
fontStyle.setFontHeightInPoints((short)10);
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(fontStyle);
return cellStyle;
}
/*设置Excel单元格行高、列宽*/
public static void setDefaultHighWidth(HSSFSheet sheet) {
sheet.setDefaultRowHeightInPoints(10);
sheet.setDefaultColumnWidth((short) 20);
}
public static void setDefaultCellHighWidthInRange(HSSFSheet sheet,short[] eachCellWidth,int high) {
//假定第一行和第一行所需的单元个已经建立好了，也就是说，在这之前已经调用了DesignXlsHeaderFooter.setXlsHeader
sheet.setDefaultRowHeightInPoints(high);//设置默认高
/*设置各列单元格宽度*/
for(int i = 0;i < eachCellWidth.length;i++) {
//System.out.print(""+i+"\t");
sheet.setColumnWidth((short) i,(short) ((eachCellWidth[i])*256));
}
}
}
DzdInfo.java 这个是获得数据的类