/**
* @方法名称: parseExcel
* @功能描述: 解析excel文件 按照键值对形式解析
* @创建时间:2016-11-1 上午11:01:08
* @param path
* @return
* @throws Exception List<Map<String,String>>
*/
public static List<Map<String,String>> parseExcel(String path) throws Exception {
// 文件流指向excel文件
FileInputStream fin = new FileInputStream(path);
// 创建excel表格
XSSFWorkbook workbook = new XSSFWorkbook(fin);
List<Map<String,String>> listMap=new ArrayList<Map<String,String>>();
try {
// 循环遍历选项卡sheet
for(int sheetNum=0;sheetNum<workbook.getNumberOfSheets();sheetNum++){
Map<Integer,String> cellName=new HashMap<Integer,String>();
XSSFSheet sheet = workbook.getSheetAt(sheetNum);
// 对应excel的行
XSSFRow row = null;
// 对应excel的列
XSSFCell cell = null;
// 得到excel的总记录条数,用于循环插入
int totalRow = sheet.getLastRowNum();
if(totalRow>0){
//获取第一行作为键值
row=sheet.getRow(0);
int cellNumFirst=row.getFirstCellNum();
int cellNumLast=row.getLastCellNum();
for(int i=cellNumFirst;i<cellNumLast;i++){
cell=row.getCell(i);
cellName.put(i, cell.toString());
}
//循环遍历除第一行之外的值并存储为键值对形式
for (int i = 1; i <= totalRow; i++) {
Map<String,String> rowInfo=new HashMap<String,String>();
row = sheet.getRow(i);
for(int y=cellNumFirst;y<cellNumLast;y++){
cell = row.getCell(y);
rowInfo.put(cellName.get(y), cell==null?"":cell.toString());
}
listMap.add(rowInfo);
}
}
}
logger.info("解析"+path+"文件成功");
} catch (Exception e) {
logger.error("解析"+path+"文件出错 错误原因:"+e.getMessage());
return null;
}finally{
UploadFileController.deleteFile(path);
}
return listMap;
}
/**
* @方法名称: buildExcelDocument
* @功能描述:导出excel文件 根据每页显示条数分选项卡
* @创建时间:2016-12-22 上午10:36:26
* @param model
* @param workbook
* @param request
* @param response
* @throws Exception
* @see org.springframework.web.servlet.view.document.AbstractExcelView#buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
*/
@SuppressWarnings("unchecked")
@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook,
HttpServletRequest request, HttpServletResponse response) throws Exception {
String filename = request.getAttribute("fileName")==null?"infomation":request.getAttribute("fileName").toString();
HSSFSheet sheet; //选项卡
HSSFCell cell; //单元格
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
List<List<String>> content = (List<List<String>>) model.get("content");
int pageCount=2;//每个选项卡的条数
int varCount = content.size();
int mod=varCount%pageCount;
int page=varCount/pageCount;
if(mod!=0){
page=(varCount/pageCount)+1;
}
for(int p=0;p<page;p++){//选项卡遍历
sheet = workbook.createSheet("sheet"+(p+1));
List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short)11);
headerStyle.setFont(headerFont);
short width = 20,height=25*20;
sheet.setDefaultColumnWidth(width);
for(int i=0; i<len; i++){ //设置标题
String title = titles.get(i);
cell = getCell(sheet, 0, i);
cell.setCellStyle(headerStyle);
setText(cell,title);
}
sheet.getRow(0).setHeight(height);
HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
int startIndex=p*pageCount;
int endIndex=(p+1)*pageCount;
if(endIndex>=varCount){
endIndex=varCount;
}
List<List<String>> sheetContent=content.subList(startIndex, endIndex);
for(int i=0; i<pageCount; i++){ //插入行
if(i<sheetContent.size()){
List<String> vpd=sheetContent.get(i);
for(int j=0;j<len;j++){ //插入单元格
cell = getCell(sheet, i+1, j);
cell.setCellStyle(contentStyle);
setText(cell,vpd.get(j));
}
}
}
}
}
* @方法名称: parseExcel
* @功能描述: 解析excel文件 按照键值对形式解析
* @创建时间:2016-11-1 上午11:01:08
* @param path
* @return
* @throws Exception List<Map<String,String>>
*/
public static List<Map<String,String>> parseExcel(String path) throws Exception {
// 文件流指向excel文件
FileInputStream fin = new FileInputStream(path);
// 创建excel表格
XSSFWorkbook workbook = new XSSFWorkbook(fin);
List<Map<String,String>> listMap=new ArrayList<Map<String,String>>();
try {
// 循环遍历选项卡sheet
for(int sheetNum=0;sheetNum<workbook.getNumberOfSheets();sheetNum++){
Map<Integer,String> cellName=new HashMap<Integer,String>();
XSSFSheet sheet = workbook.getSheetAt(sheetNum);
// 对应excel的行
XSSFRow row = null;
// 对应excel的列
XSSFCell cell = null;
// 得到excel的总记录条数,用于循环插入
int totalRow = sheet.getLastRowNum();
if(totalRow>0){
//获取第一行作为键值
row=sheet.getRow(0);
int cellNumFirst=row.getFirstCellNum();
int cellNumLast=row.getLastCellNum();
for(int i=cellNumFirst;i<cellNumLast;i++){
cell=row.getCell(i);
cellName.put(i, cell.toString());
}
//循环遍历除第一行之外的值并存储为键值对形式
for (int i = 1; i <= totalRow; i++) {
Map<String,String> rowInfo=new HashMap<String,String>();
row = sheet.getRow(i);
for(int y=cellNumFirst;y<cellNumLast;y++){
cell = row.getCell(y);
rowInfo.put(cellName.get(y), cell==null?"":cell.toString());
}
listMap.add(rowInfo);
}
}
}
logger.info("解析"+path+"文件成功");
} catch (Exception e) {
logger.error("解析"+path+"文件出错 错误原因:"+e.getMessage());
return null;
}finally{
UploadFileController.deleteFile(path);
}
return listMap;
}
/**
* @方法名称: buildExcelDocument
* @功能描述:导出excel文件 根据每页显示条数分选项卡
* @创建时间:2016-12-22 上午10:36:26
* @param model
* @param workbook
* @param request
* @param response
* @throws Exception
* @see org.springframework.web.servlet.view.document.AbstractExcelView#buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
*/
@SuppressWarnings("unchecked")
@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook,
HttpServletRequest request, HttpServletResponse response) throws Exception {
String filename = request.getAttribute("fileName")==null?"infomation":request.getAttribute("fileName").toString();
HSSFSheet sheet; //选项卡
HSSFCell cell; //单元格
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
List<List<String>> content = (List<List<String>>) model.get("content");
int pageCount=2;//每个选项卡的条数
int varCount = content.size();
int mod=varCount%pageCount;
int page=varCount/pageCount;
if(mod!=0){
page=(varCount/pageCount)+1;
}
for(int p=0;p<page;p++){//选项卡遍历
sheet = workbook.createSheet("sheet"+(p+1));
List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short)11);
headerStyle.setFont(headerFont);
short width = 20,height=25*20;
sheet.setDefaultColumnWidth(width);
for(int i=0; i<len; i++){ //设置标题
String title = titles.get(i);
cell = getCell(sheet, 0, i);
cell.setCellStyle(headerStyle);
setText(cell,title);
}
sheet.getRow(0).setHeight(height);
HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
int startIndex=p*pageCount;
int endIndex=(p+1)*pageCount;
if(endIndex>=varCount){
endIndex=varCount;
}
List<List<String>> sheetContent=content.subList(startIndex, endIndex);
for(int i=0; i<pageCount; i++){ //插入行
if(i<sheetContent.size()){
List<String> vpd=sheetContent.get(i);
for(int j=0;j<len;j++){ //插入单元格
cell = getCell(sheet, i+1, j);
cell.setCellStyle(contentStyle);
setText(cell,vpd.get(j));
}
}
}
}
}