居于poi操作EXCEL文档

                                                                获取内容

    maven pox.xml文档加入添加jar包

    <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
<exclusions>
<exclusion>
<artifactId>commons-codec</artifactId> 
<groupId>commons-codec</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>

</dependency>

导入

//接受前端上传的文件

import org.springframework.web.multipart.MultipartFile;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;

//MultipartFile 对象一般都是通过方法参数传过来的

String fileName =multipartFile .getOriginalFileName();//获取文件名

InputStream is =  multipartFile .getInputStream();//获取上传文件的输入流

//判断文件名格式是否正确

boolean isExcel2003 = fileName .matches("^.+\\.(?i)(xls)$");//判断版本是不是2003的

//fileName.matches("^.+\\.(?i)(xlsx)$")；//判断版本是不是2007的

//因为excel文档有两种规范 一种是2003版本的HSSFWoreBook,一种是2007的XSSFWorkbook的

//判断属于那种文档创建那种文档操作类

    Workbook wb = null;

if(idExcel2003){//true为2003版本的

    wb = new HSSFWordbook(is);

}    else{

    wb = new XSSFWordbook(is);

}

//根据Workbook 获取Sheet对象

//得到第一个sheet

Sheet sheet = wd.getSheetAT(0);

//得到Excel的行数

int totalRows = sheet.getPhysicalNumberofCells();

//得到Excel的列数(前提有行数)

if(totaRows >1 && sheet.geRow(0) != null){

    this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();

}

//循环Excel行数

for (int r = 1; r < totalRows; r++) {

    Row row = sheet.getRow(r);

    if(row==null){

    contiue;

    }

    for(int c = 0;c<totalCells;c++){

            Cell cell = row.getCell(c);

            if(   null != cell ){

                cell.setCellType(Cell.CELL_TYPE_STRING);   

                //接下来只要判断c的值，就是为第几列获取

                if(c=0    ){//第一列

                         String data = cell.getStringCellValue()；              

                  }

               .....

            }    

}

}

                                                        导出

//查询数据库获取到的数据List<T> lists

String fileName = "学员信息.xls"; // 文件名
String sheetName = "学员信息";// sheet名
String state = "";
String[] title = new String[] { "序号", "学生姓名", "登录账号", "性别", "密码", "账号状态", "身份证号码", "邮箱", "出生日期", "工作单位", "部门信息",

"移动电话", "毕业院校", "学历水平", "学位", "政治面貌", "所学专业", "职务", "办公电话", "干部级别", "相片" };// 标题

            //用一个二维数组代表多少行多少列

String[][] values = new String[lists.size()][];
for (int i = 0; i < lists.size(); i++) {
values[i] = new String[title.length];
// 将对象内容转换成string
Student obj = lists.get(i);
values[i][0] = i + 1 + "";
values[i][1] = obj.getStudentName();
values[i][2] = obj.getAcademicDegree();
values[i][3] = obj.getSex();
values[i][4] = obj.getPassword();


if (obj.getState() == 1) {
state = "启用";
} else {
state = "禁用";
}


values[i][5] = state;
values[i][6] = obj.getIDNumber();
values[i][7] = obj.getEmail();
values[i][8] = obj.getBirthDate();
values[i][9] = obj.getWorkUnit();
values[i][10] = obj.getDepartmental();
values[i][11] = obj.getMobilePhone();
values[i][12] = obj.getGraduationSchool();
values[i][13] = obj.getEducationalBackground();
values[i][14] = obj.getAcademicDegree();
values[i][15] = obj.getPoliticsStatus();
values[i][16] = obj.getMajor();
values[i][17] = obj.getPost();
values[i][18] = obj.getOfficePhone();
values[i][19] = obj.getCadreLevel();
values[i][20] = obj.getPhoto();
}

                //设置Excel表的样式和内容 start

                //第一步，创建一个webbook,对应一个Excel文件    

                 HSSFWorkbook wb = new HSSFWorkbook();

                    //第二步，在webbook中添加一个sheet,对应Excel文件中的sheet

                    HSSFSheet sheet = wb.createSheet(sheetName);

                    //第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制short

                    HSSFFRow row = sheet.creatRow(0);

                    //第四部，创建单元格，并设置值表头设置表头居中

                   HSSFCellStyle style = wb.createCellstyle();

                   style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式            

                  row.setHeight((short) 350);

                HSSFCell cell = null;  
       //创建标题
       for(int i=0;i<title.length;i++){
           cell = row.createCell(i);  
           cell.setCellValue(title[i]);
           cell.setCellStyle(style);  
       }
       //创建内容
       for(int i=0;i<values.length;i++){
           row = sheet.createRow(i + 1); 
           row.setHeight((short) 350);
           for(int j=0;j<values[i].length;j++){
           cell=row.createCell(j);
           cell.setCellValue(values[i][j]);
           cell.setCellStyle(style);  
           }
       }

//设置Excel表的样式内容end

//写出excel start

OutputStream os = null;
// 将文件存到指定位置

try {

        //设置 excel可写出 start

 try {  
             try {  
                  fileName = new String(fileName.getBytes(),"iso-8859-1");  
             } catch (UnsupportedEncodingException e) {  
                  // TODO Auto-generated catch block  
                  e.printStackTrace();  
             }  
             response.setContentType("application/octet-stream;charset=utf-8");  
             response.setHeader("Content-Disposition", "attachment;filename="+ fileName);  
             response.addHeader("Pargam", "no-cache");  
             response.addHeader("Cache-Control", "no-cache");  
        } catch (Exception ex) {  
             ex.printStackTrace();  

        }  

         //设置 excel可写出 end

   } 
os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}

//写出excel end