package .springboot_poi.util;
import .springboot_poi.;
import .springboot_poi.;
import 44j;
import .*;
import .*;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@Slf4j
public class ExcelUtil {
public static void exportExcel(HttpServletResponse response, ExcelData data) {
("导出解析开始，fileName:{}",data.getFileName());
try {
//实例化HSSFWorkbook
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个Excel表单，参数为sheet的名字
HSSFSheet sheet = ("sheet");
//设置表头
setTitle(workbook, sheet, data.getHead());
//设置单元格并赋值
setData(sheet, data.getData());
//设置浏览器下载
setBrowser(response, workbook, data.getFileName());
("导出解析成功!");
} catch (Exception e) {
("导出解析失败!");
();
}
}
private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
try {
HSSFRow row = (0);
//设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
for (int i = 0; i < str.length; i++) {
(i, 15 * 256);
}
//设置为居中加粗,格式化时间格式
HSSFCellStyle style = ();
HSSFFont font = ();
(true);
(font);
(("m/d/yy h:mm"));
//创建表头名称
HSSFCell cell;
for (int j = 0; j <str.length; j++) {
cell = (j);
(str[j]);
(style);
}
} catch (Exception e) {
("导出时设置表头失败！");
();
}
}
/**
* 方法名：setData
*/
private static void setData(HSSFSheet sheet, List<String[]> data) {
try{
int rowNum = 1;
for (int i = 0; i <data.size(); i++) {
HSSFRow row = (rowNum);
for (int j = 0; j < data.get(i).length; j++) {
(j).setCellValue(data.get(i)[j]);
}
rowNum++;
}
("表格赋值成功！");
}catch (Exception e){
("表格赋值失败！");
();
}
}
/**
* 方法名：setBrowser
* 功能：使用浏览器下载
* 描述：
* 创建人：typ
* 创建时间：2018/10/19 16:20
* 修改人：
* 修改描述：
* 修改时间：
*/
private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
try {
//清空response
response.reset();
//设置response的Header
("Content-Disposition", "attachment;filename=" + fileName);
OutputStream os = new BufferedOutputStream(());
("application/-excel;charset=gb2312");
//将excel写入到输出流中
workbook.write(os);
();
os.close();
("设置浏览器下载成功！");
} catch (Exception e) {
("设置浏览器下载失败！");
();
}
}
/**
* 方法名：importExcel
* 功能：导入
*/
public static List<Object[]> importExcel(String fileName) {
("导入解析开始，fileName:{}",fileName);
try {
List<Object[]> list = new ArrayList<>();
InputStream inputStream = new FileInputStream(fileName);
Workbook workbook = (inputStream);
Sheet sheet = (0);
//获取sheet的行数
int rows = ();
for (int i = 0; i < rows; i++) {
//过滤表头行
if (i == 0) {
continue;
}
//获取当前行的数据
Row row = (i);
Object[] objects = new Object[()];
int index = 0;
for (Cell cell : row) {
if (().equals(CellType.NUMERIC)) {
objects[index] = (int) ();
}
if (().equals(CellType.STRING)) {
objects[index] = ();
}
if (().equals(CellType.BOOLEAN)) {
objects[index] = ();
}
if (().equals(CellType.ERROR)) {
objects[index] = ();
}
index++;
}
list.add(objects);
}
("导入文件解析成功！");
return list;
}catch (Exception e){
("导入文件解析失败！");
();
}
return null;
}
//测试导入
public static void main(String[] args) {
try {
String fileName = "D:/";
List<Object[]> list = importExcel(fileName);
for (int i = 0; i <list.size(); i++) {
Computerroom computerroom = new Computerroom();
// ((Integer) list.get(i)[0]);
((String) list.get(i)[0]);
((String) list.get(i)[1]);
((String) list.get(i)[2]);
((String) list.get(i)[3]);
(());
}
} catch (Exception e) {
();
}
}
}