java导入excel里图片到数据库 java excel导出图片

不多说，直接上代码 poi 3.9 HSSFWorkbook book = new HSSFWorkbook(); 这个book.close();不支持

import java.io.ByteArrayOutputStream;
 import java.io.FileOutputStream;
 import java.io.IOException;
 import java.io.InputStream;
 import java.math.BigDecimal;
 import java.math.RoundingMode;
 import java.net.HttpURLConnection;
 import java.net.URL;
 import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Date;
 import java.util.List;import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
 import org.apache.poi.hssf.usermodel.HSSFPatriarch;
 import org.apache.poi.hssf.usermodel.HSSFRow;
 import org.apache.poi.hssf.usermodel.HSSFSheet;
 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 import org.apache.poi.ss.usermodel.Cell;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;import com.sjdf.erp.common.constant.CommonPlatformConstant;
 import com.sjdf.erp.common.utils.PlatformUtils;public class ExcelUntilTest {

private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUntilTest.class);

public static final String XLSX = ".xlsx";
public static final String XLS=".xls";

/**
 * Excel导出
 * @param title 导出Excel文件名称
 * @param rowList 第一个List为表头，其余行为表数据
 * @param resp HttpServletResponse 对象
 * @throws IOException
 */
public static void writeExcel(String title,List<List<Object>> rowList,HttpServletResponse resp) throws IOException{
    if (resp == null) {
        throw new NullPointerException("the HttpServletResponse is null");
    }
    HSSFWorkbook book = warpSingleWorkbook(title, rowList, false);
    // 响应客户端
    String filename = new String(title.getBytes("UTF-8"), "ISO-8859-1");
    resp.reset();
    resp.setHeader("Content-disposition", "attachment; filename=" + filename +XLS);
    resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
    // 输出Excel文件
    book.write(resp.getOutputStream());
    book.close();
}

/**
 * Excel导出设置Workbook
 * @param title 导出Excel文件名称
 * @param rowList 第一个List为表头，其余行为表数据
 * @param downLoadPic 是否下载图片
 * @throws IOException
 */
public static HSSFWorkbook warpSingleWorkbook(String title,List<List<Object>> rowList, Boolean downLoadPic) throws IOException {
    String filename = title;
    if (!PlatformUtils.hasText(title)) {
        filename = new SimpleDateFormat("yyMMddHHmmss").format(new Date());
    }
    if (rowList == null || rowList.isEmpty()) {
        throw new NullPointerException("the row list is null");
    }
    HSSFWorkbook book = new HSSFWorkbook();
    // 创建表
    HSSFSheet sheet = book.createSheet(filename);
    // 设置单元格默认宽度为15个字符
    sheet.setDefaultColumnWidth(15);
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    // 设置表头样式
    HSSFCellStyle style = book.createCellStyle();
    // 设置居左
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    // 检测表头数据（表头不允许数据为空）
    List<Object> head = rowList.get(0);
    for (Object key : head) {
        if (!PlatformUtils.hasText(key.toString())) {
            book.close();
            throw new NullPointerException("there is a blank exist head row");
        }
    }
    // 写数据
    int size = rowList.get(0).size();
    for (int i = 0; i < rowList.size(); i++) {
        List<Object> row = rowList.get(i);
        if (row == null || row.isEmpty()) {
            book.close();
            throw new NullPointerException("the "+(i+1)+"th row is null");
        }
        if (size != row.size()) {
            book.close();
            throw new IllegalArgumentException("the cell number of "+(i+1)+"th row is different form the first");
        }
        HSSFRow sr = sheet.createRow(i);
        for (int j = 0; j < row.size(); j++) {
            if (downLoadPic && i > 0 && j == 0) {
                sr.setHeight((short) (800));
                drawPictureInfoExcel(book, patriarch, i, row.get(0).toString());
            } else {
                setExcelValue(sr.createCell(j), row.get(j), style);
            }
        }
    }
    return book;
}

public static void main(String[] args) {
    FileOutputStream fileOut = null;     
   try {  
       String picUrl1 = "http://b-ssl.duitang.com/uploads/item/201502/17/20150217161549_C4K8L.jpeg";
       String picUrl2 = "http://b-ssl.duitang.com/uploads/item/201512/10/20151210135838_Y2SvK.jpeg";
       String picUrl3 = "http://img5.duitang.com/uploads/item/201407/23/20140723175802_LHCJU.jpeg";
       List<List<Object>> rowList = new ArrayList<List<Object>>();
       rowList.add(Arrays.asList("图片", "名称", "sku"));
       rowList.add(Arrays.asList(picUrl1, "名称1", "sku01"));
       rowList.add(Arrays.asList(picUrl2, "名称2", "sku02"));
       rowList.add(Arrays.asList(picUrl3, "名称3", "sku03"));
       HSSFWorkbook wb = warpSingleWorkbook("test", rowList, false);
       fileOut = new FileOutputStream("D:/测试Excel.xls");     
       // 写入excel文件     
        wb.write(fileOut);     
        System.out.println("----Excle文件已生成------");  
   } catch (Exception e) {  
       e.printStackTrace();  
   }finally{  
       if(fileOut != null){
            try {  
               fileOut.close();  
           } catch (IOException e) {  
               e.printStackTrace();  
           }  
       } 
   }  }

