编写ExcelHandlecom.hynnet jxl2.6.12.1 org.projectlombok lomboktrue
package com.manager.oa.util;
import com.manager.oa.exception.DoSqlException;
import com.manager.oa.exception.ReadFileException;
import com.manager.oa.exception.WriteExcelException;
import com.manager.oa.pojo.oa.Staff;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
@Slf4j
@Component
public class ExcelHandle {
public boolean readExcel(String excelFilePath, String sqlFilePath, String sqlResult) {
Sheet sheet;
Workbook book;
Cell cell1;
try {
//excelFilePath为要读取的excel文件路径，带上文件名
book = Workbook.getWorkbook(new File(excelFilePath));
//获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
sheet = book.getSheet(0);
//获取左上角的单元格
cell1 = sheet.getCell(0, 0);
log.info("【系统日志】 Excel标题：{}", cell1.getContents());
StringBuilder dbSql = new StringBuilder();
//读取到的sheet
Sheet[] sheetAll = book.getSheets();
Sheet s = sheetAll[0];
//获取该sheet 的有效数据的列数
int totalColumns = s.getColumns();
//获取该sheet 的有效数据的行数
int totalRows = s.getRows();
for (int i = 2; i < totalRows; i++) {
StringBuilder sql = new StringBuilder(sqlResult.substring(0, sqlResult.lastIndexOf(")")));
for (int j = 0; j < totalColumns; j++) {
Cell cell = sheet.getCell(j, i);//（列，行）
sql.append("'").append(cell.getContents()).append("',");
}
// 去掉最后一个逗号
sql = new StringBuilder(sql.substring(0, sql.toString().lastIndexOf(',')));
sql.append(");n");
dbSql.append(sql);
log.info("【系统日志】 sql：{}", sql);
}
// 生成sql文件
File file = new File(sqlFilePath);
if (!file.exists()) {
file.createNewFile();
}
byte[] bytes = dbSql.toString().getBytes();
OutputStream os = new FileOutputStream(sqlFilePath);
os.write(bytes);
os.flush();
os.close();
book.close();
} catch (Exception e) {
e.printStackTrace();
throw new ReadFileException("sql转换失败", e);
}
return true;
}
public void writeExcel(String resultExcel, List list) throws IOException {
File file = new File(resultExcel);
if (file.exists()) {
file.createNewFile();
}
try {
// resultExcel：写入到那个Excel文件 如：c:\hello.xls,或者hello.xls(这个是在项目的根目录下)
WritableWorkbook wwb = Workbook
.createWorkbook(new File(resultExcel));
// 创建Excel工作表 指定名称和位置
WritableSheet ws = wwb.createSheet("员工名单", 0);
// 设置表格的列宽度
ws.setColumnView(0, 14);//第一列宽14
ws.setColumnView(1, 12);
ws.setColumnView(2, 25);
ws.setColumnView(3, 9);
ws.setColumnView(4, 12);//第5列宽12
// **************往工作表中添加数据*****************
//定义字体格式：字体为：微软雅黑，24号子，加粗
WritableFont titleFont = new WritableFont(WritableFont
.createFont("微软雅黑"), 24, WritableFont.NO_BOLD);
WritableFont contentFont = new WritableFont(WritableFont
.createFont("楷体 _GB2312"), 12, WritableFont.NO_BOLD);
WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
WritableCellFormat contentFormat = new WritableCellFormat(
contentFont);
WritableCellFormat contentFormat2 = new WritableCellFormat(
contentFont);
contentFormat.setBorder(Border.ALL, BorderLineStyle.THIN,
Colour.BLACK);
//设置格式居中对齐
titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
contentFormat2.setAlignment(jxl.format.Alignment.CENTRE);
// ***************将定义好的单元格添加到工作表中*****************
ws.mergeCells(0, 0, 4, 0);// 合并5列
ws.addCell(new Label(0, 0, "人员名单", titleFormat));
ws.addCell(new Label(0, 1, "部门名称", contentFormat2));
ws.mergeCells(1, 1, 4, 1);// 合并4列
ws.addCell(new Label(1, 1, "所有部门", contentFormat2));
ws.addCell(new Label(0, 2, "填表人", contentFormat2));
ws.addCell(new Label(2, 2, "部门负责人", contentFormat2));
String th[] = {"姓名", "性别", "入职时间", "薪资", "所在部门"};
for (int i = 0; i < 5; i++) {
ws.addCell(new Label(i, 3, th[i], contentFormat2));
}
//这里我们可以从数据库里面查询数据，然后在这里获取数据
int j = 4;
for (Staff s : list
) {
log.debug("【系统日志】T->{}", s.toString());
ws.addCell(new Label(0, j, s.getName(), contentFormat));
ws.addCell(new Label(1, j, s.getGender(), contentFormat));
ws.addCell(new Label(2, j, s.getEntryDate(), contentFormat));
ws.addCell(new Label(3, j, s.getSalary().toString(), contentFormat));
ws.addCell(new Label(4, j, s.getDept().getName(), contentFormat));
j++;
}
// 写入工作表完毕，关闭流
wwb.write();
wwb.close();
} catch (Exception e) {
throw new WriteExcelException("写入到Excel文件出错", e);
}
}
}
调用方法
1，将工具类交给spring管理
@Autowired private ExcelHandle excelHandle;
2、调用方法
1）读取Excel产生sql文件
// sql String sql = "INSERT INTO oa_emp (name,gender,photo,entry_date,salary) VALUES();"; // true 表示成功，false表示失败 boolean flag = excelHandle.readExcel(filePath, sqlPath, sql);
格局Excel 表格修改相应的sql语句
比如我的：
1）产生Excel方法
1，在application.yml配置产生的Excel文件路径
oa: staffExcelDownLoad: E: 1_program 2_Java EEstaffUploadstaffExceldownLoad
2，注入
@Autowired
private ExcelHandle excelHandle;
@Value("${oa.staffExcelDownLoad}")
private String staffExcelDownLoad;
3，调用方法
// downLoadPath:产生的Excel文件路径；第二个参数，需要产生的List集合 excelHandle.writeExcel(downLoadPath, staffService.getAll(staff));
最后产生的Excel：
