0718成果

利用POI读出Excel中的表格,绘制成图片输出,可兼容跨行和跨列

@ 转载请注明出处

———————

/*

@Author Alphwe Shn

*/

package readxsl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

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

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.awt.*;
import java.awt.image.*;
import javax.imageio.*;
import java.awt.font.*;
import java.awt.geom.*;

public class Xsl2Img {
//MapCell类,用来描述表格信息,包括占用表格的数量和内容
static class MapCell{
private int c,r,t;
private String v;
MapCell(int r, int c, int t, String v){
this.c = c;
this.r = r;
this.t = t;
this.v = v;
}
public void setRegion(int r, int c){
this.c = c;
this.r = r;
}
public void setType(int t){
this.t = t;
}
public void setValue(String v){
this.v = v;
}
public int[] getRegion(){
int[] reg = new int[2];
reg[0] = this.r;
reg[1] = this.c;
return reg;
}
public String getValue(){
return this.v;
}
public int getR(){
return this.r;
}
public int getC(){
return this.c;
}
public int getT(){
return this.t;
}

public String toString(){
return Integer.toString(this.r) + ” ” + Integer.toString(this.c) + ” ” + this.v;
}
}

private String xslFile;
private String imgFile;
private MapCell[][] mapdata;

public Xsl2Img(String xslFilename, String imgFilename){
this.xslFile = xslFilename;
this.imgFile = imgFilename;
}

//getMergedRegion,用来获取指定表格整个表格上所有合并单元格的分布
private static CellRangeAddressList getMergedRegion(HSSFSheet sheet){
CellRangeAddressList mr = new CellRangeAddressList();
int sheetmergerCount = sheet.getNumMergedRegions();
for(int i = 0; i < sheetmergerCount; i++) {
CellRangeAddress ca = (CellRangeAddress) sheet.getMergedRegion(i);
mr.addCellRangeAddress(ca);
}
return mr;
}

//读取指定Excel工作表内容
private static MapCell[][] readXSL(HSSFSheet sheet){
MapCell[][] rslt = new MapCell[sheet.getPhysicalNumberOfRows()][sheet.getRow(0).getPhysicalNumberOfCells()];
String value = “”;
int rownum = sheet.getPhysicalNumberOfRows();// – 1;
for (int i = 0 ; i < rownum; i++){
for (int j = 0; j < sheet.getRow(i).getPhysicalNumberOfCells(); j++){
HSSFCell cell = sheet.getRow(i).getCell(j);

if (cell == null) continue;
switch (cell.getCellType()){
case HSSFCell.CELL_TYPE_NUMERIC:
if (j ==0) {value = “” + (int)cell.getNumericCellValue(); break;}
else {value = “” + cell.getNumericCellValue(); break;}
case HSSFCell.CELL_TYPE_STRING: value = “” + cell.getStringCellValue(); break;
case HSSFCell.CELL_TYPE_BLANK: value = “”; break;
case HSSFCell.CELL_TYPE_FORMULA: value = “” + cell.getNumericCellValue(); break;
default: value = “”;
}
MapCell geo_cell = new MapCell(1, 1, 1, value);
rslt[i][j] = geo_cell;
}
}
return rslt;
}

//生成最后的表格分布数组,数组的位置同单元格的位置一致,数组中的元素内容为(占用行数,占用列数,类型,取值)
private static MapCell[][] mapMergedArea(MapCell[][] map, CellRangeAddressList mr){
CellRangeAddress[] ca = mr.getCellRangeAddresses();
for (int i = 0; i < ca.length; i++){
int firstC = ca[i].getFirstColumn();
int lastC = ca[i].getLastColumn();
int firstR = ca[i].getFirstRow();
int lastR = ca[i].getLastRow();

for (int mR = firstR ; mR <= lastR ; mR++){
for (int mC = firstC ; mC <= lastC; mC++){

map[mR][mC].setType(0);
//map[mR][mC].setValue(“ntpt”);
}
}
map[firstR][firstC].setRegion((lastR – firstR + 1), (lastC – firstC + 1));
map[firstR][firstC].setType(1);
}

return map;
}

//统一调用,传入一个文件名,将Excel映射为一个二维数组
//数组的索引号为单元格在原Xsl文件中的位置
//每个元素的前两个整数值表示该单元格所占的空间
private void genXslMap() throws FileNotFoundException, IOException{
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(this.xslFile));
HSSFSheet sheet = wb.getSheetAt(0);
MapCell[][] map, map_final;
map = readXSL(sheet);
CellRangeAddressList merged_region;
merged_region = getMergedRegion(sheet);
map_final = mapMergedArea(map, merged_region);
// 测试
//for (int i = 0 ; i < map_final.length; i++){for (int j = 0 ; j < map_final[i].length ; j++){System.out.println(Integer.toString(map_final[i][j].getR())+” “+ Integer.toString(map_final[i][j].getC())+ ” ” + map_final[i][j].getValue());}}

this.mapdata = map_final;
}

