使用struts2+spring+hibernate技术,上传采用struts2的file contentType filename即可快速实现文件上传
1、POI操作Excel
①-④
2、POI操作Excel样式
样式是属于工作簿,运用于单元格;字体是属于工作簿,加载在样式中,通过样式运用于单元格
3、合并单元格对象(CellRangeAddress,起始行号,结束行号,起始列号,结束列号)
<p>
1.首先导入包</p><p>1.poi-3.10.1-20140818.jar</p><p>2.poi-ooxml-3.10.1-20140818.jar </p><p>3.poi-ooxml-schemas-3.10.1-20140818.jar</p><p>4.xmlbeans-2.6.0.jar</p><p>5.dom4j-1.6.1.jar</p><p>
</p><p>2.编写后台代码块</p>
导出excel
public void exportExcel() {
// 获取导出的数据
try {
List<User> userlist = userService.findObjects();
// 输出导出内容
HttpServletResponse response = ServletActionContext.getResponse();
response.setContentType("application/x-execl");
response.setHeader("Content-Disposition", "attachment;filename="
+ new String("用户列表.xls".getBytes(), "ISO-8859-1")); //设置浏览器可识别的编码
ServletOutputStream outputStream = response.getOutputStream();
userService.exportExcel(userlist,outputStream);
if(outputStream!=null){
outputStream.close();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//导入excel
public String importExcel(){
//先判断是否是excel文件
try {
if(userExcel!=null){
if(userExcelFileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){
//上面判断是否是excel文件,如过是的话,就导入
//
userService.importExcel(userExcel,userExcelFileName);
}
}
} catch (Exception e) {
e.printStackTrace();
}
//判断行数是否是大于二的
return "list";
}
package cn.itcast.core.util;
import java.util.List;
import javax.servlet.ServletOutputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import cn.itcast.nsfw.user.entity.User;
public class UserExcelOutPut {
public static void userExcelOutPut(List<User> userlist,
ServletOutputStream outputStream){
try {
// 创建工作簿
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
// 合并表头
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
// 创建工作表
HSSFSheet sheet = hssfWorkbook.createSheet("用户列表");
// 设置工作表的表头
sheet.addMergedRegion(cra);
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell0 = row0.createCell(0);
cell0.setCellValue("用户列表");
//统一设置列宽
sheet.setDefaultColumnWidth(18);
// 创建样式 标题的
HSSFCellStyle cellStyleBT = createCellStyleAndFont(hssfWorkbook,
(short) 18);
cell0.setCellStyle(cellStyleBT);
createCellStyleAndFont(hssfWorkbook, (short) 18);
// 第二行固定列名
// 设置第二个列名
HSSFCellStyle cellStyleTwo = createCellStyleAndFont(hssfWorkbook,
(short) 16);
String[] names = { "用户名", "账号", "所属部门", "性别", "电子邮箱" };
HSSFRow row1 = sheet.createRow(1);
for (int i = 0; i < names.length; i++) {
HSSFCell celli = row1.createCell(i);
celli.setCellValue(names[i]);
celli.setCellStyle(cellStyleTwo);
}
// 设置数据
if (userlist.size() > 0) {
for (int j = 0; j < userlist.size(); j++) {
// 从第三行开始设置
HSSFRow rowj = sheet.createRow(j + 2);
// 填充数据
HSSFCell cellContent0 = rowj.createCell(0);
cellContent0.setCellValue(userlist.get(j).getName());
HSSFCell cell1 = rowj.createCell(1);
cell1.setCellValue(userlist.get(j).getAccount());
HSSFCell cell2 = rowj.createCell(2);
cell2.setCellValue(userlist.get(j).getDept());
HSSFCell cell3 = rowj.createCell(3);
cell3.setCellValue(userlist.get(j).isGender()?"男":"女");
HSSFCell cell4 = rowj.createCell(4);
cell4.setCellValue(userlist.get(j).getEmail());
}
}
hssfWorkbook.write(outputStream);
//关闭工作簿i
hssfWorkbook.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private static HSSFCellStyle createCellStyleAndFont(HSSFWorkbook hssfWorkbook,
short fontnum) {
HSSFCellStyle cellstyle = hssfWorkbook.createCellStyle();
cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont font = hssfWorkbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints(fontnum);
cellstyle.setFont(font);
return cellstyle;
}
}
导入excel
public void importExcel(File userExcel, String userExcelFileName) {
try {
boolean is03 = false;
is03 =userExcelFileName.matches("^.+\\.(?i)(xls)$");
FileInputStream fis = new FileInputStream(userExcel);
Workbook wb = is03 ? new HSSFWorkbook(fis):new XSSFWorkbook(fis);
Sheet sheet = wb.getSheetAt(0);
if(sheet.getPhysicalNumberOfRows()>2){
User user = null;
for(int k = 2;k<sheet.getPhysicalNumberOfRows();k++){
//4、读取单元格
Row row = sheet.getRow(k);
user = new User();
//用户名
Cell cell0 = row.getCell(0);
user.setName(cell0.getStringCellValue());
//帐号
Cell cell1 = row.getCell(1);
user.setAccount(cell1.getStringCellValue());
//所属部门
Cell cell2 = row.getCell(2);
user.setDept(cell2.getStringCellValue());
//性别
Cell cell3 = row.getCell(3);
user.setGender(cell3.getStringCellValue().equals("男"));
//手机号
String mobile = "";
Cell cell4 = row.getCell(4);
try {
mobile = cell4.getStringCellValue();
} catch (Exception e) {
double dMobile = cell4.getNumericCellValue();
mobile = BigDecimal.valueOf(dMobile).toString();
}
user.setMobile(mobile);
//电子邮箱
Cell cell5 = row.getCell(5);
user.setEmail(cell5.getStringCellValue());
//生日
Cell cell6 = row.getCell(6);
if(cell6.getDateCellValue() != null){
user.setBirthday(cell6.getDateCellValue());
}
//默认用户密码为 123456
user.setPassword("123456");
//默认用户状态为 有效
user.setState(User.USER_STATE_VALID);
//5、保存用户
save(user);
}
}
wb.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}