刚整出来自己用的excel 的工具类,暂时还没啥花里胡哨的功能,等有需求了再补充吧
package com.wkrj.module.commons.utils;
import com.wkrj.module.commons.exception.BussException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.net.URL;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* excel Util
* @qq 1092843362
* @author gd
*/
public class ExcelImportUtil {
/**
* 读取excel中数据 ,一行数据对应Map,key值 'cell1'表示 该行 第一个单元格数据 ,key 值 index 表示行数 从0开始
* @param fileType xls OR xlsx
* @param inputStream
* @param sheet sheet num,begin 0 to xxx
* @param row start row ,begin 0 to xxx
* @return List<Map<String,Object>>
*/
public static List<Map<String,Object>> getSheetListInfo(String fileType, InputStream inputStream,int sheet,int row){
List<Map<String,Object>> data=new LinkedList<>();
try{
if("xls".equals(fileType.toLowerCase())){
HSSFWorkbook hssfWorkbook=new HSSFWorkbook(inputStream);
// 创建对工作表的引用
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheet);
for (int rowNum = row; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {continue;}
Map<String,Object> rowInfo= ExcelImportUtil.getRowInfos(hssfRow);
rowInfo.put("index",rowNum+"");
data.add(rowInfo);
}
}else if("xlsx".equals(fileType.toLowerCase())){
XSSFWorkbook xssfWorkbook=new XSSFWorkbook(inputStream);
XSSFSheet xssfSheet=xssfWorkbook.getSheetAt(sheet);
for (int rowNum = row; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow =xssfSheet.getRow(rowNum);
if (xssfRow == null) {continue;}
Map<String,Object> rowInfo= ExcelImportUtil.getRowInfos(xssfRow);
rowInfo.put("index",(rowNum+1)+"");
data.add(rowInfo);
}
}
}catch (Exception e){
e.printStackTrace();
//自定义异常类
throw new BussException("文件读取失败");
}
return data;
}
public static void main(String[] args) throws Exception {
JdbcTemplate jdbcTemplate= JdbcTemplateUitl.getSysJdbcTemplate();
String sql="SELECT project_name,concat('http://xx.x.x.xx/' ,project_logo) project_logo,project_summary FROM wkrj_bus_project";
List<Map<String,Object>> data= jdbcTemplate.queryForList(sql);
FileOutputStream fileOut = null;
//先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("demo");
sheet1.setDefaultColumnWidth((short)20);
sheet1.setDefaultRowHeight((short)2000);
//画图的顶级管理器,一个sheet只能获取一个(一定要注意这点)
HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
Row row = null;
BufferedImage bufferImg=null;
ByteArrayOutputStream byteArrayOut=null;
for(int i =0;i<100;i++){
try {
row = sheet1.createRow(i);
row.createCell(0).setCellValue(DataHandleUtil.setValue(data.get(i).get("project_name")));
row.createCell(2).setCellValue(DataHandleUtil.setValue(data.get(i).get("project_summary")));
String imgUrl = data.get(i).get("project_logo") + "";
URL url = new URL(imgUrl);
//获取文件后缀名
String suffix = imgUrl.substring(imgUrl.lastIndexOf(".") + 1);
bufferImg = ImageIO.read(url);
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(bufferImg, suffix, byteArrayOut);
//anchor主要用于设置图片的属性
HSSFClientAnchor anchor =
new HSSFClientAnchor
(0, //x缩放
0, // y缩放
1023, //最大1023
255, //最大255
(short) 1, //于下下个参数进行定位 0开始
i, //在第几行
(short) 1, //宽度占几格 0开始
i //第几列
);
// anchor.setAnchorType(3);
//插入图片
patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
}catch (Exception e){
e.printStackTrace();
continue;
}
}
fileOut = new FileOutputStream("D:/测试Excel.xls");
// 写入excel文件
wb.write(fileOut);
System.out.println("----Excel文件已生成------");
}
/**
* 查询一行内单元格 数据
* @param xssfRow
* @return
*/
public static Map<String, Object> getRowInfos(XSSFRow xssfRow) {
Map<String,Object> data=new HashMap<>();
int cellNum=xssfRow.getLastCellNum();
for(int i=1;i<=cellNum;i++){
data.put("cell"+i,getStringValueFromCell(xssfRow.getCell(i-1)).trim());
}
return data;
}
/**
* 查询一行内单元格 数据
* @param hssfRow
* @return
*/
public static Map<String, Object> getRowInfos(HSSFRow hssfRow) {
Map<String,Object> data=new HashMap<>();
int cellNum=hssfRow.getLastCellNum();
for(int i=1;i<=cellNum;i++){
data.put("cell"+i,getStringValueFromCell(hssfRow.getCell(i-1)).trim());
}
return data;
}
/**
* 获取单元格String 值
* @param cell
* @return
*/
public static String getStringValueFromCell(Cell cell) {
SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
DecimalFormat decimalFormat = new DecimalFormat("#.##");
String cellValue = "";
if(cell == null) {
return cellValue;
}
else if(cell.getCellType() == CellType.STRING) {
cellValue = cell.getStringCellValue();
}
else if(cell.getCellType() == CellType.NUMERIC) {
if(HSSFDateUtil.isCellDateFormatted(cell)) {
double d = cell.getNumericCellValue();
Date date = HSSFDateUtil.getJavaDate(d);
cellValue = sFormat.format(date);
}
else {
cellValue = decimalFormat.format((cell.getNumericCellValue()));
}
}
else if(cell.getCellType() == CellType.BLANK) {
cellValue = "";
}
else if(cell.getCellType() ==CellType.BOOLEAN) {
cellValue = String.valueOf(cell.getBooleanCellValue());
}
else if(cell.getCellType() == CellType.ERROR) {
cellValue = "";
}
else if(cell.getCellType() == CellType.FORMULA) {
cellValue = cell.getCellFormula().toString();
}
return cellValue;
}
}