excel导入导出

excel导入流程如下：

1.在web模块下的pom文件里加入poi包的依赖：

<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>3.9</version>
     <type>jar</type>
 </dependency>

2.导入界面jsp：

<form id="importForm" method="post" enctype="multipart/form-data"> //注意加上
<fieldset>
<legend>潜在公众客户批量导入</legend>
<table class="search">
<tr>
<td align="center"></td>
<tr>
<tr>
<td>操作说明</td>
</tr>
<tr>
<td>1.点击<a href="#" id="templete">模板</a>，下载对应的导入模板
</td>
</tr>
<tr>
<td>2.请按“浏览”按钮，选择要上载的文件  <input type="file" name="attachment" width="180px" />      //注意与controller里的File对象对应
</td>
</tr>
<tr>
<td>3.点击<input type="button" value="上传" id="upload"
value="上传" />将上传文件至服务器，并进行文件导入处理
</td>
</tr>
</table>
</fieldset>
</form>

3,在controller里声明一个File对象 attachment，并生成对应的setter函数；

4.参考例子：

public void readFile() throws SysException, IOException {
if (attachment != null && attachmentFileName != null) {
try {
InputStream is = new FileInputStream(attachment);
//通过输入流构造excel文件，并将其解析封装到BudgetMVO中
HSSFWorkbook workbook = new HSSFWorkbook(is);
HSSFRichTextString jobTemp = null;
HSSFRichTextString minTemp = null;
//遍历每个指标sheet页
//int count = 0;
for (int i = 0; i < indicatorlist.size(); i ++){
HSSFSheet sheet = workbook.getSheetAt(i);
if(workbook.getNumberOfSheets() < indicatorlist.size()){
out.write("{'code':'0','msg':'导入的文件缺失Sheet页，请检查！'}");
out.close();
return;
}
for(int j = 6; j < orglist.size()+6; j++){
if(sheet.getLastRowNum() < orglist.size()+5){
out.write("{'code':'0','msg':'文件的第"+(i+1)+"个Sheet页的行数据缺失，请检查！'}");
out.close();
return;
}
for(int k = 1; k <cyclelist.size()*2+1; k++){
if(k%2==0){
if(null != sheet.getRow(j).getCell(k)){
//若单元格是数字类型，则读取数字，否则把该单元格的位置记录到错误信息里
if(sheet.getRow(j).getCell(k).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
BudgetTargetMVO mvo = new BudgetTargetMVO();
sheet.getRow(j).getCell(k).setCellType(HSSFCell.CELL_TYPE_STRING);
minTemp = sheet.getRow(j).getCell(k).getRichStringCellValue();
mvo.setJobTaget(null==jobTemp?"":jobTemp.toString());
mvo.setMinTarget(minTemp.toString());
valuelist.add(mvo);
}else{
BudgetTargetMVO mvo = new BudgetTargetMVO();
minTemp = sheet.getRow(j).getCell(k).getRichStringCellValue();
mvo.setJobTaget(null==jobTemp?"":jobTemp.toString());
mvo.setMinTarget(minTemp.toString());
valuelist.add(mvo);
}
}else{
BudgetTargetMVO mvo = new BudgetTargetMVO();
mvo.setJobTaget(null==jobTemp?"":jobTemp.toString());
mvo.setMinTarget("");
valuelist.add(mvo);
}
}else if(k%2==1){
if(null != sheet.getRow(j).getCell(k)){
//若单元格是数字类型，则读取数字，否则把该单元格的位置记录到错误信息里
if(sheet.getRow(j).getCell(k).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
sheet.getRow(j).getCell(k).setCellType (HSSFCell.CELL_TYPE_STRING);
jobTemp = sheet.getRow(j).getCell(k).getRichStringCellValue();
}else{
jobTemp = sheet.getRow(j).getCell(k).getRichStringCellValue();
}
}else{
jobTemp = null;}}}}}
model.setCycleList(cyclelist);
model.setIndicatorList(indicatorlist);
model.setValueList(valuelist);
model.setOrgList(orglist);
if(errlist.size() < 1){  //遍历excel文件，单元格无错误数据类型
SecurityManager.setSessionAttribute("model", model);
out.write("{'code':'1','msg':'导入成功！'}");
out.close();
}else{
JSONArray jo = JSONArray.fromObject(errlist);
System.out.println(jo.toString());
out.print(jo.toString());
out.close();
}
} catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
out = response.getWriter();
out.write("{'code':'0','msg':'导入失败！'}");
out.close();}}}

