- POI依赖介绍
- CSV格式介绍
- JXL依赖介绍
- 使用依赖
- 上下文配置
- Excel2003格式导出
- 测试数据库表是否可以正常打印
- Excel2007格式
- CSV文本格式
- 使用JXL依赖导出Excel
excel对行列的限制
| excel2003 | 65536 | 256|
| excel2007 | 1048576 | 16384 |
1. Excel2003格式（java对象使用HSSFWorkbook）
Excel2003支持每个工作表中最多有 65536 行和 256列。对于工作表能够支持的最大个数，受CPU反应速度和内存大小影响。
采用常用导出方式导出数据时，需要注意的是Excel 2003行数和列数的限制。常用导出方式中的POI支持该格式的只有HSSF包
，当导出数据量大于一页的最大行数（65536）时，可采取分页的形式进行存储。
2. Excel2007格式(java对象使用SXSSFWorkbook)
Excel 2007是Excel 2003的升级版，Excel 2007支持每个工作表中最多有 1,048,576 行和16,384 列。采用常用导出方式导出数据时，需要注意的是Excel 2007行数和列数的限制，常用导出方式支持该格式的只有XSSF包，包含SXSSF扩展包，并且仅有SXSSF支持大数据。
CSV没有最大行数的限制,使用excel打开时会限制显示一百万行多点
CSV是一种通用的、相对简单的文件格式，广泛的应用在程序之间转移表格数据。
它通常具有以下特征：
1）纯文本，可以使用Excel和文本编辑器打开；
2）每条记录被分隔符分隔为字段（典型分隔符有逗号、分号或制表符；有时分隔符可 以包括可选的空格）；
3）常用导出方式不会引起内存溢出问题。
常用导出方式推荐：1
1. JXL介绍
JXL是一个开源的Java Excel API项目。它能作为Java Excel API的一个共同的支持库，是因为它的基本功能是可创建，读取和写入电子表格。基本特征如下：
1）生成Excel文件
2）从工作簿和电子表格导入数据
3）获得行和列的总数
此方式对中文支持很好，不会出现乱码情况，支持单元格的常用 *** 作，满足一般需求，该方式在写入效率上优于POI方式。
需要注意：JXL只支持xls档案格式，并且处理的数据量非常有限。
<dependencies>
<dependency>
<groupId>org.springframework.bootgroupId>
<artifactId>spring-boot-starter-webartifactId>
dependency>
<dependency>
<groupId>mysqlgroupId>
<artifactId>mysql-connector-javaartifactId>
<version>8.0.29version>
dependency>
<dependency>
<groupId>org.mybatis.spring.bootgroupId>
<artifactId>mybatis-spring-boot-starterartifactId>
<version>2.2.2version>
dependency>
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poiartifactId>
<version>3.17version>
dependency>
<dependency>
<groupId>org.apache.poigroupId>
<artifactId>poi-ooxmlartifactId>
<version>3.17version>
dependency>
<dependency>
<groupId>org.xmlunitgroupId>
<artifactId>xmlunit-coreartifactId>
dependency>
<dependency>
<groupId>net.sourceforge.jexcelapigroupId>
<artifactId>jxlartifactId>
<version>2.6.12version>
dependency>
dependencies>
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mm?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=123456
# 开启驼峰命名，不然数据库序列化时查询的数据会为null值
mybatis.configuration.map-underscore-to-camel-case=true
Excel2003格式导出
定义接口类
@Mapper
public interface QyjsdetaMapper {
@Select("select id, account_id ,external_id from table_name")
public List<QyjsdetaMode> qyjs();
}
# 或 (使用传参的形式)
@Mapper
public interface QyjsdetaMapper {
@Select("select id, account_id, external_id from table_name limit #{id}")
public List<QyjsdetaMode> qyjs(int id);
}
实体类对象：用来序列化接受数据库中的数据
public class QyjsdetaMode{
public String id ;
public String accountId ;
public String externalId ;
省略了get、set方法
}
@RestController
public class tableController {
@Autowired
public QyjsdetaMapper table;
@RequestMapping(value = "/mm", method = RequestMethod.GET)
public List<QyjsdetaMode> Qust2(int id ) {
List<QyjsdetaMode> dd = table.qyjs(id);
System.out.println("=====打印===");
System.out.println("=======================helloworld=========================="+"\n" );
return dd ;
}
}
**测试打印数据库数据100行**
http://localhost:8080/mm?id=100
导出Excel实现
@Controller
@RequestMapping("/")
public class QyjsExceldown {
@Resource
public QyjsdetaMapper qyjsdetaMpper;
@RequestMapping(value = "/dd", method = RequestMethod.GET)
public void downloadAllClassmate(HttpServletResponse response) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();//创建HSSFWorkbook对象, excel的文档对象
HSSFSheet sheet = workbook.createSheet("信息表"); //excel的表单
HSSFSheet sheet2 = workbook.createSheet("信息表2"); //excel的表单
List<QyjsdetaMode> classmateList = qyjsdetaMpper.qyjs();
System.out.println("方法1");
String fileName = "userinf" + ".csv";//设置要导出的文件的名字
//新增数据行，并且设置单元格数据
String[] headers = { "id", "姓名", "号码" };
//headers表示excel表中第一行的表头
HSSFRow row1 = sheet.createRow(0);
//在excel表中添加表头
for(int i=0;i<headers.length;i++){
HSSFCell cell = row1.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
HSSFRow row2 = sheet.createRow(1);
int rowNum = 1;
//在表中存放查询到的数据放入对应的列
for (QyjsdetaMode qyjsdetaMode : classmateList) {
if (rowNum < 65535) {
row2 = sheet.createRow(rowNum);
}
// 注意：日期类型需要转换成string类型再输出，同时需要在配置文件中开启驼峰命名，不然会报空指针
DateFormat str = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String id = String.valueOf( qyjsdetaMode.getId() );
String accountId = String.valueOf( qyjsdetaMode.getAccountId() );
String externalId = String.valueOf(qyjsdetaMode.getExternalId() );
//System.out.println("accountId："+accountId +"externalId："+ externalId);
row2.createCell(0).setCellValue(qyjsdetaMode.getId());
row2.createCell(1).setCellValue(qyjsdetaMode.getAccountId());
row2.createCell(2).setCellValue(qyjsdetaMode.getExternalId());
rowNum++;
}
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
response.flushBuffer();
workbook.write(response.getOutputStream());
System.out.println("方法 33333333");
}
}
Excel2007格式
@RestController
public class Sxssf {
@RequestMapping("/ss")
public void exportExcle() throws SQLException, IOException, ClassNotFoundException {
/**
String fileName = "excel_data.xlsx";
// File testFile = new File("D:" + File.separator + "filepath" + File.separator + "test" + File.separator + fileName);
File testFile = new File(fileName);
if (!testFile.exists()) {
//testFile.mkdirs();// 能创建多级目录
testFile.createNewFile(); // 创建文件
}else {
testFile.delete() ;
testFile.createNewFile(); // 创建文件
}
System.out.println("testFile:"+testFile
+"\ngetName:"+ testFile.getName()
+"\ngetPath:"+ testFile.getPath()
+"\ngetParent:"+testFile.getParent()
+"\ngetAbsolutePath:"+ testFile.getAbsolutePath()); 显示文件的绝对路径
**/
Class.forName("com.mysql.cj.jdbc.Driver"); //加载MYSQL JDBC驱动程序
String url = "jdbc:mysql://localhost:3306/mm";
Connection conn = DriverManager.getConnection(url, "root", "123456");
Statement stat = conn.createStatement();
// String sql = "select u.user_id ,u.account,u.name from " + "sys_user u";
String sql = "select id , account_id acc,external_id ext from "+ "table_name limit 10 " ;
// 格式: String sql = "select * from " + TableName where 1=1;
ResultSet rs = stat.executeQuery(sql);
/** 数据读取后就释放掉了 -- 测试
while(rs.next()){
// Object object = rs.getObject("1");
String id = rs.getString("id");
String name = rs.getString("acc");
String lds = rs.getString("ext") ;
System.out.println(id+" "+name+" "+"acc");
System.out.println("========================"+lds);
}
*/
long t1 = System.currentTimeMillis();
SXSSFWorkbook workbook = new SXSSFWorkbook();
workbook.createSheet("aaa");
SXSSFSheet aaa = workbook.getSheetAt(0);
// 测试导出100万行数据
for (int i=0;i<1000000;i++){
aaa.createRow(i);
aaa.getRow(i).createCell(0).setCellValue("aaaaaaaaaaaaaaaaaaaaaaa");
aaa.getRow(i).createCell(1).setCellValue("aaaaaaaaaaaaaaaaaaaaaaa");
aaa.getRow(i).createCell(2).setCellValue("aaaaaaaaaaaaaaaaaaaaaaa");
aaa.getRow(i).createCell(3).setCellValue("aaaaaaaaaaaaaaaaaaaaaaa");
aaa.getRow(i).createCell(4).setCellValue("aaaaaaaaaaaaaaaaaaaaaaa");
}
OutputStream outputStream = null;
// 打开目的输入流，不存在则会创建
outputStream = new FileOutputStream("out.xlsx");
workbook.write(outputStream);
outputStream.close();
long t2 = System.currentTimeMillis();
System.out.println("SXSSFWorkbook : 100w条数据写入Excel 消耗时间："+ (t2-t1));
//======================================================================
//设置存储在内存的行数，多余的存储在硬盘
int cacheItems = 100;
SXSSFWorkbook wb = new SXSSFWorkbook(cacheItems);
wb.createSheet("测试");
SXSSFSheet sh = wb.getSheetAt(0);
// 导出数据库表数据
int rownum = 0;
while(rs.next()){
sh.createRow(rownum);
sh.getRow(rownum).createCell(0).setCellValue(rs.getString("id"));
sh.getRow(rownum).createCell(1).setCellValue(rs.getString("acc"));
sh.getRow(rownum).createCell(2).setCellValue(rs.getString("ext"));
rownum ++;
//每当行数达到设置的值就刷新数据到硬盘,以清理内存
if(rownum % cacheItems == 0){
((SXSSFSheet)sh).flushRows();
}
}
OutputStream out = new FileOutputStream("excel_data.xlsx");
wb.write(out);
out.close();
}
}
CSV文本格式
@RestController
public class Csvexcel {
@RequestMapping("/csv")
public void exportExcle() throws IOException, SQLException, ClassNotFoundException {
// 数据库连接配置
Class.forName("com.mysql.cj.jdbc.Driver"); //加载MYSQL JDBC驱动程序
String url = "jdbc:mysql://localhost:3306/mm";
Connection conn = DriverManager.getConnection(url, "root", "123456");
Statement stat = conn.createStatement();
// String sql = "select u.user_id ,u.account,u.name from " + "sys_user u";
String sql = "select id , account_id acc,external_id ext from "+ " table_name limit 10 " ;
File csvFile = new File("excel_data.csv");
File parent = csvFile.getParentFile();
if (parent != null && !parent.exists()) {
parent.mkdirs();
}
csvFile.createNewFile();
// UTF-8使正确读取分隔符","
BufferedWriter csvWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8"), 1024);
ResultSet rs = stat.executeQuery(sql);//查询数据
while(rs.next()){
StringBuffer sb = new StringBuffer();
sb.append("\"").append(rs.getString("id")).append("\",");
sb.append("\"").append(rs.getString("acc")).append("\",");
sb.append("\"").append(rs.getString("ext")).append("\"");
csvWriter.write(sb.toString());
csvWriter.newLine();
}
csvWriter.close();
}
// 对百万级以上数据使用分页查询，不然会内存溢出
@RequestMapping("/csv2")
public void exportExcle() throws IOException, SQLException, ClassNotFoundException {
// 数据库连接配置
Class.forName("com.mysql.cj.jdbc.Driver"); //加载MYSQL JDBC驱动程序
String url = "jdbc:mysql://localhost:3306/mm";
Connection conn = DriverManager.getConnection(url, "root", "123456");
Statement stat = conn.createStatement();
File csvFile = new File("excel_data.csv");
File parent = csvFile.getParentFile();
if (parent != null && !parent.exists()) {
parent.mkdirs();
}
csvFile.createNewFile();
System.out.println("=======parent======="+csvFile);
// UTF-8使正确读取分隔符","
BufferedWriter csvWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "UTF-8"), 1024);
// 记录插入的时间
long start = System.currentTimeMillis();
// 设置分页查询的条数,每次查询一百万万，数据太大不适用分页查询会内存溢出
int offset = 0;
int size = 1000000;
while (offset<20000000){ // 插入两千万数据测试
String sql = String.format("select id , account_id acc,external_id ext from table_name limit %s, %s",offset,size);
ResultSet rs = stat.executeQuery(sql);//查询数据
while(rs.next()){
StringBuffer sb = new StringBuffer();
sb.append("\"").append(rs.getString("id")).append("\","); // 追加内容指定分割符
sb.append("\"").append(rs.getString("acc")).append("\",");
sb.append("\"").append(rs.getString("ext")).append("\"");
csvWriter.write(sb.toString());
csvWriter.newLine();
}
offset += size;
long end_start = System.currentTimeMillis() - start;
System.out.println(String.format(" 🚀🚀🚀 分页查询条数据与耗时 :: %s end_start==%s "
, String.valueOf(offset),String.valueOf(end_start)) );
}
csvWriter.close();
}
}
使用JXL依赖导出Excel
@RestController
public class Hssfexcel {
@RequestMapping("/ceshi")
//创建XLS文档，并写入数据
public void createXLS()throws Exception{
//create new XLS document
jxl.write.WritableWorkbook book = jxl.Workbook.createWorkbook(new File("测试.xls"));
//生成名为“第一页”的工作表，参数0表示是第一页
jxl.write.WritableSheet sheet = book.createSheet("第一页",0);
//在Label对象的构造出单元格第一列第一行(0,0,)的值为test
jxl.write.Label label = new jxl.write.Label(0,0,"test");
//将定义好的Label对象利用sheet对象添加到工作表中
sheet.addCell(label);
//生产数据的单元格
jxl.write.Number number = new jxl.write.Number(1,0,123.21);
sheet.addCell(number);
//写入数据并进行关闭
book.write();
book.close();
}
@Resource
public QyjsdetaMapper qyjsdetaMpper;
@RequestMapping(value = "/shuru", method = RequestMethod.GET)
public void downloadAllClassmate(HttpServletResponse response) throws IOException, WriteException {
//create new XLS document
jxl.write.WritableWorkbook book = jxl.Workbook.createWorkbook(new File("测试.xls"));
//生成名为“第一页”的工作表，参数0表示是第一页
jxl.write.WritableSheet sheet = book.createSheet("第一页", 0);
//在Label对象的构造出单元格第一列第一行(0,0,)的值为test
jxl.write.Number label01 = new jxl.write.Number(0, 0, 001);
sheet.addCell(label01);//将定义好的Label对象利用sheet对象添加到工作表中
//生产数据的单元格
jxl.write.Label label02 = new jxl.write.Label(1, 0, "交易id");
sheet.addCell(label02);
jxl.write.Label label03 = new jxl.write.Label(2, 0, "账户id");
sheet.addCell(label03);
List<QyjsdetaMode> classmateList = qyjsdetaMpper.qyjs();
int rowNum = 1;
// 对数据进行序列化
for (QyjsdetaMode qyjsdetaMode : classmateList) {
DateFormat str = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String id = String.valueOf(qyjsdetaMode.getId());
String accountId = String.valueOf(qyjsdetaMode.getAccountId());
String externalId = String.valueOf(qyjsdetaMode.getExternalId());
System.out.println("accountId：" + accountId + "externalId：" + externalId);
jxl.write.Label label1 = new jxl.write.Label(0, rowNum, id);
jxl.write.Label label2 = new jxl.write.Label(1, rowNum, accountId);
jxl.write.Label label3 = new jxl.write.Label(2, rowNum, externalId);
sheet.addCell(label1);
sheet.addCell(label2);
sheet.addCell(label3);
if (rowNum < 60000) {
rowNum++;
}
}
//写入数据并进行关闭
book.write();
book.close();
}
@RequestMapping("/duqu")
//一次型读取XLS文档
public void readXLS()throws Exception{
//获取XLS对象
jxl.Workbook book = jxl.Workbook.getWorkbook(new File("测试.xls"));
//获取XLS第一页的工作表
jxl.Sheet sheet = book.getSheet(0);
//进行读取，可根据自己的实际情况去读取
int i=0;
do{
int j=0;
String str1;
do{
jxl.Cell cell;
try{
//通过j,i的坐标找到单元格
cell = sheet.getCell(j,i);
}catch(ArrayIndexOutOfBoundsException ex){
break;
}
//用String接收单元格的值
str1= cell.getContents();
System.out.println(str1);
j++;
}while(true);
i++;
}while(true);
}
}
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）