excel导出
public class Writer {
public static String setFileDownloadHeader(HttpServletRequest request, String fileName) {
final String userAgent = request.getHeader("USER-AGENT");
String finalFileName = null;
try {
if(StringUtils.contains(userAgent, "MSIE")){//IE浏览器
finalFileName = URLEncoder.encode(fileName,"UTF8");
}else if(StringUtils.contains(userAgent, "Mozilla")){//google,火狐浏览器
finalFileName = new String(fileName.getBytes(), "ISO8859-1");
}else{
finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器
}
} catch (UnsupportedEncodingException e) {
}
return finalFileName;
}
}
public void selectAll(HttpServletRequest request,
HttpServletResponse response) {
// excel数据导出
System.out.println("excel数据导出");
// 创建一个工作簿
HSSFWorkbook workBook = new HSSFWorkbook();
// 创建一个工作表,名为:第一页
HSSFSheet sheet = workBook.createSheet("sheet1");
// 设置单元格的宽度(0:表示第一行的第一个单元格,1:第一行的第二个单元格)
sheet.setColumnWidth((short) 0, 2500);
sheet.setColumnWidth((short) 1, 5000);
sheet.setColumnWidth((short) 2, 5000);
// 创建一个单元格,从0开始
HSSFRow row = sheet.createRow((short) 0);
// 构造一个数组设置第一行之后的单元格
HSSFCell[] cell = new HSSFCell[1];
for (int i = 0; i < cell.length; i++) {
cell[i] = row.createCell(i);
}
cell[0].setCellValue("证件号");
// cell[1].setCellValue("姓名");
// cell[2].setCellValue("单位");
// 获得从数据库中查询出来的数据
List<BlackListItem> list = blackListDao.selectAll();
// 循环list中的数据
for (int i = 0; i < list.size(); i++) {
BlackListItem blackListItem = list.get(i);
HSSFRow dataRow = sheet.createRow(i + 1);
// 創建2個單元格
HSSFCell[] data = new HSSFCell[1];
for (int j = 0; j < data.length; j++) {
data[j] = dataRow.createCell(j);
}
data[0].setCellValue(blackListItem.getPassId());
// data[1].setCellValue(blackListItem.getName());
// data[2].setCellValue(blackListItem.getCompanyName());
}
try {
// 设置reponse参数
String fileName = Writer
.setFileDownloadHeader(request, "人员信息表.xls");
//
response.setHeader("Content-Disposition", "inline; filename="
+ fileName);
// 确保发送的当前文本格式
response.setContentType("application/vnd.ms-excel");
ServletOutputStream outputStream = response.getOutputStream();
// Write to the output stream
workBook.write(outputStream);
// 清除缓存
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
excel导入
public List<String> addBatchBlackList(MultipartFile file,
HttpServletRequest request) {
System.out.println("開始導入");
/**
* 用来记录添加数据库中不存在的人员的信息
*/
ArrayList<String> pssidList = new ArrayList<String>();
Workbook workbook = null;
try {
if (file.getOriginalFilename().toLowerCase().endsWith("xls")) {
workbook = new HSSFWorkbook(file.getInputStream());
} else if (file.getOriginalFilename().toLowerCase()
.endsWith("xlsx")) {
workbook = new XSSFWorkbook(file.getInputStream());
} else {
return null;
}
} catch (Exception e) {
e.printStackTrace();
}
List<BlackListItem> list = new ArrayList<BlackListItem>();
BlackListItem ppc = null;
// Read the Sheet
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workbook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
// 读取excel
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row != null) {
ppc = new BlackListItem();
Cell PASSID = row.getCell(0);
String passId = getValue(PASSID);
ppc.setPassId(passId);
System.out.println(ppc.getPassId());
list.add(ppc);
}
}
}
for (BlackListItem item : list) {
pssidList.add(item.getPassId());
}
return pssidList;
}
private String getValue(Cell row) {
if (row.getCellType() == row.CELL_TYPE_BOOLEAN) {
return String.valueOf(row.getBooleanCellValue());
} else if (row.getCellType() == row.CELL_TYPE_NUMERIC) {
String str = String.valueOf(row.getNumericCellValue());
String[] abc = str.split("\\.");
return abc[0];
} else {
return String.valueOf(row.getStringCellValue());
}
}