/**
* excel公共类
*
* @author caojx
* @date:2019年4月3日 上午9:44:44
*
*/
public class ExcelUtils {
protected static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
/**
* 写入excel
* @param list
* @param sheetName
* @param file
* @author caojx
* @date 2019年4月4日 上午8:44:14
*/
public static void writeExcel(List<Map<String, String>> list,String sheetName,File file){
try {
OutputStream fos = new FileOutputStream(file);
writeExcel(list, sheetName,fos);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 写入excel->默认样式
* @param list->map key:标题(如果没有可以是表字段) value:值
* @param sheetName
* @param out
* @author caojx
* @date 2019年4月4日 上午8:24:14
*/
public static void writeExcel(List<Map<String, String>> list,String sheetName,OutputStream out){
//定义一个Excel表格
XSSFWorkbook wb = new XSSFWorkbook(); //创建工作薄
XSSFSheet sheet = wb.createSheet(sheetName); //创建工作表
XSSFRow rowTitle = sheet.createRow(0); //行
//添加表头数据
int t = 0;
Set<String> titles = list.get(0).keySet();
for (String key : titles) {
// 将取到的值依次写到Excel的第一行的cell中
XSSFCell cell = rowTitle.createCell(t);
sheet.autoSizeColumn(t);
cell.setCellValue(key);
// 增加边框和自动换行
XSSFCellStyle style = wb.createCellStyle();
Font ztFont = wb.createFont();
ztFont.setFontHeightInPoints((short) 12); // 将字体大小设置为9px
ztFont.setFontName("华文细黑"); // 将“新宋体”字体应用到当前单元格上
ztFont.setBold(true);
style.setFont(ztFont);
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(IndexedColors.YELLOW.index);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
cell.setCellStyle(style);
t++;
}
//添加表数据
for (int i = 0; i < list.size(); i++) {
Map<String, String> map = list.get(i);
XSSFRow row = sheet.createRow(i + 1);
int n = 0;
for(String key : titles){
//将取到的值依次写到Excel的第一行的cell中
XSSFCell cell = row.createCell(n);
sheet.autoSizeColumn(n);
cell.setCellValue(map.get(key));
//增加边框和自动换行
XSSFCellStyle style = wb.createCellStyle();
Font ztFont = wb.createFont();
ztFont.setFontHeightInPoints((short) 11); // 将字体大小设置为9px
ztFont.setFontName("微软雅黑"); // 将“新宋体”字体应用到当前单元格上
style.setFont(ztFont);
style.setWrapText(true);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
cell.setCellStyle(style);
n++;
}
}
try {
wb.write(out);
out.flush();
out.close();
wb.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 写入excel->自定义样式
* @param list->map key:标题(如果没有可以是表字段) value:值
* @param sheetName
* @param out
* @author caojx
* @date 2019年4月4日 上午8:24:14
*/
public static void writeExcel(List<Map<String, String>> list,String sheetName,XSSFCellStyle titleStyle,XSSFCellStyle style,OutputStream out){
//定义一个Excel表格
XSSFWorkbook wb = new XSSFWorkbook(); //创建工作薄
XSSFSheet sheet = wb.createSheet(sheetName); //创建工作表
XSSFRow rowTitle = sheet.createRow(0); //行
//添加表头数据
int t = 0;
Set<String> titles = list.get(0).keySet();
for (String key : titles) {
// 将取到的值依次写到Excel的第一行的cell中
XSSFCell cell = rowTitle.createCell(t);
cell.setCellValue(key);
cell.setCellStyle(titleStyle);
t++;
}
//添加表数据
for (int i = 0; i < list.size(); i++) {
Map<String, String> map = list.get(i);
XSSFRow row = sheet.createRow(i + 1);
int n = 0;
for(String key : titles){
//将取到的值依次写到Excel的第一行的cell中
XSSFCell cell = row.createCell(n);
cell.setCellValue(map.get(key));
cell.setCellStyle(style);
n++;
}
}
try {
wb.write(out);
out.flush();
out.close();
wb.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 写入excel->没有样式
* @param list->map key:标题(如果没有可以是表字段) value:值
* @param sheetName
* @param out
* @author caojx
* @date 2019年4月4日 上午8:24:14
*/
public static void writeExcelNoStyle(List<Map<String, String>> list,String sheetName,OutputStream out){
//定义一个Excel表格
XSSFWorkbook wb = new XSSFWorkbook(); //创建工作薄
XSSFSheet sheet = wb.createSheet(sheetName); //创建工作表
XSSFRow rowTitle = sheet.createRow(0); //行
//添加表头数据
int t = 0;
Set<String> titles = list.get(0).keySet();
for (String key : titles) {
// 将取到的值依次写到Excel的第一行的cell中
XSSFCell cell = rowTitle.createCell(t);
cell.setCellValue(key);
t++;
}
//添加表数据
for (int i = 0; i < list.size(); i++) {
Map<String, String> map = list.get(i);
XSSFRow row = sheet.createRow(i + 1);
int n = 0;
for(String key : titles){
//将取到的值依次写到Excel的第一行的cell中
XSSFCell cell = row.createCell(n);
cell.setCellValue(map.get(key));
n++;
}
}
try {
wb.write(out);
out.flush();
out.close();
wb.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static List<Map<String, String>> readExcel(File file) {
InputStream in = null;
List<Map<String, String>> list = null;
try {
in = new FileInputStream(file);
String fileName = file.getName();
list = readExcel(fileName, in, 0);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
public static List<Map<String, String>> readExcel(File file,String sheetName) {
InputStream in = null;
List<Map<String, String>> list = null;
try {
in = new FileInputStream(file);
String fileName = file.getName();
list = readExcel(fileName, in, sheetName);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
public static List<Map<String, String>> readExcel(String fileName, InputStream in) {
return readExcel(fileName, in, 0);
}
public static List<Map<String, String>> readExcel(String fileName, InputStream in, int sheetNum) {
// map-key:表头;map-value:表值
List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
Workbook workbook = null;
FormulaEvaluator formulaEvaluator = null;
try {
// 获得工作簿
if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(in);
formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
} else if (fileName.endsWith(".xls")) {
workbook = new HSSFWorkbook(in);
formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
} else {
logger.error("文件类型错误!");
return null;
}
// // 获得工作表个数
// int sheetCount = workbook.getNumberOfSheets();
// // 遍历工作表
// for (int i = 0; i < sheetCount; i++) {
Sheet sheet = workbook.getSheetAt(sheetNum);
// 获得行数
int rows = sheet.getLastRowNum() + 1;
// 获得列数,先获得一行,在得到改行列数
Row tmp = sheet.getRow(0);
if (tmp == null) {
return null;
}
// 根据表头获取列数
int cols = tmp.getPhysicalNumberOfCells();
// 读取数据
for (int row = 1; row < rows; row++) {
Row r = sheet.getRow(row);
// int fcols = r.getFirstCellNum();
// int lcols = r.getLastCellNum();
Map<String, String> map = new LinkedHashMap<String, String>();
for (int col = 0; col < cols; col++) {
Cell cell = r.getCell(col);
// 设置单元格类型
if (cell != null) {
String key = getCellValueFormula(sheet.getRow(0).getCell(col), formulaEvaluator);
String value = getCellValueFormula(cell, formulaEvaluator);
map.put(key, value);
}
}
mapList.add(map);
}
// }
workbook.close();
in.close();
return mapList;
} catch (EncryptedDocumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static List<Map<String, String>> readExcel(String fileName, InputStream in,String sheetName) {
// map-key:表头;map-value:表值
List<Map<String, String>> mapList = new ArrayList<Map<String, String>>();
Workbook workbook = null;
FormulaEvaluator formulaEvaluator = null;
try {
// 获得工作簿
if (fileName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(in);
formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
} else if(fileName.endsWith(".xls")){
workbook = new HSSFWorkbook(in);
formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
}else{
logger.error("文件类型错误!");
return null;
}
// // 获得工作表个数
// int sheetCount = workbook.getNumberOfSheets();
// // 遍历工作表
// for (int i = 0; i < sheetCount; i++) {
Sheet sheet = workbook.getSheet(sheetName);
// 获得行数
int rows = sheet.getLastRowNum() + 1;
// 获得列数,先获得一行,在得到改行列数
Row tmp = sheet.getRow(0);
if (tmp == null) {
return null;
}
//根据表头获取列数
int cols = tmp.getPhysicalNumberOfCells();
// 读取数据
for (int row = 1; row < rows; row++) {
Row r = sheet.getRow(row);
// int fcols = r.getFirstCellNum();
// int lcols = r.getLastCellNum();
Map<String, String> map = new LinkedHashMap<String, String>();
for (int col = 0; col < cols; col++) {
Cell cell = r.getCell(col);
// 设置单元格类型
if (cell != null) {
String key = getCellValueFormula(sheet.getRow(0).getCell(col), formulaEvaluator);
String value = getCellValueFormula(cell, formulaEvaluator);
map.put(key, value);
}
}
mapList.add(map);
}
// }
workbook.close();
in.close();
return mapList;
} catch (EncryptedDocumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
private static String getCellValueFormula(Cell cell, FormulaEvaluator formulaEvaluator) {
if (cell == null || formulaEvaluator == null) {
return null;
}
if (cell.getCellType() == CellType.FORMULA) {
// cell.setCellType(CellType.STRING);
return getCellValue(formulaEvaluator.evaluate(cell));
}
return getStringValue(cell);
}
private static String getCellValue(CellValue cell) {
switch (cell.getCellType()) {
case BOOLEAN:
return cell.getBooleanValue() ? "TRUE" : "FALSE";
case FORMULA:
return cell.getStringValue();
case NUMERIC:
return String.valueOf((int) cell.getNumberValue());
case STRING:
return cell.getStringValue();
default:
return "";
}
}
private static String getStringValue(Cell cell) {
switch (cell.getCellType()) {
case BOOLEAN:
return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
case FORMULA:
cell.setCellType(CellType.STRING);
return cell.getCellFormula();
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
return sdf.format(date);
} else {
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
}
case STRING:
return cell.getStringCellValue();
default:
return "";
}
}
}