最近在做excel的导入导出功能,写了个比较通用的方法,贴上来记录一下、、
工具包用的是韩国棒子写的JavaExcel(jxl),配置比较简单,对中文的支持也不错,就是不支持07或以上的excel版本,原作者貌似也没有打算出新版本的意思,这个比较纠结。
相关的jar包是 jxl.jar,网上到处都是、
- 读取excel文件
/** * 读取excel文件 * @param file excel文件 * @param headerConvert 转换头 * @return 记录列表 * @throws Exception */ public static List<Map<String, Object>> import2Excel(File file, Map<String, String> headerConvert) throws Exception { InputStream is = null; Workbook workbook = null; //记录list List<Map<String, Object>> list=new ArrayList<Map<String,Object>>(); try { is = new FileInputStream(file); workbook = Workbook.getWorkbook(is); Sheet sheet = workbook.getSheet(0); int column = sheet.getColumns(); int row = sheet.getRows(); //转换map Map<String, Object> convertMap = new HashMap<String, Object>(); // 记录 Map<String, Object> recordMap = new HashMap<String, Object>(); // 读取头 for (int i = 0; i < column; i++) { Cell cell = sheet.getCell(i, 0); String temp = cell.getContents(); convertMap.put("" + i, headerConvert.get(temp)); } for (int i = 1; i < row; i++) { for (int j = 0; j < column; j++) { Cell cell = sheet.getCell(j, i); Object content = new Object(); if (cell.getType().equals(CellType.LABEL)) { content = cell.getContents(); } else if (cell.getType().equals(CellType.NUMBER)) { NumberCell numberCell = (NumberCell) cell; content = numberCell.getValue(); } else if (cell.getType().equals(CellType.DATE)) { DateCell dateCell = (DateCell) cell; content = dateCell.getDate(); } recordMap.put(""+convertMap.get(""+j), content); } list.add(recordMap); } // 操作完成时,关闭对象,释放占用的内存空间 workbook.close(); is.close(); return list; } catch (Exception e) { e.printStackTrace(System.out); } finally { if (is != null) { is.close(); } } return list; }
- 创建excel
public static void exportExcel(Map<String,String> title,List<Map<String,Object>> dataSet, String filename,String sheetname)throws Exception{
// 得到项目的根目录
String path = ServletActionContext.getServletContext().getRealPath("/");
// 在服务器创建临时文件
File file = new File(path + "temp");
if(!file.isDirectory()){
file.mkdirs();
}
file = new File(file.getPath()+File.separator+filename);
// 创建工作表
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(file);
} catch (Exception e) {
e.printStackTrace();
}
//获取工作簿
WritableSheet sheet = workbook.createSheet(sheetname, 0);
WritableFont font0 = new WritableFont(WritableFont.TIMES, 13,
WritableFont.NO_BOLD);
WritableFont font = new WritableFont(WritableFont.TIMES, 13,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.RED);
WritableCellFormat format0 = new WritableCellFormat(font0);
WritableCellFormat format = new WritableCellFormat(font);
// 水平居中
format.setAlignment(Alignment.CENTRE);
format0.setAlignment(Alignment.CENTRE);
//写表头
int col = 0;
for(Map.Entry<String,String> entry : title.entrySet()){
Label label = new Label(col, 0, entry.getValue(), format0);
sheet.addCell(label);
col++;
}
int row = 1;
for(Map<String,Object> data : dataSet){
int _col = 0;
for(Map.Entry<String,String> entry : title.entrySet()){
Label label = new Label(_col, row, StringUtil.filterNull(data.get(entry.getKey())), format0);
sheet.addCell(label);
_col++;
}
row++;
}
// 保存
workbook.write();
workbook.close();
//输出到前台
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment; filename="
+ java.net.URLEncoder.encode(filename, "UTF-8"));
// 创建excel文件的输入输出流
InputStream is = new FileInputStream(file);
OutputStream os = response.getOutputStream();
byte[] buf= new byte[1024];
int size = is.read(buf);
while (size > 0) {
os.write(buf, 0, size);
size = is.read(buf);
}
is.close();
os.close();
file.delete();
}
大概就这样吧、挺简单的,不过不支持07还得想想别的办法,毕竟03是个过时产品了、、以后维护也不怎么方便、、、