1、引入依赖
<!-- POI -->
<poi.version>3.9</poi.version>
<poi-ooxml.version>3.9</poi-ooxml.version>
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi-ooxml.version}</version>
</dependency> 2、编码
下面是读取Excel数据的代码内容，忽略业务逻辑，自己手动删除
@Resource
private UwoSpecialUserService uwoSpecialUserService;
private final static String xls = "xls";
private final static String xlsx = "xlsx";
//导入excel数据
@RequestMapping(value = "1425", method = {RequestMethod.GET, RequestMethod.POST})
public Response<Object> importFile(@RequestParam("file") MultipartFile file){
//目标：读excel，入库
List<UwoSpecialUser> uwoSpecialUserList=new ArrayList<>();
Response<Object> result = new Response<Object>();
try {
int count = 0;
String msg = "";
//技巧：平时怎么读，代码怎么写。
//1.打开工作簿(xls、xlsx格式)
Workbook workbook = getWorkBook(file);
//2.从工作簿中打开工作表
//workbook.getSheet("Sheet1");//根据名字读取工作表
Sheet sheet = workbook.getSheetAt(0);//g根据索引来读取工作表0-based physical & logical
//3.一行一行读
for (Row row : sheet) {
//第一行一般是标题，要跳过
if(row.getRowNum()==0||row.getRowNum()==1){
continue;
}
//将值封装到对象
UwoSpecialUser uwoSpecialUser=new UwoSpecialUser();
//一格一格读数据
if (row.getCell(0)!=null){
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
String specialUserName = row.getCell(0).getStringCellValue();
uwoSpecialUser.setSpecialUserName(specialUserName);
}
if (row.getCell(1)!=null){
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String phonenum = row.getCell(1).getStringCellValue();
uwoSpecialUser.setPhonenum(phonenum);
}
if (row.getCell(2)!=null){
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
String money = row.getCell(2).getStringCellValue();
if (money!=null&&!"".equals(money)){
uwoSpecialUser.setMoney(Double.valueOf(money));
}
}
if (row.getCell(3)!=null){
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
String type = row.getCell(3).getStringCellValue();
if (type!=null&&!"".equals(type)){
uwoSpecialUser.setType(Integer.valueOf(type));
}
}
if (uwoSpecialUser!=null){
if (uwoSpecialUser.getCreateTime() == null) {
uwoSpecialUser.setCreateTime(new Date());
uwoSpecialUser.setUpdateTime(uwoSpecialUser.getCreateTime());
}
//判断手机号码不为空的才执行插入数据
if (StringUtils.isNotBlank(uwoSpecialUser.getPhonenum())){
if (StringUtils.isNotBlank(uwoSpecialUser.getSpecialUserName())){
//将对象填入集合
uwoSpecialUserList.add(uwoSpecialUser);
}else {
msg += uwoSpecialUser.getPhonenum()+",";
}
}
}
}
for (UwoSpecialUser uwoSpecialUser:uwoSpecialUserList) {
try {
int i = uwoSpecialUserService.insertInfo(uwoSpecialUser);
count += i;
} catch (Exception e) {
e.printStackTrace();
msg += uwoSpecialUser.getPhonenum()+",";
}
}
if (msg!=null&&!"".equals(msg)){
msg+="该手机号码已存在或者不合法或者没有填写姓名";
result.setMsg(msg);
}
result.setData("成功上传"+count+"条数据");
} catch (Exception e) {
e.printStackTrace();
result.setData("上传失败，Excel格式有误");
result.setResponseState(ResponseState.FAILED);
}
return result;
}
//公共方法获取2003和2007的Workbook工作薄对象
public static Workbook getWorkBook(MultipartFile file) {
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if(fileName.endsWith(xls)){
//2003
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith(xlsx)){
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
LOGGER.info(e.getMessage());
}
return workbook;
}注：以上内容请忽略业务逻辑，下面是导出Excel代码内容
@RequestMapping(value = "1426", method = {RequestMethod.GET, RequestMethod.POST})
public Response<Object> download(HttpServletResponse response, UwoCompanyWithdraw uwoCompanyWithdraw) throws IOException {
//创建Excel
//创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
//建立新的sheet对象（excel的表单）
HSSFSheet sheet = wb.createSheet("特殊用户赠送金上传模板");
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(wb);
HSSFCellStyle style = this.getStyle(wb); //单元格样式对象
//在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
HSSFRow row1 = sheet.createRow(0);
//创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
HSSFCell cell = row1.createCell(0);
//设置单元格内容
cell.setCellValue("特殊用户赠送金上传模板");
cell.setCellStyle(columnTopStyle);
String[] rowsName = new String[]{"用户名(必填)","手机号码(必填)","金额(默认为0)","类型(默认为1)"};
//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, rowsName.length-1));
//在sheet里创建第二行
HSSFRow row2 = sheet.createRow(1);
//创建单元格并设置单元格内容
for (int i = 0;i < rowsName.length;i++){
HSSFCell cell1 = row2.createCell(i);
cell1.setCellValue(rowsName[i]);
cell1.setCellStyle(columnTopStyle);
}
//在sheet里创建第三行
HSSFRow rown = null;
rown = sheet.createRow(2);
HSSFCell cell0 = rown.createCell(0);
cell0.setCellValue("老王");
cell0.setCellType(Cell.CELL_TYPE_STRING);
cell0.setCellStyle(style);
HSSFCell cell1 = rown.createCell(1);
cell1.setCellValue("13456789666");
cell1.setCellType(Cell.CELL_TYPE_STRING);
cell1.setCellStyle(style);
HSSFCell cell2 = rown.createCell(2);
cell2.setCellValue("100");
cell2.setCellType(Cell.CELL_TYPE_STRING);
cell2.setCellStyle(style);
HSSFCell cell3 = rown.createCell(3);
cell3.setCellValue("1");
cell3.setCellType(Cell.CELL_TYPE_STRING);
cell3.setCellStyle(style);
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < rowsName.length; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
}
}
//输出Excel文件
OutputStream output = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename="+toUtf8String("特殊用户赠送金上传模板表.xls"));
response.setContentType("application/msexcel");
wb.write(output);
output.close();
return null;
}
//解决文件名乱码问题，可提取封装为工具类使用
public static String toUtf8String(String s){
StringBuffer sb = new StringBuffer();
for (int i=0;i<s.length();i++){
char c = s.charAt(i);
if (c >= 0 && c <= 255){sb.append(c);}
else{
byte[] b;
try { b = Character.toString(c).getBytes("utf-8");}
catch (Exception ex) {
System.out.println(ex);
b = new byte[0];
}
for (int j = 0; j < b.length; j++) {
int k = b[j];
if (k < 0) k += 256;
sb.append("%" + Integer.toHexString(k).toUpperCase());
}
}
}
return sb.toString();
}
/*
* 列头单元格样式，可提取封装为工具类使用
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)11);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/*
* 列数据信息单元格样式，可提取封装为工具类使用
*/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
552
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
