java导出excel poi jxl_Java Excel导出方法（POI或jxl）

1.使用POI组件

/**

* 导出excel

* @param wb

* @param sheetname

* @param head

* @param List> dataList

*/

private static void createSheetForBaseData(XSSFWorkbook wb,

String sheetname, Map head, List> dataList) {

XSSFSheet sheet = wb.createSheet(sheetname);

XSSFRow headRow = sheet.createRow(0);

List key=new ArrayList();

List value=new ArrayList();

for (Map.Entry entry:head.entrySet()) {

key.add(entry.getKey());

value.add(entry.getValue());

}

for (int i = 0; i < value.size() ;i++) {

XSSFCell cell = headRow.createCell(i);

cell.setCellValue(value.get(i).toString());

}

try {

for (int j = 0; j < dataList.size(); j++) {

XSSFRow row = sheet.createRow(j + 1);

for (int i = 0; i < key.size(); i++) {

Object val = dataList.get(j).get(key.get(i));

val = val == null ? "-" : val;

XSSFCell cell = row.createCell(i);

// cell.setCellStyle(style2);

cell.setCellValue(val.toString());

}

}

} catch (Exception e) {

e.printStackTrace();

}

sheet.protectSheet("123456");//设置工作表保护密码

}

2.jxl组件(加文字水印)

private static void createSheetForBaseDataWaterMark(HttpServletResponse response,

String sheetname, Map head, List> dataList, Map mapWaterMark) {

WritableWorkbook workbook = null;

OutputStream os =null;

FileInputStream fis = null;

try {

os = response.getOutputStream();

// 取得输出流

response.setCharacterEncoding("UTF-8");

response.setHeader("Content-Disposition", "attachment; filename="

+ new String(sheetname.getBytes("gb2312"), "iso8859-1")+".xls");//fileName为下载时用户看到的文件名利用jxl 将数据从后台导出为excel

response.setHeader("Content-Type", "application/msexcel");

workbook = Workbook.createWorkbook(os);

//创建一个sheet

WritableSheet sheet = workbook.createSheet(sheetname, 0);

List key=new ArrayList();

List value=new ArrayList();

for (Map.Entry entry:head.entrySet()) {

key.add(entry.getKey());

value.add(entry.getValue());

}

//第一行

for (int i = 0; i < value.size() ;i++) {

Label label = new Label(i, 0, value.get(i).toString());

sheet.addCell(label);

}

for (int j = 0; j < dataList.size(); j++) {

for (int i = 0; i < key.size(); i++) {

Object val = dataList.get(j).get(key.get(i));

val = val == null ? "-" : val;

Label label = new Label(i, j+1, val.toString());

sheet.addCell(label);

}

}

//水印图片文件

File fileImg = createWaterMark(mapWaterMark);

byte imageData[] = new byte[(int) fileImg.length()];

fis = new FileInputStream(fileImg);

fis.read(imageData);

sheet.setWaterMarkImage(imageData, 480, 1020);

//设置密码保护

sheet.setProtected(true);

SheetSettings settings = sheet.getSettings();

settings.setPassword(UUID.randomUUID().toString());

workbook.write();

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

workbook.close();

os.close();

fis.close();

} catch (WriteException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

}

}

/**

* 生成水印图片

*

* @param mapWaterMark

* @return

* @throws IOException

*/

public static File createWaterMark(Map mapWaterMark) throws IOException {

int width = 480; // 水印图片的宽度

int height = 1020; // 水印图片的高度 因为设置其他的高度会有黑线，所以拉高高度

File watermarkFileName = new File(System.getProperty("user.dir") + "/" + System.currentTimeMillis() + ".bmp");

// 获取bufferedImage对象

BufferedImage bi = new BufferedImage(width, height,BufferedImage.TYPE_INT_RGB);

// 处理背景色，设置为 白色

int minx = bi.getMinX();

int miny = bi.getMinY();

for (int i = minx; i < width; i++) {

for (int j = miny; j < height; j++) {

bi.setRGB(i, j, 0xffffff);

}

}

// 获取Graphics2d对象

Graphics2D g2d = bi.createGraphics();

// 设置字体颜色为灰色

g2d.setColor(Color.LIGHT_GRAY);

// 设置图片的属性

g2d.setStroke(new BasicStroke(1));

// 设置字体

g2d.setFont(new java.awt.Font("Serif", Font.ITALIC, 20));

// 设置字体倾斜度

g2d.rotate(Math.toRadians(-10));

// 写入水印文字 原定高度过小，所以累计写水印，增加高度

Object[] arrayWaterMark = mapWaterMark.keySet().toArray();

for (int i = 0; i < mapWaterMark.size(); i++) {

//水印距离顶部高度120

g2d.drawString(arrayWaterMark[i] + "：" + mapWaterMark.get(arrayWaterMark[i]), 0, 120 + 40 * (i+1));

}

// 设置透明度

g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));

// 释放对象

g2d.dispose();

// 通过bmp写入文件

BMPEncoder.write(bi, watermarkFileName);

return watermarkFileName;

}