private static void drawPictureInfoExcel(HSSFWorkbook wb,HSSFPatriarch patriarch,int rowIndex,String pictureUrl){
    //rowIndex代表当前行
    try {
        
        if(PlatformUtils.hasText(pictureUrl)) {
            URL url = new URL(pictureUrl);  
            //打开链接  
            HttpURLConnection conn = (HttpURLConnection)url.openConnection();  
            //设置请求方式为"GET"  
            conn.setRequestMethod("GET");  
            //超时响应时间为5秒  
            conn.setConnectTimeout(5 * 1000);  
            //通过输入流获取图片数据  
            InputStream inStream = conn.getInputStream();  
            //得到图片的二进制数据，以二进制封装得到数据，具有通用性  
            byte[] data = readInputStream(inStream);  
            //anchor主要用于设置图片的属性  
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 250,(short) 0, rowIndex, (short) 0, rowIndex);
            //Sets the anchor type （图片在单元格的位置）
            //0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells.
            anchor.setAnchorType(3);
            patriarch.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));   
        }
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

private static byte[] readInputStream(InputStream inStream) throws Exception{  
    ByteArrayOutputStream outStream = new ByteArrayOutputStream();  
    //创建一个Buffer字符串  
    byte[] buffer = new byte[1024];  
    //每次读取的字符串长度，如果为-1，代表全部读取完毕  
    int len = 0;  
    //使用一个输入流从buffer里把数据读取出来  
    while( (len=inStream.read(buffer)) != -1 ){  
        //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度  
        outStream.write(buffer, 0, len);  
    }  
    //关闭输入流  
    inStream.close();  
    //把outStream里的数据写入内存  
    return outStream.toByteArray();  
}

/**
 * 设置Excel浮点数可做金额等数据统计
 * @param cell 单元格类
 * @param value 传入的值
 */
public static void setExcelValue(HSSFCell cell, Object value, HSSFCellStyle style){
    // 写数据
    if (value == null) {
        cell.setCellValue("");
    }else {
        if (value instanceof Integer || value instanceof Long) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(Long.valueOf(value.toString()));
        } else if (value instanceof BigDecimal) {
            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
            cell.setCellValue(((BigDecimal)value).setScale(CommonPlatformConstant.INT_3, RoundingMode.HALF_UP).doubleValue());
        } else {
            cell.setCellValue(value.toString());
        }
        cell.setCellStyle(style);
    }
}

// 参数解析

HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 250,(short) 0, rowIndex, (short) 0, rowIndex)
 Parameters:
 dx1 - the x coordinate within the first cell.//定义了图片在第一个cell内的偏移x坐标，既左上角所在cell的偏移x坐标，一般可设0
 dy1 - the y coordinate within the first cell.//定义了图片在第一个cell的偏移y坐标，既左上角所在cell的偏移y坐标，一般可设0
 dx2 - the x coordinate within the second cell.//定义了图片在第二个cell的偏移x坐标，既右下角所在cell的偏移x坐标，一般可设0
 dy2 - the y coordinate within the second cell.//定义了图片在第二个cell的偏移y坐标，既右下角所在cell的偏移y坐标，一般可设0
 col1 - the column (0 based) of the first cell.//第一个cell所在列，既图片左上角所在列
 row1 - the row (0 based) of the first cell.//图片左上角所在行
 col2 - the column (0 based) of the second cell.//图片右下角所在列
 row2 - the row (0 based) of the second cell.//图片右下角所在行java.math.BigDecimal
 是java.math包中提供的类，用来对超过16位有效位的数进行精确的运算。
 new BigDecimal(entitySymbol.decimalPoint).setScale(0, RoundingMode.HALF_UP).intValue();
 .setScale(0, RoundingMode.HALF_UP);//第一个参数是：保留多少位小数，第二个是四舍五入。
 .intValue();//是将BigDecimal转为Int类型。
 EG：BigDecimal.setScale();//用于格式化小数点
 setScale(1);//表示保留以为小数，默认用四舍五入方式
 setScale(1,BigDecimal.ROUND_DOWN);//直接删除多余的小数位，如2.35会变成2.3
 setScale(1,BigDecimal.ROUND_UP);//进位处理，2.35变成2.4
 setScale(1,BigDecimal.ROUND_HALF_UP);//四舍五入，2.35变成2.4
 setScaler(1,BigDecimal.ROUND_HALF_DOWN);//四舍五入，2.35变成2.3，如果是5则向下舍

Apache POI读取excel没有关闭文件流，导致共享冲突
类org.apache.poi.xssf.usermodel.XSSFWorkbook

提供了下面这个构造函数：

public XSSFWorkbook(String path) throws IOException {
    this(openPackage(path));
}

直接传入excel文件全路径，但是无法关闭文件流，没有提供类似close()，dispose()的方法，操作完之后文件即被锁定，无法编辑或删除，如果此用法可以释放文件资源还请知道人告诉我一下，我目前没找到。
建议使用以下方法构造：

input = new FileInputStream(new File(path));
workbook = new XSSFWorkbook(new BufferedInputStream(input));

使用trycatch，在finally中执行input.close()即可释放文件