Excel导入、导出
来源:互联网 发布:大数据风控总监 编辑:程序博客网 时间:2024/06/03 18:55
- package com.poi;
- import java.util.List;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- /**
- * <pre>
- * Title: ExcelEntity.java
- * Description:
- * Copyright: Maple Copyright (c) 2013
- * Company:
- * </pre>
- *
- * @author duanke
- * @version 1.0
- * @date 2013年12月31日
- */
- public class ExcelEntity {
- private String sheetName; // excel 名称
- private String[] columnNames; // 列名
- private String[] propertyNames; // 属性名称
- private String[] cLabels;
- private int rpp = 200;
- private HSSFCellStyle style = null;
- @SuppressWarnings("rawtypes")
- private List resultList;
- public String getSheetName() {
- return sheetName;
- }
- public void setSheetName(String sheetName) {
- this.sheetName = sheetName;
- }
- public String[] getColumnNames() {
- return columnNames;
- }
- public void setColumnNames(String[] columnNames) {
- this.columnNames = columnNames;
- }
- public String[] getPropertyNames() {
- return propertyNames;
- }
- public void setPropertyNames(String[] propertyNames) {
- this.propertyNames = propertyNames;
- }
- public String[] getCLabels() {
- return cLabels;
- }
- public void setCLabels(String[] labels) {
- cLabels = labels;
- }
- public int getRpp() {
- return rpp;
- }
- public void setRpp(int rpp) {
- this.rpp = rpp;
- }
- public HSSFCellStyle getStyle() {
- return style;
- }
- public void setStyle(HSSFCellStyle style) {
- this.style = style;
- }
- @SuppressWarnings("rawtypes")
- public List getResultList() {
- return resultList;
- }
- @SuppressWarnings("rawtypes")
- public void setResultList(List resultList) {
- this.resultList = resultList;
- }
- }
[java] view plain copy
- package com.poi;
- import java.io.IOException;
- import java.io.OutputStream;
- import java.io.UnsupportedEncodingException;
- import java.text.SimpleDateFormat;
- import java.util.Date;
- import java.util.Iterator;
- import java.util.List;
- import java.util.Map;
- import javax.servlet.http.HttpServletResponse;
- 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.Region;
- /**
- * <pre>
- * Title: ExportExcelUtil.java
- * Description:
- * Copyright: Maple Copyright (c) 2013
- * Company:
- * </pre>
- *
- * @author duanke
- * @version 1.0
- * @date 2013年12月31日
- */
- public class ExportExcelUtil {
- /**
- * 创建Excel表格
- *
- * @param object
- * @param outStream
- * @throws Exception
- */
- @SuppressWarnings("rawtypes")
- public static void exportExcel(ExcelEntity object, OutputStream outStream) throws Exception {
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet(object.getSheetName());
- HSSFRow row = sheet.createRow(0);// 创建第一行
- HSSFCell cell = row.createCell(0);// 创建第一行的第一个单元格
- cell.setCellValue("序号");
- String[] colNames = object.getColumnNames();
- String[] propertys = object.getPropertyNames();
- for (int i = 0; i < colNames.length; i++) { // 添加列名,从第一行的第二个单元格开始添加
- row.createCell(i + 1).setCellValue(colNames[i]);
- }
- Iterator it = object.getResultList().iterator();
- int rowNum = 1; // 从第二行开始添加数据
- while (it.hasNext()) {
- Map map = (Map) it.next();
- HSSFRow rw = sheet.createRow(rowNum);
- rw.createCell(0).setCellValue(rowNum); // 添加序号
- rowNum++;
- for (int x = 0; x < propertys.length; x++) {
- String property = propertys[x];
- if (map.containsKey(property)) {
- Object value = map.get(propertys[x]); // 根据属性名称得到属性值
- if (value == null || "null".equalsIgnoreCase(value.toString())) {
- value = "";
- }
- rw.createCell(x + 1).setCellValue(value + "");
- } else {
- rw.createCell(x + 1).setCellValue("");
- }
- }
- }
- try {
- wb.write(outStream);
- outStream.flush();
- outStream.close();
- } catch (IOException e) {
- if (outStream != null) {
- outStream.close();
- }
- e.printStackTrace();
- }
- }
- /**
- * 导出Excel
- *
- * @param response
- * @param list
- * @param columns
- * @param propertyNames
- * @throws IOException
- * @throws UnsupportedEncodingException
- * @throws Exception
- */
- @SuppressWarnings("rawtypes")
- public static void exportView(HttpServletResponse response, List<Map> list, String[] columns, String[] propertyNames) throws IOException, UnsupportedEncodingException, Exception {
- SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
- ExcelEntity entity = new ExcelEntity();
- entity.setColumnNames(columns);
- entity.setPropertyNames(propertyNames);
- entity.setResultList(list);
- entity.setSheetName(sdf.format(new Date()));
- OutputStream outStream = response.getOutputStream();
- response.setContentType("application/vnd.ms-excel;charset=UTF-8");
- String fileName = sdf.format(new Date()) + ".xls";
- response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "iso8859-1"));
- exportExcel(entity, outStream);
- }
- /**
- * 创建通用EXCEL头部
- *
- * @param headString
- * 头部显示的字符
- * @param colSum
- * 该报表的列数
- */
- @SuppressWarnings("deprecation")
- public void createNormalHead(String headString, int colSum, String sheetName) {
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet(sheetName);
- HSSFRow row = sheet.createRow(0);
- // 设置第一行
- HSSFCell cell = row.createCell(0);
- row.setHeight((short) 400);
- // 定义单元格为字符串类型
- cell.setCellType(HSSFCell.ENCODING_UTF_16);
- cell.setCellValue(new HSSFRichTextString(headString));
- // 指定合并区域
- sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) colSum));
- 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.setFontHeight((short) 300);
- cellStyle.setFont(font);
- cell.setCellStyle(cellStyle);
- }
- }
[java] view plain copy
- package com.poi;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import org.apache.commons.io.FilenameUtils;
- 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.ss.util.CellReference;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- /**
- * <pre>
- * Title: ReadExcel.java
- * Description:
- * Copyright: Maple Copyright (c) 2013
- * Company:
- * </pre>
- *
- * @author duanke
- * @version 1.0
- * @date 2013年12月31日
- */
- public class ReadExcel {
- /**
- * Excel 2003
- */
- private final static String XLS = "xls";
- /**
- * Excel 2007
- */
- private final static String XLSX = "xlsx";
- /**
- * 由Excel文件的Sheet导出至List
- *
- * @param file
- * 导入的excel文件
- * @param sheetNum
- * excel工作空间,一般情况为0
- * @return
- */
- public static List<Map<String, Object>> exportListFromExcel(File file, int sheetNum) throws IOException {
- return exportListFromExcel(new FileInputStream(file), FilenameUtils.getExtension(file.getName()), sheetNum);
- }
- /**
- * 由Excel流的Sheet导出至List
- *
- * @param is
- * @param extensionName
- * @param sheetNum
- * @return
- * @throws IOException
- */
- public static List<Map<String, Object>> exportListFromExcel(InputStream is, String extensionName, int sheetNum) throws IOException {
- Workbook workbook = null;
- if (extensionName.toLowerCase().equals(XLS)) {
- workbook = new HSSFWorkbook(is);
- } else if (extensionName.toLowerCase().equals(XLSX)) {
- workbook = new XSSFWorkbook(is);
- }
- return readCell(workbook, sheetNum);
- }
- /**
- * 读取Cell的值
- *
- * @param sheet
- * @return
- */
- public static List<Map<String, Object>> readCell(Workbook workbook, int sheetNum) {
- Sheet sheet = workbook.getSheetAt(sheetNum);
- // 解析公式结果
- // FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
- List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
- // 遍历所有行
- // for (Row row : sheet)
- // 除去表头即第一行
- for (int i = 1; i <= sheet.getLastRowNum(); i++) {
- Row row = sheet.getRow(i);
- Map<String, Object> map = new HashMap<String, Object>();
- // 便利所有列
- for (Cell cell : row) {
- // 获取单元格的类型
- CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
- String key = cellRef.formatAsString();
- switch (cell.getCellType()) {
- // 字符串
- case Cell.CELL_TYPE_STRING:
- map.put(key, cell.getRichStringCellValue().getString());
- break;
- // 数字
- case Cell.CELL_TYPE_NUMERIC:
- if (DateUtil.isCellDateFormatted(cell)) {
- map.put(key, cell.getDateCellValue());
- } else {
- map.put(key, cell.getNumericCellValue());
- }
- break;
- // boolean
- case Cell.CELL_TYPE_BOOLEAN:
- map.put(key, cell.getBooleanCellValue());
- break;
- // 方程式
- case Cell.CELL_TYPE_FORMULA:
- map.put(key, cell.getCellFormula());
- break;
- case Cell.CELL_TYPE_BLANK:
- break;
- case Cell.CELL_TYPE_ERROR:
- break;
- // 空值
- default:
- map.put(key, "");
- }
- }
- list.add(map);
- }
- return list;
- }
- public static void main(String[] args) throws IOException {
- // String paths = "c:\\excel.xlsx";
- String paths = ReadExcel.class.getResource("c:\\excel.xlsx").getFile();
- List<Map<String, Object>> lists = ReadExcel.exportListFromExcel(new File(paths), 0);
- System.out.println(lists);
- }
- }
阅读全文
0 0
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- EXCEL导入导出
- Excel导入&导出
- 数据库导入导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- excel导入、导出数据
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- 导入/导出Excel
- CSS Modules使用详解
- Linux内核源码阅读以及工具
- Unity 利用AndroidJavaClass 获取jar实例
- 最新手机号码验证正则表达式
- CERT NetSA Security Suite遇见 Spark:网络流量分析新操作
- Excel导入、导出
- Spl迭代器--AppendIterator
- opencv读取、显示、保存图片
- rvm安装 卸载 升级ruby版本
- 1.4union——联合体
- 每天进步一点,坚持每天更新,记录开始日期2017-8-25
- 写给自己的话
- 将n个数分为m个数[动态规划][两种思路]
- 拼上物联网最后拼图 IBM启动LoRa平台