ExcelUtil.java 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384
  1. package com.mtgg.utils;
  2. import com.mtgg.bean.PayLogDTO;
  3. import com.mtgg.bean.PoiExcelBean;
  4. import org.apache.poi.hssf.usermodel.*;
  5. import org.apache.poi.hssf.util.HSSFColor;
  6. import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  7. import org.apache.poi.ss.usermodel.*;
  8. import org.apache.poi.ss.util.CellRangeAddress;
  9. import org.apache.poi.xssf.usermodel.*;
  10. import java.beans.IntrospectionException;
  11. import java.beans.PropertyDescriptor;
  12. import java.io.FileInputStream;
  13. import java.io.FileOutputStream;
  14. import java.io.IOException;
  15. import java.io.InputStream;
  16. import java.lang.reflect.InvocationTargetException;
  17. import java.lang.reflect.Method;
  18. import java.math.BigDecimal;
  19. import java.text.DecimalFormat;
  20. import java.text.NumberFormat;
  21. import java.text.ParseException;
  22. import java.text.SimpleDateFormat;
  23. import java.util.*;
  24. /**
  25. * Created by liuhaixiang on 2017/excels_station/5.
  26. */
  27. public class ExcelUtil {
  28. private final static String excel2003L =".xls"; //2003- 版本的excel
  29. private final static String excel2007U =".xlsx"; //2007+ 版本的excel
  30. /**
  31. * Excel导入
  32. */
  33. public static List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception{
  34. List<List<Object>> list = null;
  35. //1 根据流,获取workbook
  36. Workbook work = getWorkbook(in,fileName);
  37. if(null == work){ throw new Exception("创建Excel工作薄为空!"); }
  38. //2 三要素,sheet,row,cell
  39. Sheet sheet = null;
  40. Row row = null;
  41. Cell cell = null;
  42. list = new ArrayList<List<Object>>();
  43. //3 遍历sheet
  44. for (int i = 0; i < work.getNumberOfSheets(); i++) {
  45. sheet = work.getSheetAt(i);
  46. if(sheet==null){continue;}
  47. //遍历sheet中所有行,包含头部行
  48. for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
  49. //读取一行
  50. row = sheet.getRow(j);
  51. //去掉空行和表头
  52. if(row==null||row.getFirstCellNum()==j){continue;}
  53. //遍历所有的列
  54. List<Object> li = new ArrayList<Object>();
  55. for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
  56. cell = row.getCell(y);
  57. li.add(getCellValue(cell));
  58. }
  59. list.add(li);
  60. }
  61. }
  62. return list;
  63. }
  64. /**
  65. * 描述:根据文件后缀,自适应上传文件的版本
  66. */
  67. public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception{
  68. Workbook wb = null;
  69. String fileType = fileName.substring(fileName.lastIndexOf("."));
  70. if(excel2003L.equals(fileType)){
  71. wb = new HSSFWorkbook(inStr); //2003-
  72. }else if(excel2007U.equals(fileType)){
  73. wb = new XSSFWorkbook(inStr); //2007+
  74. }else{
  75. throw new Exception("解析的文件格式有误!");
  76. }
  77. return wb;
  78. }
  79. /**
  80. * 描述:对表格中数值进行格式化
  81. */
  82. public static Object getCellValue(Cell cell){
  83. Object value = null;
  84. DecimalFormat df = new DecimalFormat("0"); //格式化字符类型的数字
  85. SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
  86. DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
  87. switch (cell.getCellType()) {
  88. case Cell.CELL_TYPE_STRING:
  89. value = cell.getRichStringCellValue().getString();
  90. break;
  91. case Cell.CELL_TYPE_NUMERIC:
  92. if("General".equals(cell.getCellStyle().getDataFormatString())){
  93. value = df.format(cell.getNumericCellValue());
  94. }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
  95. value = sdf.format(cell.getDateCellValue());
  96. }else{
  97. value = df2.format(cell.getNumericCellValue());
  98. }
  99. break;
  100. case Cell.CELL_TYPE_BOOLEAN:
  101. value = cell.getBooleanCellValue();
  102. break;
  103. case Cell.CELL_TYPE_BLANK:
  104. value = "";
  105. break;
  106. default:
  107. break;
  108. }
  109. return value;
  110. }
  111. /**
  112. * 导入Excel表结束
  113. * 导出Excel表开始
  114. * @param sheetName 工作簿名称
  115. * @param clazz 数据源model类型
  116. * @param objs excel标题列以及对应model字段名
  117. * @param map 标题列行数以及cell字体样式
  118. */
  119. public static XSSFWorkbook createExcelFile(Class clazz, List objs, Map<Integer, List<PoiExcelBean>> map, String sheetName) throws
  120. IllegalArgumentException,IllegalAccessException,InvocationTargetException,
  121. ClassNotFoundException, IntrospectionException, ParseException {
  122. // 创建新的Excel工作簿
  123. XSSFWorkbook workbook = new XSSFWorkbook();
  124. // 在Excel工作簿中建一工作表,其名为缺省值, 也可以指定Sheet名称
  125. XSSFSheet sheet = workbook.createSheet(sheetName);
  126. // 以下为excel的字体样式以及excel的标题与内容的创建,下面会具体分析;
  127. createFont(workbook); //字体样式
  128. createTableHeader(sheet, map); //创建标题(头)
  129. createTableRows(sheet, map, objs, clazz); //创建内容
  130. return workbook;
  131. }
  132. private static XSSFCellStyle fontStyle;
  133. private static XSSFCellStyle fontStyle2;
  134. public static void createFont(XSSFWorkbook workbook) {
  135. // 表头
  136. fontStyle = workbook.createCellStyle();
  137. XSSFFont font1 = workbook.createFont();
  138. font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
  139. font1.setFontName("黑体");
  140. font1.setFontHeightInPoints((short) 14);// 设置字体大小
  141. fontStyle.setFont(font1);
  142. fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
  143. fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
  144. fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
  145. fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
  146. fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
  147. // 内容
  148. fontStyle2=workbook.createCellStyle();
  149. XSSFFont font2 = workbook.createFont();
  150. font2.setFontName("宋体");
  151. font2.setFontHeightInPoints((short) 10);// 设置字体大小
  152. fontStyle2.setFont(font2);
  153. fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
  154. fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
  155. fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
  156. fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
  157. fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中
  158. }
  159. /**
  160. * 根据ExcelMapping 生成列头(多行列头)
  161. *
  162. * @param sheet 工作簿
  163. * @param map 每行每个单元格对应的列头信息
  164. */
  165. public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<PoiExcelBean>> map) {
  166. int startIndex=0;//cell起始位置
  167. int endIndex=0;//cell终止位置
  168. for (Map.Entry<Integer, List<PoiExcelBean>> entry : map.entrySet()) {
  169. XSSFRow row = sheet.createRow(entry.getKey());
  170. List<PoiExcelBean> excels = entry.getValue();
  171. for (int x = 0; x < excels.size(); x++) {
  172. //合并单元格
  173. if(excels.get(x).getCols()>1){
  174. if(x==0){
  175. endIndex+=excels.get(x).getCols()-1;
  176. CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
  177. sheet.addMergedRegion(range);
  178. startIndex+=excels.get(x).getCols();
  179. }else{
  180. endIndex+=excels.get(x).getCols();
  181. CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);
  182. sheet.addMergedRegion(range);
  183. startIndex+=excels.get(x).getCols();
  184. }
  185. XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());
  186. cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
  187. if (excels.get(x).getCellStyle() != null) {
  188. cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
  189. }
  190. cell.setCellStyle(fontStyle);
  191. }else{
  192. XSSFCell cell = row.createCell(x);
  193. cell.setCellValue(excels.get(x).getHeadTextName());// 设置内容
  194. if (excels.get(x).getCellStyle() != null) {
  195. cell.setCellStyle(excels.get(x).getCellStyle());// 设置格式
  196. }
  197. cell.setCellStyle(fontStyle);
  198. }
  199. }
  200. }
  201. }
  202. public static void createTableRows(XSSFSheet sheet, Map<Integer, List<PoiExcelBean>> map, List objs, Class clazz)
  203. throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,
  204. ClassNotFoundException, ParseException {
  205. int rowindex = map.size();
  206. int maxKey = 0;
  207. List<PoiExcelBean> ems = new ArrayList<PoiExcelBean>();
  208. for (Map.Entry<Integer, List<PoiExcelBean>> entry : map.entrySet()) {
  209. if (entry.getKey() > maxKey) {
  210. maxKey = entry.getKey();
  211. }
  212. }
  213. ems = map.get(maxKey);
  214. List<Integer> widths = new ArrayList<Integer>(ems.size());
  215. for (Object obj : objs) {
  216. XSSFRow row = sheet.createRow(rowindex);
  217. for (int i = 0; i < ems.size(); i++) {
  218. PoiExcelBean em = (PoiExcelBean) ems.get(i);
  219. // 获得get方法
  220. PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz);
  221. Method getMethod = pd.getReadMethod();
  222. Object rtn = getMethod.invoke(obj);
  223. String value = "";
  224. // 如果是日期类型进行转换
  225. if (rtn != null) {
  226. if (rtn instanceof Date) {
  227. value = Double.toString(DateUtil.getExcelDate((Date)rtn));
  228. } else if(rtn instanceof BigDecimal){
  229. NumberFormat nf = new DecimalFormat("#,##0.00");
  230. value=nf.format((BigDecimal)rtn).toString();
  231. } else if((rtn instanceof Integer) && (Integer.valueOf(rtn.toString())<0 )){
  232. value="--";
  233. }else {
  234. value = rtn.toString();
  235. }
  236. }
  237. XSSFCell cell = row.createCell(i);
  238. cell.setCellValue(value);
  239. cell.setCellType(XSSFCell.CELL_TYPE_STRING);
  240. cell.setCellStyle(fontStyle2);
  241. // 获得最大列宽
  242. int width = value.getBytes().length * 300;
  243. // 还未设置,设置当前
  244. if (widths.size() <= i) {
  245. widths.add(width);
  246. continue;
  247. }
  248. // 比原来大,更新数据
  249. if (width > widths.get(i)) {
  250. widths.set(i, width);
  251. }
  252. }
  253. rowindex++;
  254. }
  255. // 设置列宽
  256. for (int index = 0; index < widths.size(); index++) {
  257. Integer width = widths.get(index);
  258. width = width < 2500 ? 2500 : width + 300;
  259. width = width > 10000 ? 10000 + 300 : width + 300;
  260. sheet.setColumnWidth(index, width);
  261. }
  262. }
  263. /**
  264. * @Author: MTGG
  265. * @Date: 15:22 2018/5/9
  266. * @Describe: 读取excel
  267. */
  268. public static List<List<String>> getExcel(String file_path) {
  269. List<List<String>> list = new ArrayList<List<String>>();
  270. try {
  271. InputStream is = new FileInputStream(file_path);
  272. Workbook wb = null;
  273. if (file_path.endsWith("xls")) {
  274. wb = new HSSFWorkbook(is);
  275. } else if (file_path.endsWith("xlsx")) {
  276. wb = new XSSFWorkbook(is);
  277. }
  278. int i = 0;
  279. Sheet sheet = wb.getSheetAt(0);
  280. Iterator rows = sheet.rowIterator();
  281. String str = null;
  282. while (rows.hasNext()) {
  283. List<String> dataList = new ArrayList<String>();
  284. PayLogDTO dataDoDTO = new PayLogDTO();
  285. Row row = (Row) rows.next();
  286. Iterator cells = row.cellIterator();
  287. while (cells.hasNext()) {
  288. Cell cell = (Cell) cells.next();
  289. switch (cell.getCellType()) {
  290. case Cell.CELL_TYPE_NUMERIC://数字
  291. if (DateUtil.isCellDateFormatted(cell)) {
  292. SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
  293. str = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
  294. break;
  295. }
  296. DecimalFormat format = new DecimalFormat("#");
  297. Number value = cell.getNumericCellValue();
  298. str = format.format(value);
  299. dataList.add(str);
  300. break;
  301. case Cell.CELL_TYPE_STRING://字符串
  302. if (i == 0){
  303. continue;
  304. }
  305. str = cell.getStringCellValue();
  306. dataList.add(str);
  307. break;
  308. case Cell.CELL_TYPE_BOOLEAN://Boolean
  309. System.out.print(cell.getBooleanCellValue() + "--boolean\t");
  310. break;
  311. case Cell.CELL_TYPE_FORMULA://公式
  312. System.out.print(cell.getCellFormula() + "\tb");
  313. break;
  314. default:
  315. System.out.print("unsuported sell type" + "\t");
  316. break;
  317. }
  318. }
  319. list.add(dataList);
  320. i++;
  321. }
  322. is.close();
  323. } catch (IOException ex) {
  324. ex.printStackTrace();
  325. }
  326. return list;
  327. }
  328. /**
  329. * @Author: MTGG
  330. * @Date: 15:25 2018/5/9
  331. * @Describe: 写入excel 追加数据
  332. */
  333. public static boolean writeToExcel(PayLogDTO dataDoDTO,String filePath) {
  334. try {
  335. FileInputStream fs=new FileInputStream(filePath); //获取d://test.xls
  336. POIFSFileSystem ps=new POIFSFileSystem(fs); //使用POI提供的方法得到excel的信息
  337. HSSFWorkbook wb=new HSSFWorkbook(ps);
  338. HSSFSheet sheet=wb.getSheetAt(0); //获取到工作表,因为一个excel可能有多个工作表
  339. sheet.setDefaultColumnWidth((short) 40); //宽度制定
  340. HSSFCellStyle style = wb.createCellStyle(); //制作样式
  341. //设置 颜色
  342. style.setFillForegroundColor(HSSFColor.WHITE.index);
  343. //制作字体字号
  344. HSSFFont font = wb.createFont();
  345. font.setColor(HSSFColor.VIOLET.index);
  346. font.setFontHeightInPoints((short) 15);
  347. font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  348. style.setFont(font);
  349. HSSFRow row=sheet.getRow(0); //获取第一行(excel中的行默认从0开始,所以这就是为什么,一个excel必须有字段列头),即,字段列头,便于赋值
  350. System.out.println(sheet.getLastRowNum()+" "+row.getLastCellNum()); //分别得到最后一行的行号,和一条记录的最后一个单元格
  351. FileOutputStream out=new FileOutputStream(filePath); //向d://test.xls中写数据
  352. row=sheet.createRow((short)(sheet.getLastRowNum()+1)); //在现有行号后追加数据
  353. row.setRowStyle(style);
  354. //追加数据
  355. row.createCell(0).setCellValue(dataDoDTO.getAccount()); //设置第一个(从0开始)单元格的数据
  356. row.createCell(1).setCellValue(dataDoDTO.getTotalFee()); //设置第二个(从0开始)单元格的数据
  357. row.createCell(2).setCellValue(dataDoDTO.getDate());
  358. out.flush();
  359. wb.write(out);
  360. out.close();
  361. System.out.println(row.getPhysicalNumberOfCells()+" "+row.getLastCellNum());
  362. }catch (Exception e){
  363. System.out.println("写excel出错");
  364. }
  365. return true;
  366. }
  367. }