package com.bjsxt.servlet;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.bjsxt.biz.DutyBiz;
import com.bjsxt.biz.impl.DutyBizImpl;
import com.bjsxt.dao.DutyDao;
import com.bjsxt.dao.impl.DutyDaoImpl;
import com.bjsxt.util.ExcelUtil;
@WebServlet("/login/exportDutyServlet")
public class ExportDutyServlet extends HttpServlet{
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
DutyDao dd = new DutyDaoImpl();
DutyBiz db = new DutyBizImpl();
db.setDutyDao(dd);
resp.setHeader("content-disposition", "attachment;filename=vv.xlsx");
List<Map<String, String>> list = db.selectAll();
ExcelUtil.export("考勤记录", resp.getOutputStream(), list, "username","name","dname","checkDate","checkIn","checkOut");
resp.getOutputStream().flush();
}
}
package com.bjsxt.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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;
import com.bjsxt.biz.DutyBiz;
import com.bjsxt.biz.impl.DutyBizImpl;
import com.bjsxt.dao.DutyDao;
import com.bjsxt.dao.impl.DutyDaoImpl;
public class ExcelUtil {
public static void export(String sheetName,OutputStream os,List<?> list,String ... fields) {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet(sheetName);
//标题(创建一行)
Row row = sheet.createRow(0);
for (int i = 0; i < fields.length; i++) {
//创建单元格(一行得第i列)
Cell cell = row.createCell(i);
//把标题放到单元格中。
cell.setCellValue(fields[i]);
//设置单元格格式
CellStyle cs = wb.createCellStyle();
//设置对齐方式/水平居中
cs.setAlignment(HorizontalAlignment.CENTER);
//设置字体
Font font = wb.createFont();
//加粗
font.setBold(true);
cs.setFont(font);
//把样式应用到单元格中
cell.setCellStyle(cs);
}
for(int i = 1;i<list.size();i++) {
row = sheet.createRow(i);
for(int j = 0;j<fields.length;j++) {
Cell cell = row.createCell(j);
@SuppressWarnings("unchecked")
Map<String, String > map = (Map<String, String>) list.get(i);
cell.setCellValue(map.get(fields[j])==null?"":map.get(fields[j]));
}
}
try {
wb.write(os);
} catch (IOException e1) {
e1.printStackTrace();
}
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception {
DutyDao dd = new DutyDaoImpl();
DutyBiz db = new DutyBizImpl();
db.setDutyDao(dd);
List<Map<String, String>> list = db.selectAll();
OutputStream os = new FileOutputStream(new File("C:\\Users\\Windows\\Desktop\\dd.xlsx"));
export("duty", os, list, "username","name","dname","checkDate","checkIn","checkOut");
System.out.println("ok");
}
private static Object getFieldValue() {
System.out.println("我的是从list中的map中取得数据,如果list中存放得是对象数据,则需要"
+ "编写此方法将数据转化");
return null;
}
}