excel导出流程如下：

1.在web模块下的pom文件里加入poi包的依赖，如下：

<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>3.9</version>
     <type>jar</type>
 </dependency>

2.创建excel文件的原则是先生成 HSSFWorkbook 工作簿，然后是Sheet页，接着是行，最后是行里的每个单元格。

public void createExl(BudgetMVO budget) {
try{
HSSFWorkbook wb = new HSSFWorkbook();  //创建一个excel文件
for (int i = 0; i < indicatorList.size(); i++){
HSSFSheet sheet = wb.createSheet(indicatorList.get(i).getName()); //生成sheet页
for(int j = 0; j < orgList.size()+7; j++){
HSSFRow row = sheet.createRow(j); //生成行
for (int k = 0; k < cycleList.size()*2+1; k ++){
HSSFCell c = row.createCell(k); //生成单元格
if(j==5 && k > 0){
if(k%2 == 0){
c.setCellValue("保底目标");
}else if(k%2 ==1){
c.setCellValue("承包目标");
}
}
}
}
sheet.getRow(0).getCell(0).setCellValue("预算详细信息");
sheet.addMergedRegion(new CellRangeAddress(0,0,0,cycleList.size()*2)); //设置合并区域函数
//第二行
sheet.getRow(1).getCell(0).setCellValue("预算名称");
sheet.getRow(1).getCell(1).setCellValue(budget.getName());
sheet.addMergedRegion(new CellRangeAddress(1,1,1,cycleList.size()));
sheet.getRow(1).getCell(cycleList.size()+1).setCellValue("预算周期");
sheet.getRow(1).getCell(cycleList.size()+2).setCellValue(budget.getCycle());
sheet.addMergedRegion(new CellRangeAddress(1,1,1,cycleList.size()));
//第三行
sheet.getRow(2).getCell(0).setCellValue("编制时间");
sheet.getRow(2).getCell(1).setCellValue(budget.getCreateDate());
sheet.addMergedRegion(new CellRangeAddress(2,2,1,cycleList.size()));
sheet.getRow(2).getCell(cycleList.size()+1).setCellValue("发布时间");
sheet.getRow(2).getCell(cycleList.size()+2).setCellValue(budget.getIssuedDate());
sheet.addMergedRegion(new CellRangeAddress(2,2,cycleList.size()+2,cycleList.size()*2));
//第四行
sheet.getRow(3).getCell(0).setCellValue("预算描述");
sheet.getRow(3).getCell(1).setCellValue(budget.getRemark());
sheet.addMergedRegion(new CellRangeAddress(3,3,1,cycleList.size()*2));
//第五行
sheet.getRow(4).getCell(0).setCellValue("营销单元");
sheet.addMergedRegion(new CellRangeAddress(4,5,0,0));
for (int m = 0; m < cycleList.size(); m++){
sheet.getRow(4).getCell(2*m+1).setCellValue(cycleList.get(m));
sheet.addMergedRegion(new CellRangeAddress(4,4,m*2+1,m*2+2));
}
int count = 0;
//数据定位
for (int n = 6; n < 6+orgList.size(); n++){
for (int a = 0; a < cycleList.size()*2+1; a++){
if(a == 0){
sheet.getRow(n).getCell(a).setCellValue(orgList.get(n-6));
}else{
if(a%2 == 0){
sheet.getRow(n).getCell(a).setCellValue(valueList.get(count).getMinTarget());
count++;
}else if(a%2 == 1){
sheet.getRow(n).getCell(a).setCellValue(valueList.get(count).getJobTaget());
}}}}}

try {
//以流的形式把文件输出
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=budget.xls");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
} catch (IOException e) {
System.out.println(e.getLocalizedMessage());
}
} catch (Exception e) {
System.out.println(e.getMessage());}}