//根据由Excel生成的数据映射生成图片
public void genImg() throws IOException{
genXslMap();
MapCell[][] map = this.mapdata;
System.out.println(“generatig img from excel data map…”);
int fontsize = 10;
File file = new File (this.imgFile);
Font font = new Font(“SansSerif”, Font.BOLD, fontsize);

int row_num = map.length;
int col_num = map[0].length;

//以下生成表格长宽和单元格的长宽

double geo_height = 240;
double geo_width = 260;
double cell_height = fontsize +5;
double cell_width = fontsize * 6 +5;

//第一种方式,指定表格长宽

// cell_height = (geo_height – 20)/row_num;
// cell_width = (geo_width – 20)/col_num;

//第二种方式,指定单元格长宽
geo_height = cell_height * row_num + 20;
geo_width = cell_width * col_num + 20;

//以上生成表格长宽和单元格长宽

BufferedImage bi = new BufferedImage((int)geo_width, (int)geo_height, BufferedImage.TYPE_INT_RGB);

Graphics2D g = (Graphics2D) bi.getGraphics();
g.setBackground(Color.WHITE);
g.clearRect(0, 0, (int)geo_width, (int)geo_height);
g.setPaint(Color.BLACK);

double x = 10;
double y = 10;

for (int row = 0; row < row_num ; row++){
x = 10;
for (int col = 0; col < col_num; col++ ){

Rectangle2D rect = new Rectangle2D.Double(x, y, cell_width * map[row][col].getC() * map[row][col].getT(), cell_height * map[row][col].getR() * map[row][col].getT());
g.draw(rect);

if ((map[row][col].getValue().length() )> (int)((cell_width * map[row][col].getC() + 5 )/ fontsize)){
//System.out.println(“截行”);
String temp1 = map[row][col].getValue().substring(0, (int)(cell_width / fontsize) -1);
String temp2 = map[row][col].getValue().substring((int)(cell_width / fontsize) – 1,map[row][col].getValue().length());
map[row][col].setValue(temp1);
map[row][col].setRegion(1, map[row][col].getC());
map[row + 1][col].setValue(temp2);
map[row + 1][col].setRegion(map[row][col].getR(), map[row][col].getC());
}

FontRenderContext context = g.getFontRenderContext();
Rectangle2D bound = font.getStringBounds(map[row][col].getValue(), context);

double center_x = x + (cell_width * map[row][col].getC() – bound.getWidth())/2; // + 5/2;
double center_y = y + (cell_height * map[row][col].getR() – bound.getHeight())/2 + fontsize + 5/2;

g.drawString(map[row][col].getValue(), (int)center_x, (int)center_y);
x += cell_width;
}
y += cell_height;
}
ImageIO.write(bi, “jpg”, file);
System.out.println(“done”);
}

public static void main(String[] args) throws IOException {
String xslFile = “/data/workspace_javaee/table.xls”;
String imgFile = “/data/workspace_javaee//test.jpg”;
Xsl2Img temp = new Xsl2Img(xslFile, imgFile);
temp.genImg();
}
}

Advertisements
此条目发表在未分类分类目录,贴了, , 标签。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s