【java】使用mysql+java绘制Excel二维表格

本文是使用java语言对于后台的数据进行绘制，由于所用到的表格的长度较长，直接使用.xlsx后缀的Excel表

1、首先要导入数据库的jar包，以及读取excel的jar包

2、
public static void main(String[] args) throws Exception{
String fileName = "C:\\分词\\test.xlsx";
Test upload = new Test();
upload.getExcelData(fileName,".xlsx");
}

3、判断excel版本

//判断excel版本
static Workbook openWorkbook(InputStream in,String filename,String fileFileName)throws IOException{

    Workbook wb = null;
    if(fileFileName.endsWith(".xlsx")){

        wb = new XSSFWorkbook(in);//Excel 2007
    } else {

        wb = (Workbook) new HSSFWorkbook(in);//Excel 2003

    }
    return wb;
}

4、

public static void getExcelData(String fileName,String fileFileName) throws Exception {
    OutputStream out = null;
    InputStream in = new FileInputStream(fileName); //创建输入流
    Workbook wb = openWorkbook(in, fileName,fileFileName);// 获取Excel文件对象
    Sheet sheet = wb.getSheetAt(0);// 获取文件的指定工作表m 默认的第一个
    out =  new FileOutputStream(fileName);

5、

System.out.println("===========正在导入横坐标============");
    String sql = null;
    DBHelper db1;
    ResultSet ret = null;
    sql = "select * from table ";//SQL语句
    db1 = new DBHelper(sql);//创建DBHelper对象
    ret = db1.pst.executeQuery();//执行语句，得到结果集
    int xLocation = 1;
    Row row0 = sheet.createRow(0);
    while (ret.next()) {
        Cell first0 = row0.createCell(xLocation);
        String keyword = ret.getString(2);
        first0.setCellValue(keyword);
        xLocation ++ ;
    }
    db1.close();
    ret.close();

6、

System.out.println("==========正在导入纵坐标=======");
    DBHelper dbY;
    ResultSet retY = null;
    String sqlY = "select distinct paperid from co_word_ty ";//SQL语句
    dbY = new DBHelper(sqlY);//创建DBHelper对象
    retY = dbY.pst.executeQuery();//执行语句，得到结果集
   // out =  new FileOutputStream(fileName);
    int yLocation = 1;
    while (retY.next()) {
        Row rowY = sheet.createRow(yLocation);
        Cell fristY = rowY.createCell(0);
        String paperid = retY.getString(1);
        fristY.setCellValue(paperid);
        yLocation ++;
    }
    dbY.close();
    retY.close();

7、

/*
     * 
     * 初始化数据表
     */
    /*System.out.println("==================正在初始化表格=================");
    int rowNumber = sheet.getLastRowNum();
    for(int i = 1;i <= rowNumber;i++){
        Row rowXY = sheet.getRow(i);
        int cellNumber = rowXY.getLastCellNum();
        for(int j=1;j<= xLocation-1;j++){
            System.out.println(j);
            Cell fristXY = rowXY.createCell(j);

            fristXY.setCellValue(0);
        }

    }*/

8、

/*
     * 
     * 填充数据
     */
    System.out.println("==================正在输出数据=================");
    DBHelper dbXYData;
    ResultSet retXYData = null;
    String sqlXYData = "select locationX,locationY from co_word_ty ";//SQL语句
    dbXYData = new DBHelper(sqlXYData);
    retXYData = dbXYData.pst.executeQuery();
    while (retXYData.next()) {
        String x = retXYData.getString(1);
        String y = retXYData.getString(2);

        //System.out.println(x);
        if(x != null && x != ""){
            System.out.println(Integer.parseInt(x)+"======================="+Integer.parseInt(y));
            Row rowXYData = sheet.getRow(Integer.parseInt(y));
            Cell fristXYData = rowXYData.createCell(Integer.parseInt(x));
            fristXYData.setCellValue(1);
        }

    }
    dbXYData.close();
    retXYData.close();


    wb.write(out); 
    System.out.println("=====================done===================");