excel表格导出为图片_Java读写与导出Excel表格实例代码详解

更新时间:2018-09-13    来源:excel    手机版     字体:

【www.bbyears.com--excel】

正常导出报表的场景就是从数据库读取数据,然后按照指定的格式生成报表。其中可能涉及到的就是插入/复制行,单元格设置公式计算这些典型应用。下面就按这个需求给出解决方案。思路就是,首先制定一个Excel模板文档,暂命名为TEMPLATE.xls;然后读取该文档,插入/复制一些数据进去,另存为新的报表文件。这样就不需要在写入数据的时候考虑繁琐的表格样式问题了。

读取模板文件

 代码如下 复制代码

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(TEMPLATE_PATH));
HSSFWorkbook wb = new HSSFWorkbook(fs, true);
HSSFSheet sheet = wb.getSheet("Sheet1");

复制行

 代码如下 复制代码

public void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) {
 // Get the source / new row
 HSSFRow newRow = worksheet.getRow(destinationRowNum);
 HSSFRow sourceRow = worksheet.getRow(sourceRowNum);
 
 // If the row exist in destination, push down all rows by 1 else create
 // a new row
 if (newRow != null) {
  worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
 } else {
  newRow = worksheet.createRow(destinationRowNum);
 }
 
 // Loop through source columns to add to new row
 for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
  // Grab a copy of the old/new cell
  HSSFCell oldCell = sourceRow.getCell(i);
  HSSFCell newCell = newRow.createCell(i);
 
  // If the old cell is null jump to next cell
  if (oldCell == null) {
   newCell = null;
   continue;
  }
 
  // Copy style from old cell and apply to new cell
  HSSFCellStyle newCellStyle = workbook.createCellStyle();
  newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
 
  newCell.setCellStyle(newCellStyle);
 
  // If there is a cell comment, copy
  if (newCell.getCellComment() != null) {
   newCell.setCellComment(oldCell.getCellComment());
  }
 
  // If there is a cell hyperlink, copy
  if (oldCell.getHyperlink() != null) {
   newCell.setHyperlink(oldCell.getHyperlink());
  }
 
  // Set the cell data type
  newCell.setCellType(oldCell.getCellType());
 
  // Set the cell data value
  switch (oldCell.getCellType()) {
  case Cell.CELL_TYPE_BLANK:
   newCell.setCellValue(oldCell.getStringCellValue());
   break;
  case Cell.CELL_TYPE_BOOLEAN:
   newCell.setCellValue(oldCell.getBooleanCellValue());
   break;
  case Cell.CELL_TYPE_ERROR:
   newCell.setCellErrorValue(oldCell.getErrorCellValue());
   break;
  case Cell.CELL_TYPE_FORMULA:
   newCell.setCellFormula(oldCell.getCellFormula());
   break;
  case Cell.CELL_TYPE_NUMERIC:
   newCell.setCellValue(oldCell.getNumericCellValue());
   break;
  case Cell.CELL_TYPE_STRING:
   newCell.setCellValue(oldCell.getRichStringCellValue());
   break;
  }
 }
 
 // If there are are any merged regions in the source row, copy to new row
 for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
  CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
  if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
   CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getFirstRow() - cellRangeAddress.getLastRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
   worksheet.addMergedRegion(newCellRangeAddress);
  }
 }
}

插入行,并设置相同的样式

 代码如下 复制代码

// Insert one row
sheet.shiftRows(rowNumber, sheet.getLastRowNum(), 1);
HSSFRow source = sheet.getRow(rowNumber - 1);
HSSFRow newRow = sheet.createRow(rowNumber);
 
for (int i = source.getFirstCellNum(), columnNo = source.getLastCellNum(); i < columnNo; i++) {
 HSSFCell sourceCell = source.getCell(i);
 HSSFCell targetCell = newRow.createCell(i);
 targetCell.setCellStyle(sourceCell.getCellStyle());
 
 //Get data from DB and set cell value
 targetCell.setCellValue("Just for test " + i);
}

设置单元格公式的时候,注意不要加=,直接设置公式就可以。

 代码如下 复制代码
 //设置单元格公式
sheet.getRow(total).getCell(10).setCellFormula(String.format("SUM(K%s:K%s)", aIndex + 1, total));
sheet.getRow(total).getCell(11).setCellFormula(String.format("100*J%s/K%s", total + 1, total + 1));
//列宽度自适应
sheet.autoSizeColumn(1);

保存

 代码如下 复制代码

FileOutputStream fileOut = new FileOutputStream(SAVE_PATH);
wb.write(fileOut);
fileOut.close();
update @ 2011/11/09

删除行,行号0-based

 代码如下 复制代码 public void removeRow(HSSFSheet sheet, int rowIndex) {
 int lastRowNum = sheet.getLastRowNum();
 if (rowIndex >= 0 && rowIndex < lastRowNum) {
  sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
 }
 if (rowIndex == lastRowNum) {
  HSSFRow removingRow = sheet.getRow(rowIndex);
  if (removingRow != null) {
   sheet.removeRow(removingRow);
  }
 }
}

