该篇文章使用poi3.5的版本
需要导入包如下
execl导入
/**
* execl 数据导入
* @author 小涂
* @title : import_excel
* @date Aug 21, 2013 1:04:42 PM
* @return String
*/
public static String import_excel(){
String id=null;
String name=null;
String sex=null;
String Dormitory=null;
String Sept=null;
Workbook workbook = null;
int k=0;
int flag = 0; //指示指针所访问的位置
String path="C:/Users/X/Desktop/212.xls";//获取文件的路径
try {
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
// 循环工作表Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
// 循环列Cell
// "学号","姓名","性别","寝室号","所在系"};
for (int cellNum = 0; cellNum <=4; cellNum++) {
HSSFCell xh = hssfRow.getCell(cellNum);
if (xh == null) {
continue;
}
System.out.print(getValue(xh)+"\t");
}
System.out.print("\n");
}
}
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return null;
}
/**
* 得到Excel表中的值
*
* @param hssfCell
* Excel中的每一个格子
* @return Excel中每一个格子中的值
*/
@SuppressWarnings("static-access")
private static String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
// 返回布尔类型的值
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
// 返回数值类型的值
return String.valueOf(hssfCell.getNumericCellValue());
} else {
// 返回字符串类型的值
return String.valueOf(hssfCell.getStringCellValue());
}
}
导出execl 文件
/**
* 导出execl 文件
* @author 小涂
* @title : extport
* @date Aug 21, 2013 12:27:38 PM
* @return String
*/
public String extport(){
List studentList=new ArrayList<Student>();//学生LIst
for(int i=0;i<10;i++)
{ Student student=new Student();//学生对象
student.setStudentId("200908110"+i);
student.setStudentName("杨波"+i);
student.setStudentSex("男");
student.setStudentDormitory("14-20"+i);
student.setStudentSept("软件工程系");
studentList.add(student);
}
String []tableHeader={"学号","姓名","性别","寝室号","所在系"};
short cellNumber=(short)tableHeader.length;//表的列数
HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个excel
HSSFCell cell = null; //Excel的列
HSSFRow row = null; //Excel的行
HSSFCellStyle style = workbook.createCellStyle(); //设置表头的类型
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle style1 = workbook.createCellStyle(); //设置数据类型
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont(); //设置字体
HSSFSheet sheet = workbook.createSheet("sheet1"); //创建一个sheet
HSSFHeader header = sheet.getHeader();//设置sheet的头
try {
if(studentList.size() < 1 ){
header.setCenter("查无资料");
}else{
header.setCenter("学生表");
row = sheet.createRow(0);
row.setHeight((short)400);
for(int k = 0;k < cellNumber;k++){
cell = row.createCell(k);//创建第0行第k列
cell.setCellValue(tableHeader[k]);//设置第0行第k列的值
sheet.setColumnWidth(k,8000);//设置列的宽度
font.setColor(HSSFFont.COLOR_NORMAL); // 设置单元格字体的颜色.
font.setFontHeight((short)350); //设置单元字体高度
style1.setFont(font);//设置字体风格
cell.setCellStyle(style1);
}
for(int i = 0 ;i < studentList.size() ;i++){
Student student1 = (Student)studentList.get(i);//获取student对象
row = sheet.createRow((short) (i + 1));//创建第i+1行
row.setHeight((short)400);//设置行高
if(student1.getStudentId() != null){
cell = row.createCell(0);//创建第i+1行第0列
cell.setCellValue(student1.getStudentId());//设置第i+1行第0列的值
cell.setCellStyle(style);//设置风格
}
if(student1.getStudentName() != null){
cell = row.createCell(1); //创建第i+1行第1列
cell.setCellValue(student1.getStudentName());//设置第i+1行第1列的值
cell.setCellStyle(style); //设置风格
}
//由于下面的和上面的基本相同，就不加注释了
if(student1.getStudentSex() != null){
cell = row.createCell(2);
cell.setCellValue(student1.getStudentSex());
cell.setCellStyle(style);
}
if(student1.getStudentDormitory()!= null){
cell = row.createCell(3);
cell.setCellValue(student1.getStudentDormitory());
cell.setCellStyle(style);
}
if(student1.getStudentSept() != null){
cell = row.createCell(4);
cell.setCellValue(student1.getStudentSept());
cell.setCellStyle(style);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
// HttpServletResponse response = null;//创建一个HttpServletResponse对象
OutputStream out = null;
try {
out = new FileOutputStream(new File("C:/Users/X/Desktop/212.xls"));
//response = ServletActionContext.getResponse();//初始化HttpServletResponse对象
//out = response.getOutputStream();//
} catch (FileNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
// String headerStr ="student学生";
// headerStr =new String(headerStr.getBytes("gb2312"), "ISO8859-1");//headerString为中文时转码
// response.setHeader("Content-disposition","attachment; filename="+ headerStr+".xls");//filename是下载的xls的名，建议最好用英文
// response.setContentType("application/msexcel;charset=UTF-8");//设置类型
// response.setHeader("Pragma","No-cache");//设置头
// response.setHeader("Cache-Control","no-cache");//设置头
// response.setDateHeader("Expires", 0);//设置日期头
workbook.write(out);
out.flush();
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}finally{
try{
if(out!=null){
out.close();
}
}catch(IOException e){
e.printStackTrace();
}
}
return null;
}
如果你是在web应用是使用请将Response 和out 的代码打开
测试结果
导出文件
导入数据
