package com.zp.test.controller;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import com.zp.test.model.User;
import com.zp.test.service.HelloWorldService;
@Controller
public class HelloWorldController {
@Autowired
private HelloWorldService helloWorldService;
@RequestMapping("index")
public String index() {
helloWorldService.test();
return "index";
}
/**
* 表单封装数据
*
* @param user
* @return
*/
@RequestMapping("login")
public String login(@ModelAttribute User user) {
System.out.println(user.getUserName());
System.out.println(user.getPassword());
return "success";
}
@ResponseBody
@RequestMapping("upload")
public String upLoad(HttpServletRequest request,HttpServletResponse response,@RequestParam("userName") String userName,@RequestParam("password")String password,@RequestParam("file") MultipartFile mFile) throws Exception {
if(!"kpr".equals(userName)){
return "userName error !";
}else{
if(!"1234qwer".equals(password)){
return "password error !";
}
}
String targetFilePath = "G:\\excel_poi\\b.xls";
if (!mFile.isEmpty()) {
String fileName = mFile.getOriginalFilename();
System.out.println("上传文件名称是===" + fileName);
try {
OutputStream out = null;
try {
File targetFile = new File(targetFilePath);
out = new FileOutputStream(targetFile);
} catch (FileNotFoundException e1) {
e1.printStackTrace();
}
try {
File file = new File(targetFilePath+"1");
FileUtils.copyInputStreamToFile(mFile.getInputStream(), file);
InputStream is = new FileInputStream(file);
XSSFWorkbook inBook = new XSSFWorkbook(is);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("test");
for (int numSheet = 0; numSheet < inBook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = inBook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow row = xssfSheet.getRow(rowNum);
HSSFRow workrow = sheet.createRow(rowNum);
if (row != null) {
for (int i = 0; i < row.getLastCellNum(); i++) {
if (i == 3) {
String s = row.getCell(3).toString();
List<String> list = new ArrayList<String>();
String newStr = "";
for (int j = 0; j < s.length(); j = j + 3) {
String subStr = s.substring(j, j + 3);
if (!list.contains(subStr)) {
list.add(subStr);
HSSFCell newCell = workrow.createCell(list.indexOf(subStr) + 4);
newCell.setCellValue(row.getCell(s.indexOf(subStr) / 3 + 4).getRawValue());
newStr += subStr;
} else {
continue;
}
HSSFCell cell2 = workrow.createCell(2);
cell2.setCellValue(list.size());
HSSFCell cell3 = workrow.createCell(3);
cell3.setCellValue(newStr);
}
} else if (i == 0) {
HSSFCell newCell = workrow.createCell(i);
if (row.getCell(i) != null) {
newCell.setCellValue(row.getCell(i).toString());
}
} else if (i == 1) {
HSSFCell newCell = workrow.createCell(i);
if (row.getCell(i) != null) {
newCell.setCellValue(row.getCell(i).getDateCellValue());
}
}
}
}
}
}
workbook.write(out);
is.close();
out.flush();
out.close();
System.out.println("suc");
/
File outFile = new File(targetFilePath);
FileInputStream fis = new FileInputStream(outFile);
BufferedInputStream bis = new BufferedInputStream(fis);
ServletOutputStream sos = response.getOutputStream();
BufferedOutputStream bos = new BufferedOutputStream(sos);
String contentType = "application/vnd.ms-excel";//定义导出文件的格式的字符串
String recommendedName = new String(fileName.getBytes(),"iso_8859_1");//设置文件名称的编码格式
response.setContentType(contentType);//设置导出文件格式
response.setHeader("Content-Type","application/force-download");
response.setHeader("Content-Type","application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=newFile.xls");//
int bytesRead = 0;
byte[] buffer = new byte[1024];
while ((bytesRead = bis.read(buffer)) != -1) {
bos.write(buffer, 0, bytesRead);
}
bos.flush();
bos.close();
bis.close();
fis.close();
} catch (Exception e) {
System.out.println(e);
} finally {
try {
out.close();
System.out.println("close");
} catch (IOException e) {
e.printStackTrace();
}
}
}catch (Exception e){
}
// FileUtils.copyInputStreamToFile(file.getInputStream(), new File("G:\\test\\", System.currentTimeMillis() + file.getOriginalFilename()));
return "success";
}else{
return "The file can not be empty !";
}
}
}
java 读取Excel处理并在网页下载处理后的新文件
最新推荐文章于 2023-04-25 16:01:25 发布