使用poi或jxl，通过java读写xls、xlsx文档

package nicetime.com.baseutil;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 1）设计一个类ExcelUtil，包含readExcel 、writeExcel 和getSumSocre 方法
 * 2）readExcel 使用poi包 读取文件，读取文件时 区分xls 和xlsx
 * 3）getSumSocre 方法用于计算 每个学生的成绩总分；
 * 4）writeExcel 方法使用jxl.jar 包 ，把计算每个学生的总成绩写入到student_sorce.xlsx 文件中
 * 5）把上述文件的内容 写入到数据库中的xxx库的student_score 表中， 下面是mysql 连接信息和要求
 * Mysql 连接信息：连接地址：192.168.1.133:3306 数据库名： xxx 用户名：xxx 密码： xxx
 * 要求：每个人以自己的名字拼音新建一表，表名格式为：姓名拼音_student_score ， 包括的字段有 name（姓名），question_1（第一题），question_2（第二题）……..，total_score（总分）8个字段。
  */

public class ExcelUtil
{
    // 测试环境
    private String url = "jdbc:mysql://192.168.1.133:3306/数据库名?autoReconnect=true&useUnicode=true&characterEncoding=utf8&allowMultiQueries=true" +
            "&rewriteBatchedStatements=true&useServerPrepStmts=true&cachePrepStmts=true&useSSL=false&&failOverReadOnly=false";
    private String user = "用户名";
    private String password = "密码";

