导出:
后台我用的是springmvc框架。
@RequestMapping("/downUser")
public String downuser(HttpServletRequest request,HttpServletResponse response) throws IOException{String fileName="用户数据Excel文件";
//往数据库插入一条下载记录数据
Down d=new Down();
d.setFileName("用户数据Excel文件");try {
d.setDownUser(((User) sessionProvider.getAttribute(request, CommonUtils.USER_SESSION)).getUserId());
} catch (Exception e) {
// TODO: handle exception
}
equipmentService.addDown(d);
//填充projects数据
List<QueryUser> projects=userService.findallUser();
List<Map<String,Object>> list=ExcelUtil.createUserExcelRecord(projects);
String columnNames[]={"用户名","密码","真实姓名","运营商名","性别","手机号码","邮箱","创建/导入时间","创建人名或导入人名"};//列名
String keys[] ={"username","password","reaName","opeName","sex","mobile","email","createTime","createName"};//map中的key
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
ExcelUtil.createWorkBook(list,keys,columnNames).write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
return null;
}
下面是ExcelUtil工具类的代码:
public class ExcelUtil {
/**
* 创建excel文档,
* [@param](http://my.oschina.net/u/2303379) list 数据
* @param keys list中map的key数组集合
* @param columnNames excel的列名
* */
public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) {
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
// 创建第一个sheet(页),并命名
Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
// 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。
for(int i=0;i<keys.length;i++){
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}
// 创建第一行
Row row = sheet.createRow((short) 0);
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
// 创建第一种字体样式(用于列名)
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式(用于值)
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// Font f3=wb.createFont();
// f3.setFontHeightInPoints((short) 10);
// f3.setColor(IndexedColors.RED.getIndex());
// 设置第一种单元格的样式(用于列名)
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
// 设置第二种单元格的样式(用于值)
cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_CENTER);
//设置列名
for(int i=0;i<columnNames.length;i++){
Cell cell = row.createCell(i);
cell.setCellValue(columnNames[i]);
cell.setCellStyle(cs);
}
//设置每行每列的值
for (short i = 1; i < list.size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行,在页sheet上
Row row1 = sheet.createRow((short) i);
// 在row行上创建一个方格
for(short j=0;j<keys.length;j++){
Cell cell = row1.createCell(j);
cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
cell.setCellStyle(cs2);
}
}
return wb;
}
public static List<Map<String, Object>> createUserExcelRecord(List<QueryUser> projects) {
List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
Map<String, Object> map = new HashMap<String, Object>();
map.put("sheetName", "sheet1");
listmap.add(map);
QueryUser project=null;
for (int j = 0; j < projects.size(); j++) {
project=projects.get(j);
Map<String, Object> mapValue = new HashMap<String, Object>();
mapValue.put("username", project.getUsername());
mapValue.put("password", project.getPassword());
mapValue.put("reaName", project.getReaName());
mapValue.put("opeName", project.getOpeName());
mapValue.put("sex", project.getSex());
mapValue.put("mobile", project.getMobile());
mapValue.put("email", project.getEmail());
mapValue.put("createTime",DateUtil.getDateTimeString(project.getCreateTime()));
mapValue.put("createName",project.getCreateName());
listmap.add(mapValue);
}
return listmap;
}
}
导入:
@RequestMapping(value = "/save")
public void saveExcel( HttpServletRequest request, ModelMap model,HttpServletResponse response) throws Exception{
//String fileName = file.getOriginalFilename();
//System.out.println(fileName);
int count=0;
// = file.getInputStream();
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
InputStream inputStream=multipartRequest.getFile("file").getInputStream();
List<String> readList = new ArrayList<String>();
XSSFWorkbook wssf=new XSSFWorkbook(inputStream);
//循环每一页
for (XSSFSheet xssfSheet : wssf) {
if(xssfSheet==null){
continue;
}
for(int cellNum = 2; cellNum <= xssfSheet.getLastRowNum(); cellNum++){
String result="";
XSSFRow xssfrow=xssfSheet.getRow(cellNum);
int min=xssfrow.getFirstCellNum();
int max= xssfrow.getLastCellNum();
for(int cow=min;cow<max;cow++){
XSSFCell cell=xssfrow.getCell(cow) ;
if(cell==null){
break;
}
result+=(cell.toString().trim()+",");
if(cell.toString().trim().length()<=0){
break;
}
}
System.out.println(result);
readList.add(result);
}
}
String notSuccess="";
try{
Connection connection = JdbcUtil.getConnection();
PreparedStatement prepareStatement = connection.prepareStatement("insert into tb_user (username,password,reaName,operatorId,sex,mobile,email,createUserId) values(?,?,?,?,?,?,?,?)");
int id=((User)(sessionProvider.getAttribute(request,CommonUtils.USER_SESSION))).getUserId();
for (String xssfSheet : readList) {
String[] split = xssfSheet.split(",");
BigDecimal bd = new BigDecimal(split[4]);
prepareStatement.setString(1,split[0]);
if(split[6].contains(".")){
BigDecimal bd1 = new BigDecimal(split[6]);
prepareStatement.setString(2,bd1.toPlainString());
}else{
prepareStatement.setString(2,split[6]);
}
prepareStatement.setString(3,split[1]);
List<Operator> operators = MemoryData.operators;
long opId=0;
for (Operator operator : operators) {
if(operator.getOpeName().equals(split[2])){
opId=operator.getOpeId();
}
}
prepareStatement.setInt(4,(int)opId);
prepareStatement.setString(5,split[3]);
prepareStatement.setString(6,bd.toPlainString());
prepareStatement.setString(7,split[5]);
prepareStatement.setInt(8,id);
prepareStatement.addBatch();
try {
prepareStatement.execute();
System.out.println("chenggong fou ");
} catch (Exception e) {
count++;
notSuccess+=split[0]+";";
e.printStackTrace();
}
}
prepareStatement.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
int all=readList.size();
int not=all-count;
System.out.println("xialai de ");
response.getWriter().write(not+","+count+","+notSuccess);;
//response.getOutputStream().write(JSON.toJSONString(count).getBytes());
}