这里只是列出了一些简单操作
公司用的一个导出Excel表格的类,贴出来和大家共享下

 代码如下 复制代码

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

import com.shxy.exception.FileOperationException;
import com.shxy.util.Verify;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
 * 生成excel表格
 * @author
 *
 */
public class ExcelExport {
 /**
  * 构造器
  *
  */
 public ExcelExport() {

 }
 /**
  * 生成具有一定格式excel
  * @param sheetName sheet名称,默认为sheet1
  * @param nf 数字类型的格式 如:jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");默认无格式
  * @param content 二维数组,要生成excel的数据来源
  * @param 合并项 每一项的数据格式为0,1,0,2 即:把(0,1)和(0,2)合并--->第1列的第一、二个元素合并
  * @param os excel输出流
  * @param row 需要水平居中的行,默认居左。以逗号分隔的字符串
  * @param col 需要水平居中的列,默认居左。以逗号分隔的字符串
  * @throws FileOperationException
  */
 public void export(String sheetName, NumberFormat nf,  String[][] content, String[] mergeInfo, OutputStream os, String row, String col)
   throws FileOperationException {
  if (Verify.isNullObject(content, os)
    || Verify.isNull2DArray(content)) {
   return;
  }
  //默认名称
  if(Verify.isNullObject(sheetName)){
   sheetName="sheet1";
  }
  Setrows=this.getInfo(row);
  Setcols=this.getInfo(col);
  WritableWorkbook workbook = null;
  try {
   workbook = Workbook.createWorkbook(os);
   WritableSheet sheet = workbook.createSheet(sheetName, 0);
   for (int i = 0; i < content.length; i++) {
    for (int j = 0; j < content[i].length; j++) {
     if(content[i][j]==null){
      content[i][j]="";
     }
     if (isNumber(content[i][j])&&!rows.contains(i)&&!cols.contains(j)) {//处理数字
      Number number=null;
      if(Verify.isNullObject(nf)){//数字无格式
       number = new Number(j, i, Double
         .valueOf(content[i][j]));
      }else{//如果有格式,按格式生成
       jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat(nf);
       number = new Number(j, i, Double
         .valueOf(content[i][j]),wcfn);
      }
      sheet.addCell(number);
     } else {//处理非数字
      WritableCellFormat format=new WritableCellFormat();
      if(rows.contains(i)||cols.contains(j)){
       format.setAlignment(jxl.format.Alignment.CENTRE);
      }else{
       format.setAlignment(jxl.format.Alignment.LEFT);
      }
      format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
      Label label = new Label(j, i, content[i][j],format);
      sheet.addCell(label);
     }
    }
   }
   this.merge(sheet, mergeInfo);
   workbook.write();
  } catch (Exception e) {
   e.printStackTrace();
   throw new FileOperationException(e);
  }finally {
   try {
    workbook.close();
   } catch (WriteException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }
 
 /**
  * 生成固定格式的excel,表格都为文本,水平居左,垂直居中
  * @param sheetName sheet名称,默认为sheet1
  * @param content 二维数组,要生成excel的数据来源
  * @param os excel输出流
  * @throws FileOperationException
  */
 public void exportFormatExcel(String[][] content, String sheetName, OutputStream os)
 throws FileOperationException {
  if (Verify.isNullObject(content, os)
    || Verify.isNull2DArray(content)) {
   return;
  }
//  默认名称
  if(Verify.isNullObject(sheetName)){
   sheetName="sheet1";
  }
  WritableWorkbook workbook = null;
  try {
   workbook = Workbook.createWorkbook(os);
   WritableSheet sheet = workbook.createSheet(sheetName, 0);
  
   for (int i = 0; i < content.length; i++) {
    for (int j = 0; j < content[i].length; j++) {
     if(content[i][j]==null){
      content[i][j]="";
     }
     WritableCellFormat format=new WritableCellFormat();
     format.setAlignment(jxl.format.Alignment.LEFT);
     format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
     Label label = new Label(j, i, content[i][j],format);
     sheet.addCell(label);
    }
   }
  
  
   workbook.write();
  } catch (Exception e) {
   e.printStackTrace();
   throw new FileOperationException(e);
  }finally {
   try {
    workbook.close();
   } catch (WriteException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }
 
 /**
  * 生成固定格式的excel,表格都为文本,水平居左,垂直居中
  * @param sheetName sheet名称,默认为sheet1
  * @param content Map,要生成excel的数据来源
  * @param os excel输出流
  * @throws FileOperationException
  */
 public void exportFormatExcel(Map content,String[] salary_name_array, String sheetName, OutputStream os)
 throws FileOperationException {
  if (Verify.isNullObject(content, os)
    || content.size()==0) {
   return;
  }
//  默认名称
  if(Verify.isNullObject(sheetName)){
   sheetName="sheet1";
  }
  WritableWorkbook workbook = null;
  try {
   workbook = Workbook.createWorkbook(os);
   WritableSheet sheet = workbook.createSheet(sheetName, 0);
   int index = 0;
   for (int k = 0; k < salary_name_array.length; k++) {
    String[][] value = (String[][])content.get(salary_name_array[k]);
    if(value!=null&&value.length>0){
         if(index!=0){
         index++;
         }
      WritableCellFormat format1=new WritableCellFormat();
      format1.setAlignment(jxl.format.Alignment.LEFT);
      format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
      Label label1 = new Label(0, index, salary_name_array[k] ,format1);
      sheet.addCell(label1);
     for (int i = 0; i < value.length; i++) {
      index ++ ;
      for (int j = 0; j < value[i].length; j++) {
       if(value[i][j]==null){
        value[i][j]="";
       }
       WritableCellFormat format=new WritableCellFormat();
       format.setAlignment(jxl.format.Alignment.LEFT);
       format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
      
       Label label = new Label(j, index, value[i][j],format);
       sheet.addCell(label);
      }
     }
    }
   }
   workbook.write();
  } catch (Exception e) {
   e.printStackTrace();
   throw new FileOperationException(e);
  }finally {
   try {
    workbook.close();
   } catch (WriteException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }
 
 
 /**
  * 合并表格
  * @param sheet 工作表
  * @param mergeInfo 要合并的表格的信息
  * @throws RowsExceededException
  * @throws NumberFormatException
  * @throws WriteException
  */
 private void merge(WritableSheet sheet,String[]mergeInfo) throws RowsExceededException, NumberFormatException, WriteException{
  if(Verify.isNullObject(sheet)||Verify.isNull1DArray(mergeInfo)){
   return;
  }else if(!this.isMergeInfo(mergeInfo)){
   return;
  }else{
   for(String str:mergeInfo){
    String[]temp=str.split(",");
    sheet.mergeCells(Integer.parseInt(temp[1]), Integer.parseInt(temp[0]), Integer.parseInt(temp[3]), Integer.parseInt(temp[2]));
   }
  }
 }
 /**
  * 处理要居中的行或列的数据
  * @param indexes 行标或列标
  * @return 行坐标或列坐标组成的集合
  */
 private Set getInfo(String indexes){
  Setset=new HashSet();
  if(Verify.isNullObject(indexes)){
   return set;
  }
  String[]temp=indexes.split(",",0);
  for(String str:temp){
   if(isNumeric(str)){
    set.add(Integer.parseInt(str));
   }
  }
  return set;
 }
 /**
  * 判断字符串是否由纯数字组成
  * @param str 源字符串
  * @return true是,false否
  */
 private boolean isNumeric(String str) {
  if (Verify.isNullObject(str)){
   return false;
  }
  Pattern pattern = Pattern.compile("[0-9]*");
  return pattern.matcher(str).matches();
 }
 /**
  * 判断字符串是否是数字
  * @param str 源字符串
  * @return true是,false否
  */
 private boolean isNumber(String number) {
  //判断参数
  if (Verify.isNullObject(number)){
   return false;
  }
  //查看是否有小数点
  int index = number.indexOf(".");
  if (index < 0) {
   return isNumeric(number);
  } else {
   //如果有多个".",则不是数字
   if(number.indexOf(".")!=number.lastIndexOf(".")){
    return false;
   }
   String num1 = number.substring(0, index);
   String num2 = number.substring(index + 1);
   return isNumeric(num1) && isNumeric(num2);
  }
 }
 /**
  * 判断合并项内容是否合法
  * @param mergeInfo 合并项 每一项的数据格式为0,1,0,2即把(0,1)和(0,2)合并
  * @return true合法,false非法
  */
 private boolean isMergeInfo(String[]mergeInfo){
  if(Verify.isNull1DArray(mergeInfo)){
   return false;
  }else{
   for(String str:mergeInfo){
    String[]temp=str.split(",");
    if(Verify.isNull1DArray(temp)||temp.length!=4){
     return false;
    }else{
     for(String s:temp){
      if(!isNumeric(s)){
       return false;
      }
     }
    }
   }
  }
  return true;
 }
 public static void main(String[] args) {
  ExcelExport ee=new ExcelExport();
  String[][]content=new String[][]{{"测试","第一列",null,"第三列"},{"第一行","aa","2.00","22"},{"第二行","bb","3.01","3"},{"第三行","cc","4.00","4"}};
  try {
   OutputStream os=new FileOutputStream("D://test//test.xls");
//   ee.export(null,null, content,null, os);
   ee.export(null,null, content,new String[]{"0,1,0,2","1,0,3,0"}, os,"0,1","0");
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
}

本文来源:http://www.bbyears.com/bangongshuma/44318.html

热门标签

更多>>

本类排行