package com.pflm.shakeExternal.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.format.CellFormatType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class ExcelUtil {
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
/**
* 创建excel文档，
* @param list 数据
* @param keys list中map的key数组集合
* @param columnNames excel的列名
* */
public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) {
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
// 创建第一个sheet（页），并命名
Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
for(int i=0;i<keys.length;i++){
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
// 创建第一行
Row row = sheet.createRow((short) 0);
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
// 创建第一种字体样式（用于列名）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式（用于值）
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// Font f3=wb.createFont();
// f3.setFontHeightInPoints((short) 10);
// f3.setColor(IndexedColors.RED.getIndex());
// 设置第一种单元格的样式（用于列名）
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
// 设置第二种单元格的样式（用于值）
cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_CENTER);
//设置列名
for(int i=0;i<columnNames.length;i++){
Cell cell = row.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
//设置每行每列的值
for (short i = 1; i < list.size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow((short) i);
// 在row行上创建一个方格
for(short j=0;j<keys.length;j++){
Cell cell = row1.createCell(j);
cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
cell.setCellStyle(cs2);
}
}
return wb;
}
/**
* 得去excel （支持加密文件，但是对xls文件，xlsx文件不支持）
* @param f
* @param password
* @return
*/
@SuppressWarnings("resource")
public static List<String> readExcel(File f,String password){
try{
String fileName = f.getName();
String type = fileName.substring(fileName.indexOf(".")+1);
if(XLS.equals(type)){
HSSFWorkbook hwb = null;
Sheet sheet = null;
if(password!=null && !"".equals(password)){
Biff8EncryptionKey.setCurrentUserPassword(password);
NPOIFSFileSystem fs = new NPOIFSFileSystem(f, true);
hwb = new HSSFWorkbook(fs);
Biff8EncryptionKey.setCurrentUserPassword(null);
}else{
hwb = new HSSFWorkbook(new FileInputStream(f));
}
hwb = new HSSFWorkbook(new FileInputStream(f));
sheet = hwb.getSheetAt(0);
return readCell(sheet);
}else{
NPOIFSFileSystem fs = new NPOIFSFileSystem(f, true);
EncryptionInfo info = new EncryptionInfo(fs);
Decryptor decryptor = Decryptor.getInstance(info);
decryptor.verifyPassword(password);
InputStream is = decryptor.getDataStream(fs);
XSSFWorkbook xwb = new XSSFWorkbook(is);
is.close();
XSSFSheet sheet = xwb.getSheetAt(0);
return readCell(sheet);
}
}catch(Exception e){
e.printStackTrace();
}
return null;
}
private static List<String> readCell(Sheet sheet){
List<String> res = new ArrayList<>();
int start_row = sheet.getFirstRowNum();
int end_row = sheet.getLastRowNum();
for(int i=start_row; i<=end_row;i++){
Row row = sheet.getRow(i);
int start_cell = row.getFirstCellNum();
int end_cell = row.getLastCellNum();
StringBuilder sb = new StringBuilder();
for(int j = start_cell; j<end_cell; j++){
Cell cell = row.getCell(j);
if(cell == null){
continue;
}
sb.append(",");
// CellType type1 = cell.getCellTypeEnum();
// switch(type1){ //获取数据
// case _NONE:
// break;
// case BLANK: //白单元格
// sb.append("");
// break;
// case BOOLEAN: //布尔类型
// break;
// case ERROR: //故障
// break;
// case FORMULA: //公式
// break;
// case NUMERIC: //数值、日期类型
// break;
// case STRING: //字符串
// break;
// default:// 未知类型
// break;
// }
sb.append(cell.toString());
}
String line = sb.toString();
line = line.substring(1);
res.add(line);
}
return res;
}
private static Sheet getSheet(InputStream in,String fileName){
Sheet sheet = null;
try{
String type = fileName.substring(fileName.indexOf(".")+1);
if(XLS.equals(type)){
HSSFWorkbook hwb = hwb = new HSSFWorkbook(in);
sheet = hwb.getSheetAt(0);
}else{
XSSFWorkbook xwb = xwb = new XSSFWorkbook(in);
sheet = xwb.getSheetAt(0);
}
}catch(Exception e){
e.printStackTrace();
}finally {
if(in!=null){
try {
in.close();
in = null;
} catch (IOException e) {
e.printStackTrace();
}
}
}
return sheet;
}
/**
* 读取excel文件，只要某一行的某一列出现空值，直接结束循环，并返回null
* @param in
* @param fileName
* @param titles
* @param date
* @param length
* @return
* @throws IOException
*/
public static List<Map<String,Object>> readExcel(InputStream in,String fileName,String[] titles,String[] colums, Date date,int length){
return readCell(getSheet(in,fileName),titles,colums,date,length);
}
/**
* 读取数据，只要某一行的某一列出现空值，直接结束循环，并返回null
* @param sheet
* @param titles
* @param colums
* @param date
* @return
*/
private static List<Map<String,Object>> readCell(Sheet sheet,String[] titles,String[] colums,Date date,int length){
List<Map<String,Object>> res = new ArrayList<>();
int start_row = sheet.getFirstRowNum();
int end_row = sheet.getLastRowNum();
boolean flag = Boolean.TRUE;
SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
for(int i=start_row; i<=end_row&&flag;i++){//读取行
Row row = sheet.getRow(i);
int start_cell = row.getFirstCellNum();
//隐藏下面这这几段是因为 有些行中，是没有数据的，比如一共7列。第一行全有数据，第二行前三个字段有数据，那么此时end_cell=3，就会读取不到完整的数据了@！
/*int end_cell = row.getLastCellNum();
if((titles!=null && titles.length<end_cell) || (colums!=null && colums.length<end_cell) || end_cell!=length){
return null;
}*/
Map<String,Object> map = new HashMap<>();;
for(int j = start_cell; j<titles.length; j++){//读取列
Cell cell = row.getCell(j);
/*if(cell==null){
continue;
}*/
if(i==0){//检查导入表的标题是否符合colums所给的数据
if(!cell.toString().equals(colums[j])){
res.clear();
res=null;
flag = Boolean.FALSE;
break;
}
}else{
Object data = null;
if(cell!=null){ //levan增加。处理那些数值为空值，list中key没了的问题
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数值型
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是date类型则 ，获取该cell的date值
data = format.format(addDay(cell.getDateCellValue(), 0));
} else {
//xieyz版本
/*double cellValue = cell.getNumericCellValue();
data = String.valueOf(new DecimalFormat("#").format(cellValue)); */
//Levan版本
cell.setCellType(Cell.CELL_TYPE_STRING);
String temp = cell.getStringCellValue();
// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
if (temp.indexOf(".") > -1) {
data = String.valueOf(new Double(temp)).trim();
} else {
data = temp.trim();
}
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
data = cell.getStringCellValue().replaceAll("\"|'", "");
break;
}
}
map.put(titles[j], data);
}
}
if(date!=null)
map.put("createTime", date);
if(i!=0)
res.add(map);
}
return res;
}
private static Date addDay(Date date,int i){
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
calendar.add(Calendar.DAY_OF_MONTH, i);
return calendar.getTime();
}
public static void main(String[] args) throws IOException{
//woshihd
//电渠二季.xls
// File f = new File("F:/电渠二季度政企专项活动宽带号码清单.xlsx");
// File f = new File("F:/星级用户首访第二批.xls");
File f = new File("F:/视频类目标用户 （3月流量使用率低于25%）-20170411.xlsx");
}
}
ExcelUtil工具类
相关推荐
-
Logback自定义日志颜色
java2019-1-7
-
Mybatis+Spring实现Mysql读写分离
java2019-1-8
-
基于redis实现的分布式锁
java2019-1-8
-
根据经纬度算距离
java
2019-1-13
-
jgit工具类
java
2019-1-12
-
实现List<Sting>的正则表达式验证
java2019-1-12
-
Java获取当前系统信息
java2019-1-12
-
读取a.txt和b.txt中有序的序列，合并有序序列保存成c.txt
java2019-1-13
-
springMVC图片上传，删除
java2019-1-13
-
RedisShardPoolUtil
java2019-1-13