Java中导入/导出excel,导出pdf报表信息
1.项目中经常需要用到报表生成,信息导入数据库的功能.主要有以下几种.
2.其中比较简单的是 外部数据无需处理直接 导入数据库中,这种比较简单.直接利用Navicat数据库工具 导入外部.示例如下
1.准备customer.xlsx文件信息
2.使用导入向导,选择导入文件的xlsx文件类型
3.选择导入数据源及相关表位置.
4.选择导入xlsx数据源位置,从第几行开始导入,字段名行的相对行位置.
5.选择导入数据 对应表字段相关信息
6.选择导入模式,此处我使用的是 记录添加模式
7.开始添加 ,影像了几条记录
8.记录添加成功
3.根据需要的信息 生成导出excel表格,具体代码示例如下
package com.bxd.app.util;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import com.bxd.app.view.biz.ExportExcelView;
public class ExportExcelUtil {
/**
* 创建表格标题
*
* @param wb
* Excel文档对象
* @param sheet
* 工作表对象
* @param headString
* 标题名称
* @param col
* 标题占用列数
*/
@SuppressWarnings("deprecation")
public static void createHeadTittle(HSSFWorkbook wb, HSSFSheet sheet, String headString, int col) {
HSSFRow row = sheet.createRow(0); // 创建Excel工作表的行
HSSFCell cell = row.createCell(0); // 创建Excel工作表指定行的单元格
row.setHeight((short) 1000); // 设置高度
cell.setCellType(HSSFCell.ENCODING_UTF_16); // 定义单元格为字符串类型
cell.setCellValue(new HSSFRichTextString(headString));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col)); // 指定标题合并区域
// 定义单元格格式，添加单元格表样式，并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 指定单元格垂直居中个对齐
cellStyle.setWrapText(true); // 指定单元格自动换行
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 16); // 字体大小
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
/**
* 创建表头
*
* @param wb
* Excel文档对象
* @param sheet
* 工作表对象
* @param thead
* 表头内容
* @param sheetWidth
* 每一列宽度
*/
@SuppressWarnings("deprecation")
public static void createThead(HSSFWorkbook wb, HSSFSheet sheet, String[] thead, int[] sheetWidth) {
HSSFRow row1 = sheet.createRow(1);
row1.setHeight((short) 600);
// 定义单元格格式，添加单元格表样式，并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setWrapText(true);
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 设置背景色
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 设置右边框类型
cellStyle.setRightBorderColor(HSSFColor.BLACK.index); // 设置右边框颜色
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
// 设置表头内容
for (int i = 0; i < thead.length; i++) {
HSSFCell cell1 = row1.createCell(i);
cell1.setCellType(HSSFCell.ENCODING_UTF_16);
cell1.setCellValue(new HSSFRichTextString(thead[i]));
cell1.setCellStyle(cellStyle);
}
// 设置每一列宽度
for (int i = 0; i < sheetWidth.length; i++) {
sheet.setColumnWidth(i, sheetWidth[i]);
}
}
/**
* 填入数据
*
* @param wb
* // Excel文档对象
* @param sheet
* // 工作表对象
* @param result
* // 表数据
*/
@SuppressWarnings("deprecation")
public static void createTable(HSSFWorkbook wb, HSSFSheet sheet, List<LinkedHashMap<String, String>> result) {
// 定义单元格格式，添加单元格表样式，并添加到工作薄
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);
// 单元格字体
HSSFFont font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
// 循环插入数据
for (int i = 0; i < result.size(); i++) {
HSSFRow row = sheet.createRow(i + 2);
row.setHeight((short) 400); // 设置高度
HSSFCell cell = null;
int j = 0;
for (String key : (result.get(i).keySet())) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
cell.setCellValue(new HSSFRichTextString(result.get(i).get(key)));
j++;
}
}
}
public static void main(String[] args) {
//测试hashmap treemap linkedhashmap之间的顺序
/*Map<String, String> map=new HashMap<>();
System.out.println("hashmap排序");
add_keyvalue(map);
TreeMap<String, String> map2=new TreeMap<>();
System.out.println("treemap排序");
add_keyvalue(map2);
LinkedHashMap<String, String> map3=new LinkedHashMap<>();
System.out.println("linkedhash排序");
add_keyvalue(map3);*/
// 1.封装数据
List<ExportExcelView> list = new LinkedList<>();
ExportExcelView b1 = new ExportExcelView();
b1.setDeclsno("201810251706470169854601");
b1.setDecdt("2018-09-22");
b1.setEleacno("1209394999");
b1.setCustName("张三");
b1.setEntName("正信广电");
b1.setSaleName("郭启铭");
b1.setSaleTel("17342064227");
b1.setRealsumretbal("1000");
b1.setDecutionFee("100");
ExportExcelView b2 = new ExportExcelView();
b2.setDeclsno("201810251706470176052618");
b2.setDecdt("2018-09-22");
b2.setEleacno("1209394999");
b2.setCustName("赵四");
b2.setEntName("正信广电");
b2.setSaleName("郭启铭");
b2.setSaleTel("17342064227");
b2.setRealsumretbal("2000");
b2.setDecutionFee("200");
list.add(b1);
list.add(b2);
// 实体类转换为map
List<LinkedHashMap<String, String>> result = new ArrayList<>();
LinkedHashMap<String, String> map = new LinkedHashMap<>();
for (ExportExcelView e : list) {
map.put("declsno", e.getDeclsno());
map.put("decdt", e.getDecdt());
map.put("eleacno", e.getEleacno());
map.put("custName",e.getCustName());
map.put("entName",e.getEntName());
map.put("saleName",e.getSaleName());
map.put("saleTel",e.getSaleTel());
map.put("realsumretbal",e.getRealsumretbal());
map.put("decutionFee",e.getDecutionFee());
result.add(map);
}
// 2.定义变量值 创建Excel文件
String fileName = "正信广电_201809代扣费用表.xls"; // 定义文件名
String headString = "正信广电_201809代扣费用表"; // 定义表格标题
String sheetName = "正信广电_201809代扣费用表"; // 定义工作表表名
String filePath = "D:\\"; // 文件本地保存路径
String[] thead = { "扣款流水", "扣款日期", "发电户号", "用户姓名", "开发商",
"业务员姓名","业务员手机号","扣款金额(元)", "代扣费用(元)" };
int[] sheetWidth = { 7500, 4000, 3000, 3000, 4000, 3000, 5000, 5000,5000}; // 定义每一列宽度
HSSFWorkbook wb = new HSSFWorkbook(); // 创建Excel文档对象
HSSFSheet sheet = wb.createSheet(sheetName); // 创建工作表
// 3.生成表格
// ①创建表格标题
createHeadTittle(wb, sheet, headString, 8);
// result.get(0).size() - 1为表格占用列数，从0开始
// ②创建表头
createThead(wb, sheet, thead, sheetWidth);
// ③填入数据
createTable(wb, sheet, result);
FileOutputStream fos;
try {
fos = new FileOutputStream(new File(filePath + fileName));
wb.write(fos);
fos.close();
wb.close();
System.out.println("导出excel成功");
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
}
}
public static void add_keyvalue(Map<String, String> map){
map.put("351", "11");
map.put("512", "222");
map.put("853", "333");
map.put("125", "333");
map.put("341", "333");
Iterator<String> iterator=map.keySet().iterator();
while(iterator.hasNext()){
System.out.println(iterator.next());
}
}
}
2.导出pdf示例,将多张图片合成pdf文件 生成到指定位置
package com.bxd.app.util;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import javax.imageio.ImageIO;
import com.bxd.core.util.FileUtil;
import com.lowagie.text.BadElementException;
import com.lowagie.text.Document;
import com.lowagie.text.DocumentException;
import com.lowagie.text.Image;
import com.lowagie.text.Rectangle;
import com.lowagie.text.pdf.PdfWriter;
class PrintToPdfUtil {
/**
*
* @param imageFolderPath
* 图片文件夹地址
* @param pdfPath
* PDF文件保存地址
*
*/
public static void toPdf(String imageFolderPath, String pdfPath) {
try {
// 图片文件夹地址
// String imageFolderPath = "D:/Demo/ceshi/";
// 图片地址
String imagePath = null;
// PDF文件保存地址
// String pdfPath = "D:/Demo/ceshi/hebing.pdf";
// 输入流
FileOutputStream fos = new FileOutputStream(pdfPath);
// 创建文档
Document doc = new Document(null, 0, 0, 0, 0);
// doc.open();
// 写入PDF文档
PdfWriter.getInstance(doc, fos);
// 读取图片流
BufferedImage img = null;
// 实例化图片
Image image = null;
// 获取图片文件夹对象
File file = new File(imageFolderPath);
File[] files = file.listFiles();
// 循环获取图片文件夹内的图片
for (File file1 : files) {
if (file1.getName().endsWith(".png") || file1.getName().endsWith(".jpg")
|| file1.getName().endsWith(".gif") || file1.getName().endsWith(".jpeg")
|| file1.getName().endsWith(".tif")) {
// System.out.println(file1.getName());
imagePath = imageFolderPath + file1.getName();
// 读取图片流
img = ImageIO.read(new File(imagePath));
// 根据图片大小设置文档大小
doc.setPageSize(new Rectangle(img.getWidth(), img.getHeight()));
// 实例化图片
image = Image.getInstance(imagePath);
// 添加图片到文档
doc.open();
doc.add(image);
}
}
// 关闭文档
doc.close();
} catch (IOException e) {
e.printStackTrace();
} catch (BadElementException e) {
e.printStackTrace();
} catch (DocumentException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
long time1 = System.currentTimeMillis();
toPdf("C:/2018-11-14/",
"C:/hebing.pdf");
long time2 = System.currentTimeMillis();
int time = (int) ((time2 - time1) / 1000);
System.out.println("执行了：" + time + "秒！");
}
}
3.导入excel文件信息
package com.bxd.app.util;
import com.bxd.app.dao.biz.BdCustomerDao;
import com.bxd.app.entity.BdCustomer;
import com.bxd.core.util.BeanUtil;
import com.bxd.core.util.IdcardValidator;
import com.bxd.core.util.MD5;
import com.bxd.core.util.StringUtil;
import com.bxd.core.util.TelValidatorUtil;
import com.bxd.core.util.TextFormater;
import com.bxd.core.util.UUIDGenerator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
@Component
public class ImportCustomerUtil {
private static Logger logger =LoggerFactory.getLogger(ImportCustomerUtil.class);
@Autowired
public BdCustomerDao bdCustomerDao;
/**
* 生成需要的数据
*
* @param passwd
* 密码
* @param filePath
* 文件路径
* @param columns
* 列数据
* @param type
* 01 只需要注册的 02 代表 注册和实名都需要的弄的数据
* @return
*/
@SuppressWarnings("unused")
public static List<BdCustomer> import_excel(String passwd, String filePath, String columns[], String type) {
Workbook wb = null;
Sheet sheet = null;
Row row = null;
List<Map<String, String>> list = null;
String cellData = null;
wb = readExcel(filePath);
if (wb != null) {
// 用来存放表中数据
list = new ArrayList<Map<String, String>>();
// 获取第一个sheet
sheet = wb.getSheetAt(0);
// 获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
// 获取第一行
row = sheet.getRow(0);
// 获取最大列数
int colnum = row.getPhysicalNumberOfCells();
for (int i = 1; i < rownum; i++) {
Map<String, String> map = new LinkedHashMap<String, String>();
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < colnum; j++) {
cellData = (String) getCellFormatValue(row.getCell(j));
map.put(columns[j], cellData);
}
} else {
break;
}
list.add(map);
}
}
// 遍历解析出来的list
logger.info("*******excel读取出来的数量：" + list.size() + "*****");
List<BdCustomer> excel_result = new LinkedList<BdCustomer>();
for (Map<String, String> map : list) {
for (Entry<String, String> entry : map.entrySet()) {
// logger.info(entry.getKey() + ":" + entry.getValue() + ",");
}
BdCustomer bdCustomer = BeanUtil.toBean(map, BdCustomer.class);
excel_result.add(bdCustomer);// 存储到list集合中
}
List<BdCustomer> result = new LinkedList<BdCustomer>();
int no_register=0;
if (type.equals("01")) {//只需要注册的用户数据
for (int i = 0; i < excel_result.size(); i++) {
// 手机号码格式不正确 不能注册的用户信息及数量
if (!TelValidatorUtil.mobileValidator(excel_result.get(i).getTelphone())) {
logger.info("姓名:"+excel_result.get(i).getCustName()+","+
excel_result.get(i).getTelphone()+"手机号格式不正确");
no_register++;
continue;
}
//手机号码格式正确 同时 客户姓名或者 身份证号不正确
if (TelValidatorUtil.mobileValidator(excel_result.get(i).getTelphone())
&& (StringUtil.isEmpty(excel_result.get(i).getCustName())
||excel_result.get(i).getCustName().indexOf("*")!=-1
||!"".equals( IdcardValidator.Validate(excel_result.get(i).getIdNo())))) {
try {
// 对用户信息进行加密
BdCustomer bdCustomer = new BdCustomer();
bdCustomer.setCustNo("U" + UUIDGenerator.nextSerial());// 如果手机号不为空,则设置用户编号
bdCustomer.setCustAcct(DesensitizationUtil.encrypt_private(excel_result.get(i).getTelphone()));
bdCustomer.setTelphone(DesensitizationUtil.encrypt_private(excel_result.get(i).getTelphone()));
bdCustomer.setPasswd(MD5.crypt(passwd));
bdCustomer.setRegTime(TextFormater.format(new Date(), "yyyyMMdd"));
bdCustomer.setIsBind("0");
bdCustomer.setIsLocked("0");
bdCustomer.setSources("1");
bdCustomer.setCustName("");
bdCustomer.setIdType("01");
result.add(bdCustomer);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
logger.info("无法注册的用户数量:"+no_register);
} else {//需要注册和实名的用户数据为
for (int i = 0; i < excel_result.size(); i++) {
// 手机号正确,姓名及 身份证号格式正确
if (TelValidatorUtil.mobileValidator(excel_result.get(i).getTelphone())
&& StringUtil.isNotEmpty(excel_result.get(i).getCustName())
&&excel_result.get(i).getCustName().indexOf("*")==-1
&&"".equals(IdcardValidator.Validate(excel_result.get(i).getIdNo()))
) {
try {
// 对用户信息进行加密
BdCustomer bdCustomer = new BdCustomer();
//实名认证所需填写信息
bdCustomer.setCustName(DesensitizationUtil.encrypt_private(excel_result.get(i).getCustName()));
bdCustomer.setIsVerified("01");
bdCustomer.setVerifiedTime(TextFormater.format(Calendar.getInstance().getTime(), "yyyyMMdd"));
bdCustomer.setCustAge(IdNOToAge(excel_result.get(i).getIdNo()));//根据身份证号计算年龄
bdCustomer.setIdNo(DesensitizationUtil.encrypt_private(excel_result.get(i).getIdNo()));
bdCustomer.setSources("1");
bdCustomer.setCustNo("U" + UUIDGenerator.nextSerial());// 如果手机号不为空,则设置用户编号
bdCustomer.setCustAcct(DesensitizationUtil.encrypt_private(excel_result.get(i).getTelphone()));
bdCustomer.setTelphone(DesensitizationUtil.encrypt_private(excel_result.get(i).getTelphone()));
bdCustomer.setPasswd(MD5.crypt(passwd));
bdCustomer.setRegTime(TextFormater.format(new Date(), "yyyyMMdd"));
bdCustomer.setIsBind("0");
bdCustomer.setIsLocked("0");
bdCustomer.setIdType("01");
result.add(bdCustomer);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
}
return result;
}
public static void main(String[] args) {
String passwd = "666666d";// 导入用户密码
String filepath = "C:\\test.xlsx";// 文件路径地址
String columns[] = { "custName", "roofArea", "idNo", "homeAddress", "telphone" };
List<BdCustomer> result = import_excel(passwd, filepath, columns, "01");
System.out.println(TelValidatorUtil.mobileValidator(""));
}
/**
* 根据身份证号计算年龄
* @param IdNO
* @return
*/
private static int IdNOToAge(String IdNO){
Integer birthyear=Integer.parseInt(IdNO.substring(6, 10));//出生年月
Integer year=Integer.parseInt(TextFormater.format(Calendar.getInstance().getTime(),"yyyy"));//
return year-birthyear;
}
// 读取excel
@SuppressWarnings("resource")
public static Workbook readExcel(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
return wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return wb = new XSSFWorkbook(is);
} else {
return wb = null;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
@SuppressWarnings("deprecation")
public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
// 判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
// 判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
// 转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
} else {
// 数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
}
}