使用poi或jxl，通过java读写xls、xlsx文档
2024-09-04 09:11:46
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`;\n" +
"CREATE TABLE `huangtao_student_score` (\n" +
"\t`id` INT(11) NOT NULL AUTO_INCREMENT,\n" +
"\t`name` VARCHAR(4) NOT NULL DEFAULT '0' COMMENT '姓名',\n" +
"\t`question_1` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第一题成绩',\n" +
"\t`question_2` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第二题成绩',\n" +
"\t`question_3` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第三题成绩',\n" +
"\t`question_4` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第四题成绩',\n" +
"\t`question_5` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第五题成绩',\n" +
"\t`question_6` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '第六题成绩',\n" +
"\t`total_score` SMALLINT(6) NOT NULL DEFAULT '0' COMMENT '总成绩',\n" +
"\tPRIMARY KEY (`id`)\n" +
")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==="); } }
最新文章
- 手游聚合SDK开发之远程开关---渠道登入白名单
- MySQL配置、使用规范
- STM32F429i-DISCO FreeRTOS keil STM32CubeMX
- datatables ajax后端请求那些坑
- 用systemd脚本自动启动node js程序
- Java IO1：IO和File
- jquery点击添加样式，再点击取出样式
- 通过js获取前台数据向一般处理程序传递Json数据，并解析Json数据，将前台传来的Json数据写入数据库表中
- 数学复习 ---- Mathematics Notes: A Programmer's Perspective ---- by Orzer ---- 我是沙茶
- Linux防火墙iptables学习笔记（三）iptables命令详解和举例[转载]
- 【转】怎样创建一个Xcode插件（Part 2）
- android 瀑布流（图片浏览）
- AsyncTask 不能与Thread.sleep()同时使用解决方案
- DevExpress的Web控件汉化方法
- Linux 系统分区
- css3 UI 修饰——回顾
- Linux - crontab的创建以及注意事项
- Hibernate-ORM:05.Hibernate中的list()和iterator()
- P1417 烹调方案 (0/1背包+贪心)
- JSON.parseObject 和 JSON.toJSONString
热门文章
- 【248】◀▶IEW-Unit13
- iis部署错误：HTTP 错误 500.21 - Internal Server Error
- 洛谷P1654 产品排序（sort）
- uoj#290. 【ZJOI2017】仙人掌（数数+仙人掌+树形dp）
- 深入Java集合学习系列：HashSet的实现原理
- 程序员收藏必看系列：深度解析MySOL优化（一）
- iOS风格的弹出框（alert，prompt,confirm）
- oracle 查看 job 日志
- centOS7.5上部署server jre1.8.0_192 tomcat-8.5.35 mysql-8.0.13
- IOS 打包提示错误（ERROR ITMS-90125: ERROR ITMS-90087: ERROR ITMS-90209:）