    /**
     * poi方式-读xls、xlsx文件并写入数据到数据库表
     */
    public void writeScoreInfoToDB(String fileName)
    {
        File xlsFile = new File(fileName);

        // 获得工作簿
        org.apache.poi.ss.usermodel.Workbook workbook = null;

        String end=fileName.substring(fileName.lastIndexOf("."));
        InputStream input=null;

        try {
            input=new FileInputStream(fileName);

            if(".xls".endsWith(end))
            {
                workbook=new HSSFWorkbook(input);
            }

            if(".xlsx".endsWith(end))
            {
                workbook=new XSSFWorkbook(input);

            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        // 获得工作表个数
        int sheetCount = workbook.getNumberOfSheets();

            Connection conn = null;
            PreparedStatement pstm = null;

            try {
                //加载MySQL驱动
                Class.forName("com.mysql.jdbc.Driver");

                //创建数据库表 test_student_score
                String createSql="DROP TABLE IF EXISTS `test_student_score`;
" +
                        "CREATE TABLE `huangtao_student_score` (
" +
                        "	`id` INT(11) NOT NULL AUTO_INCREMENT,
" +
                        "	`name` VARCHAR(4) NOT NULL DEFAULT '0' COMMENT '姓名',
" +
                        "	`question_1` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第一题成绩',
" +
                        "	`question_2` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第二题成绩',
" +
                        "	`question_3` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第三题成绩',
" +
                        "	`question_4` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第四题成绩',
" +
                        "	`question_5` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第五题成绩',
" +
                        "	`question_6` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第六题成绩',
" +
                        "	`total_score` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '总成绩',
" +
                        "	PRIMARY KEY (`id`)
" +
                        ")ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE='utf8_general_ci' COMMENT='学生成绩信息表';";

                //插入数据到表中
                String insertSql = "INSERT INTO `test_student_score` " +
                        "(`name`,`question_1`,`question_2`,`question_3`,`question_4`,`question_5`,`question_6`,`total_score`) " +
                        "VALUES (?,?,?,?,?,?,?,?);";

                conn = DriverManager.getConnection(url, user, password);

                //预执行创建数据库表的sql语句
                pstm = conn.prepareStatement(createSql);
                pstm.execute();


                // 遍历工作表
                for (int i = 0; i < sheetCount; i++)
                {
                    org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(i);

                    // 获得行数
                    int rows = sheet.getLastRowNum() + 1;

                    // 获得列数，先获得一行，再得到该行的列数
                    Row tmp = sheet.getRow(0);

                    if (tmp == null)
                    {
                        continue;
                    }
                    int cols = tmp.getPhysicalNumberOfCells();

//                    System.out.println("cols"+cols+"row_"+rows);

                    // 读取数据 第1行1列数据为中文，所以需另外处理
                    for (int row = 1; row <rows; row++)
                    {
                        Row r = sheet.getRow(row);

                        //预执行sql语句
                        pstm = conn.prepareStatement(insertSql);

                        for (int col = 0; col <cols; col++)
                        {
                            //第1列数据为中文
                            if(col==0)
                            {
                                String value=r.getCell(0).getStringCellValue();
                                pstm.setString(1,value);

//                                System.out.printf("%10s", value);
                            }
                            else
                            {
                                //其他列数据为数字，用该方式处理
                                double value=r.getCell(col).getNumericCellValue();

                                pstm.setDouble(col+1,value);

//                                System.out.printf("%10s", Math.round(value));
                            }
                        }

//                        System.out.println("");

                        //插入数据到表中
                        pstm.execute();
                    }
                }

            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
        }

    /**
     * jxl方式-读数据
     * 小数据量建议用jxl方式，大数据量建议用poi方式
     */
    public void readxlsExcelByjxl(String fileName)
    {
        File xlsFile = new File(fileName);

        // 获得工作簿对象
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(xlsFile);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }

        // 获得所有工作表
        Sheet[] sheets = workbook.getSheets();

        // 遍历工作表
        if (sheets != null)
        {
            for (Sheet sheet : sheets)
            {
                // 获得行数
                int rows = sheet.getRows();

                // 获得列数
                int cols = sheet.getColumns();

                // 读取数据
                for (int row = 0; row < rows; row++)
                {
                    for (int col = 0; col < cols; col++)
                    {
                        System.out.printf("%10s", sheet.getCell(col, row).getContents());
                    }
                    System.out.println();
                }
            }
        }
        workbook.close();
    }

    /**
     * jxl-新增数据到已有xls表
     * 小数据量建议用jxl方式，大数据量建议用poi方式
     */
    public void updatexlsExcelByjxl(String fileName)
    {
        File xlsFile = new File(fileName);

        // 获取一个工作簿
        Workbook workbook = null;
        try {
            workbook=Workbook.getWorkbook(xlsFile);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }

        //将该工作簿设置为可编辑，相当于开了一个副本
        WritableWorkbook writeWorkbook=null;
        try {
            writeWorkbook=Workbook.createWorkbook(xlsFile,workbook);
        } catch (IOException e) {
            e.printStackTrace();
        }

        //获取第1个工作表
        WritableSheet sheet = writeWorkbook.getSheet(0);

        // 获得行数
        int rows = sheet.getRows();

        // 获得列数
        int cols = sheet.getColumns();

        // 向工作表中添加数据
        //生成rows行clos+1列表格的数据

        //计算每个学生的总成绩，并写入到最后一列中
        for (int row = 0; row < rows; row++)
        {
            int sum=0;

            for (int col = 0; col < cols; col++)
            {
                // 向工作表中添加数据
                //排除第1行1列数据
                if(row!=0&&col!=0)
                {
                    //获取每题成绩
                    int value=Integer.valueOf(sheet.getCell(col, row).getContents());
                    int score=Math.round(value);

                    //求总和
                    sum=sum+score;
                }
            }

            //将每个学生的总成绩写入表中的最后1列
            try {

                if(row!=0)
                {
                    sheet.addCell(new Label(cols, row,String.valueOf(sum)));
//                    System.out.println("row_"+row+"_"+sum);
                }
            } catch (WriteException e) {
                e.printStackTrace();
            }
        }

        //增加一列为总成绩
        try {
            sheet.addCell(new Label(cols, 0,"学生总成绩"));
        } catch (WriteException e) {
            e.printStackTrace();
        }
        try {
            writeWorkbook.write();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            writeWorkbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }

    /**
     * poi方式-读xls、xlsx文件
     */
    public void readExcel(String fileName)
    {
        File xlsFile = new File(fileName);

        // 获得工作簿
        org.apache.poi.ss.usermodel.Workbook workbook = null;

        String end=fileName.substring(fileName.lastIndexOf("."));
        InputStream input=null;

        try {
            input=new FileInputStream(fileName);

            if(".xls".endsWith(end))
            {
                workbook=new HSSFWorkbook(input);
            }

            if(".xlsx".endsWith(end))
            {
                workbook=new XSSFWorkbook(input);

            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        // 获得工作表个数
        int sheetCount = workbook.getNumberOfSheets();

        // 遍历工作表
        for (int i = 0; i < sheetCount; i++)
        {

            org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(i);

            // 获得行数
            int rows = sheet.getLastRowNum() + 1;

            // 获得列数，先获得一行，再得到该行的列数
            Row tmp = sheet.getRow(0);

            if (tmp == null)
            {
                continue;
            }
            int cols = tmp.getPhysicalNumberOfCells();

            // 读取数据 第1行1列数据为中文，所以需另外处理
            for (int row = 0; row < rows; row++)
            {
                Row r = sheet.getRow(row);

                for (int col = 0; col < cols; col++)
                {
                    //第1行1列数据为中文
                    if(row==0||col==0)
                    {
                        String value=r.getCell(col).getStringCellValue();
                        System.out.printf("%10s", value);
                    }
                    else
                    {
                        //其他列数据为数字，用该方式处理
                        double value=r.getCell(col).getNumericCellValue();
                        System.out.printf("%10s", Math.round(value));
                    }

                }
                System.out.println();
            }
        }
    }

    /**
     * poi方式-修改xls、xlsx文件
     */
    public void writeExcel(String fileName)
    {
        File xlsFile = new File(fileName);

        // 获得工作簿
        org.apache.poi.ss.usermodel.Workbook workbook = null;

        //获取文件中.的位置
        String end=fileName.substring(fileName.lastIndexOf("."));

        FileInputStream fileInput=null;

        FileOutputStream fileOutput=null;

        try {
            fileInput=new FileInputStream(fileName);

            //根据文件类型创建workbook对象
            if(".xls".endsWith(end))
            {
                workbook=new HSSFWorkbook(fileInput);
            }

            if(".xlsx".endsWith(end))
            {
                workbook=new XSSFWorkbook(fileInput);

            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        // 获得工作表个数
        int sheetCount = workbook.getNumberOfSheets();

        // 遍历工作表
        for (int i = 0; i < sheetCount; i++)
        {
            org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(i);

            // 获得行数
            int rows = sheet.getLastRowNum() + 1;

            // 获得列数，先获得一行，再得到该行的列数
            Row tmp = sheet.getRow(0);

            if (tmp == null)
            {
                continue;
            }
            int cols = tmp.getPhysicalNumberOfCells();

            // 向工作表中添加数据
            //生成rows行clos+1列表格的数据

            //计算每个学生的总成绩，并写入到最后一列中
            for (int row = 0; row < rows; row++)
            {
                long sum=0;
                Row r = sheet.getRow(row);

                for (int col = 0; col < cols; col++)
                {
                    // 向工作表中添加数据
                    //排除第1行1列数据
                    if(row!=0&&col!=0)
                    {
                        //获取每题成绩
                        double value=r.getCell(col).getNumericCellValue();
                        long score=Math.round(value);

                        //求总和
                        sum=sum+score;
                    }
                }

                //将每个学生的总成绩写入表中的最后1列
                if(row!=0)
                {
                    r.createCell(cols).setCellValue(sum);
//                    System.out.println("sum="+sum);
                }
            }

            //增加一列为总成绩 学生总成绩
            sheet.getRow(0).createCell(cols).setCellValue("学生总成绩");

            try {
                //将文件保存
                fileOutput=new FileOutputStream(fileName);

                workbook.write(fileOutput);

                //关闭打开文件的对象
                fileOutput.close();

            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }

    /**
     * poi方式-获得每个学生的成绩总分 适用于xls xlsx文件
     * @param fileName
     */
    public void getSumSocre(String fileName)
    {
        File xlsFile = new File(fileName);

        // 获得工作簿
        org.apache.poi.ss.usermodel.Workbook workbook = null;

        String end=fileName.substring(fileName.lastIndexOf("."));
        InputStream input=null;

        try {
            input=new FileInputStream(fileName);

            if(".xls".endsWith(end))
            {
                workbook=new HSSFWorkbook(input);
            }

            if(".xlsx".endsWith(end))
            {
                workbook=new XSSFWorkbook(input);

            }

        } catch (IOException e) {
            e.printStackTrace();
        }

        // 获得工作表个数
        int sheetCount = workbook.getNumberOfSheets();

        // 遍历工作表
        for (int i = 0; i < sheetCount; i++)
        {
            org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(i);

            // 获得行数
            int rows = sheet.getLastRowNum() + 1;

            // 获得列数，先获得一行，再得到该行的列数
            Row tmp = sheet.getRow(0);

            if (tmp == null)
            {
                continue;
            }
            int cols = tmp.getPhysicalNumberOfCells();

            // 读取数据 第1行1列数据为中文，所以需另外处理
            for (int row = 0; row < rows; row++)
            {
                long sum=0;
                String value=null;
                Row r = sheet.getRow(row);

                for (int col = 0; col < cols; col++)
                {
                    //第1行1列数据为中文
                    if(row==0||col==0)
                    {
                        value=r.getCell(0).getStringCellValue();
                    }
                    if(row!=0&&col!=0)
                    {
                        //其他列数据为数字，用该方式处理 算出总成绩
                        double valuea=r.getCell(col).getNumericCellValue();
                        long score=Math.round(valuea);

                        //求总和
                        sum=sum+score;
                    }
                }

                //输出姓名
                value=r.getCell(0).getStringCellValue();
                System.out.printf("%10s", value);

                //输出总成绩
                System.out.printf("%10s", sum==0?"总成绩":sum);
                System.out.println();
            }
        }
    }

    public static void main(String[] args)
    {
//        文件名称
        String fileName1="E:\ideaSpace\autoProject\basicUtilTest\src\nicetime\com\baseutil\student_score.xls";
        String fileName2="E:\ideaSpace\autoProject\basicUtilTest\src\nicetime\com\baseutil\student_score.xlsx";

        ExcelUtil eu =new ExcelUtil();

//        第2：readExcel
//        poi方式读xls、xlsx文件
        System.out.println("start===readExcel===");
        eu.readExcel(fileName1);
        eu.readExcel(fileName2);
        System.out.println("end===readExcel===");

//        第3：getSumScore
//        poi方式从xls、xlsx文件中计算并得出每个学生的总成绩
        System.out.println("end===getSumSocre===");
        eu.getSumSocre(fileName1);
        eu.getSumSocre(fileName2);
        System.out.println("end===getSumSocre===");

//        第4：WriteExcel
//        poi方式修改xls、xlsx文件
        System.out.println("start===writeExcel===");
        eu.writeExcel(fileName1);
        eu.writeExcel(fileName2);
        System.out.println("end===writeExcel===");

//        第5：writeScoreInfoToDB
//        poi方式-读xls、xlsx文件并写入数据到数据库表
        System.out.println("start===writeScoreInfoToDB===");
        eu.writeScoreInfoToDB(fileName1);
        eu.writeScoreInfoToDB(fileName2);
        System.out.println("end===writeScoreInfoToDB===");

//        jxl方式读xls文件
        System.out.println("start===readxlsExcelByjxl===");
        eu.readxlsExcelByjxl(fileName1);
        System.out.println("end===readxlsExcelByjxl===");

//        jxl方式在已有的的xls文件中新增内容
        System.out.println("start===updatexlsExcelByjxl===");
        eu.updatexlsExcelByjxl(fileName1);
        System.out.println("end===updatexlsExcelByjxl===");

    }

}