必须依赖poi包
结果:
package best.utils;
import java.util.Map;
import org.apache.poi.ss.usermodel.Workbook;
public interface ExcelTemplate {
/**
* 可选
* 设置excel日期样式
* @param dataFormat
*/
public void setDateFormat(String dataFormat);
/**
* 可选
* 设置工作薄名称
* @param sheetName
*/
public void setSheetName(String sheetName) ;
/**
* 创建excel
* @return key=2003或2007 对应为excel2003、excel2007
*/
public Map<String,Workbook> createExcel();
}
package best.utils;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.XSSFWorkbook;
public class ExcelTemplateImpl implements ExcelTemplate {
private List<Map<String, Object>> datas;
private final Map<String,Workbook> wbmap = new HashMap<String, Workbook>();
private String dataFormat = "yyyy-MM-dd";
private String sheetName;
public ExcelTemplateImpl(List<Map<String, Object>> datas){
init(datas);
}
/**
* 初始化
* @param datas
*/
private void init(List<Map<String, Object>> datas){
this.datas = datas;
//构造2003、2007版excel
wbmap.put("2003", new HSSFWorkbook());
wbmap.put("2007", new XSSFWorkbook());
}
public void setDateFormat(String dataFormat){
this.dataFormat = dataFormat;
}
public Map<String,Workbook> createExcel(){
Set<Entry<String, Workbook>> wbset = wbmap.entrySet();
Iterator<Entry<String, Workbook>> eit = wbset.iterator();
while(eit.hasNext()){
Entry<String, Workbook> workEntry = eit.next();
Workbook wb = workEntry.getValue();
//设置样式
CellStyle cellStyle = wb.createCellStyle();//创建样式
cellStyle.setDataFormat(wb.createDataFormat().getFormat(dataFormat));//日期格式
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//居中
//创建一个新的表\并创建名称
Sheet s = null==sheetName?s = wb.createSheet():wb.createSheet(sheetName);
for(int i=0; i<datas.size(); i++){ //遍历数据
Map<String,Object> map = datas.get(i);
//定义列(列名第一行)
Row titleColl = s.createRow(0);
//定义行(内容第二行起)
Row row = s.createRow(i+1);
Set<Entry<String, Object>> set = map.entrySet();
Iterator<Entry<String, Object>> it = set.iterator();
int x = 0;
while(it.hasNext()){
//列名
Entry<String, Object> entry = it.next();
Cell titleCell = titleColl.createCell(x);
titleCell.setCellValue(entry.getKey());
//设置列名样式
titleCell.setCellStyle(cellStyle);
//内容
Cell cell = row.createCell(x);
Object value = entry.getValue();
if(value instanceof String){
cell.setCellValue(value.toString());
}else if(value instanceof Double){
cell.setCellValue((Double)value);
}else if(value instanceof Integer){
cell.setCellValue((Integer)value);
}else if(value instanceof Float){
cell.setCellValue((Float)value);
}else if(value instanceof Boolean){
cell.setCellValue((Boolean)value);
}else if(value instanceof java.util.Date|value instanceof java.sql.Date){
cell.setCellValue((Date)value);
}
//设置内容样式
cell.setCellStyle(cellStyle);
//自动调整列宽
s.autoSizeColumn(x);
x++;
}
}
}
return wbmap;
}
public List<Map<String, Object>> getDatas() {
return datas;
}
public void setDatas(List<Map<String, Object>> datas) {
this.datas = datas;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
}
测试
package best.utils;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Test {
/**
* @param args
*/
public static void main(String[] args){
// 模拟数据
List<Map<String,Object>> datas = new ArrayList<Map<String,Object>>();
Map<String,Object> map = new HashMap<String,Object>();
map.put("用户名", "xiaomaha");
map.put("密码", "1234567qq");
map.put("创建日期", new Date());
Map<String,Object> map2 = new HashMap<String,Object>();
map2.put("用户名", "xiaomaha21111111111111111111111111111111111111中文11111112");
map2.put("密码", "1-1234567qq11111111111111111111111111111111111111中文111111111113");
map2.put("创建日期", new Date());
Map<String,Object> map3 = new HashMap<String,Object>();
map3.put("用户名", "xiaomaha3");
map3.put("密码", "2-1234567qq");
map3.put("创建日期", new Date());
datas.add(map);
datas.add(map2);
datas.add(map3);
//创建excel
ExcelTemplate et = new ExcelTemplateImpl(datas);
et.setSheetName("测试");
Map<String, Workbook> workMap = et.createExcel();
Workbook wb = workMap.get("2007"); //获取excel2007
//如果愿意英汉转换
// 1:最好的方法是写一个xml！ 然后解析
// 2:json格式，当然需要相对应的json插件
// 3:Map实现
// ---------------------------------------
// Map例子
// Map<String,Object> map3 = new HashMap<String,Object>();
// map3.put("userName", "xiaomaha3");
// map3.put("passWord", "2-1234567qq");
// map3.put("createDate", new Date());
// Map<String,String> m = new HashMap<String,String>();
// m.put("userName", "用户名");
// m.put("passWord", "密码");
// m.put("createDate", "创建日期");
// 然后ExcelTemplate 加入一个方法就可以实现了
//测试 写入磁盘
String filename = "d://workbook2.xls";
if (wb instanceof XSSFWorkbook) {
filename = filename + "x";
}
FileOutputStream out;
try {
out = new FileOutputStream(filename);
try {
wb.write(out);
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
页面下载例子
String contenttype = "application/msexcel"
response.setContentType(contenttype);
response.setHeader("Content-Disposition", "attachment;Filename=" + filename + ".xls");//03为.xls 07为.xlsx
OutputStream os = response.getOutputStream();
//创建excel
ExcelTemplate et = new ExcelTemplateImpl(datas);
Map<String, Workbook> workMap = et.createExcel();
Workbook wb = workMap.get("2003"); //获取excel2003
wb.write(os);
os.flush();
os.close();
下载
- 大小: 4.1 KB
- 大小: 4.8 KB
- bestExcel.rar (9 MB)
- 下载次数: 8
相关文章
