package com.yltd.cnyun.common.utils;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.nio.charset.Charset;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
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.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
// import com.csvreader.CsvReader;
/**
* @author
* @Description:TODO操作Excel工具类
* @date 2018年8月8日
* @since 2018年8月8日
*/
public class ExcelUtil {
private static DateFormat dateformate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**
* 输入excel文件 titleMp 对应结果集的字段名称为Key ,显示在Excel标题的内容为值.例:name-->姓名
*
* @param titleMp
* @param recordLt
* 输出的结果集
* @param path
* 输出路径
* @throws Exception
*/
public static void createFile(Map<String, String> titleMp, List recordLt, String path) throws Exception {
Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
String safeName = WorkbookUtil.createSafeSheetName("sheet1");
// 创建sheet
Sheet sheet1 = wb.createSheet(safeName);
// 设置标题单元格样式
CellStyle titlestyle = wb.createCellStyle();
titlestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titlestyle.setFillForegroundColor(HSSFColor.YELLOW.index);
titlestyle.setBorderBottom(CellStyle.BORDER_THIN);
titlestyle.setBorderTop(CellStyle.BORDER_THIN);
titlestyle.setBorderLeft(CellStyle.BORDER_THIN);
titlestyle.setBorderRight(CellStyle.BORDER_THIN);
titlestyle.setAlignment(CellStyle.ALIGN_CENTER);
// 生成行对象
Row row = sheet1.createRow((short) 0);
// 生成标题
Iterator iter = titleMp.entrySet().iterator();
int cellNum = 0;
List<String> cellNameLt = new ArrayList<String>();
while (iter.hasNext()) {
Map.Entry entry = (Map.Entry) iter.next();
Object val = entry.getValue();
Cell cell = row.createCell(cellNum);
cell.setCellValue(createHelper.createRichTextString(val.toString()));
cell.setCellStyle(titlestyle);
++cellNum;
cellNameLt.add(entry.getKey().toString());
}
CellStyle style = wb.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
int rowNum = 1;
// 生成行列表
for (int k = 0; k < recordLt.size(); k++) {
// 创建行
row = sheet1.createRow((short) rowNum);
int cellNo = 0;
for (int i = 0; i < cellNameLt.size(); i++) {
Cell cell = row.createCell(cellNo);
// 获取对应的列的值
Object value = PropertyUtils.getProperty(recordLt.get(k), cellNameLt.get(i));
String data = formateExcelData(value);
cell.setCellValue(createHelper.createRichTextString(data));
cell.setCellStyle(style);
++cellNo;
}
++rowNum;
}
// 文件输出
FileOutputStream fileOut = new FileOutputStream(path);
wb.write(fileOut);
fileOut.close();
}
/**
* 格式化excel导出数据
*
* @param value
*/
private static String formateExcelData(Object value) {
if (null == value) {
return null;
} else {
if (value instanceof Date) {
value = dateformate.format(value);
}
if (value instanceof Double) {
value = Double.parseDouble(value.toString());
}
return value.toString();
}
}
/**
* 读csv格式
*
* @param dir
* 文件路径 .csv
* @return
*//*
public static List<String[]> readCsvFile(String dir) {
File file = new File(dir);
CsvReader reader = null;
List<String[]> csvList = new ArrayList<String[]>(); // 用来保存数据
try {
reader = new CsvReader(file.getAbsolutePath(), ',', Charset.forName("SJIS"));
// 一般用这编码读就可以了
reader.readHeaders();
while (reader.readRecord()) { // 逐行读入除表头的数据
csvList.add(reader.getValues());
}
reader.close();
return csvList;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (reader != null) {
reader.close();
}
}
return null;
}
*//**
* 解析某个目录下个所有csv
*
* @param dir
* 文件路径 .csv
* @return
*//*
public static List<List<String[]>> readDirCsvFile(String dir) {
File fileCount = new File(dir);
File[] list = fileCount.listFiles();
CsvReader reader = null;
List<List<String[]>> listStrs = new ArrayList<List<String[]>>();
try {
for (int i = 0; i < list.length; i++) {
File file = list[i];
List<String[]> csvList = new ArrayList<String[]>(); // 用来保存数据
reader = new CsvReader(file.getAbsolutePath(), ',', Charset.forName("SJIS"));
// 一般用这编码读就可以了
reader.readHeaders();
while (reader.readRecord()) { // 逐行读入除表头的数据
csvList.add(reader.getValues());
}
listStrs.add(csvList);
}
return listStrs;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (reader != null) {
reader.close();
}
}
return null;
}*/
}
工具类:ExcelUtil
最新推荐文章于 2024-07-23 16:21:13 发布