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) {
("Export parsing started, fileName:{}",data.getFileName());
try {
//Instantiating the HSSFWorkbook
HSSFWorkbook workbook = new HSSFWorkbook();
//Create an Excel sheet with the name of the sheet as a parameter.
HSSFSheet sheet = ("sheet");
//Setting the table header
setTitle(workbook, sheet, data.getHead());
//Setting cells and assigning values
setData(sheet, data.getData());
//Setting up browser downloads
setBrowser(response, workbook, data.getFileName());
("Export parsed successfully!");
} catch (Exception e) {
("Export parsing failed!");
();
}
}
private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
try {
HSSFRow row = (0);
//To set the column width, the second parameter of setColumnWidth should be multiplied by256The unit of this parameter is1/256character width
for (int i = 0; i < str.length; i++) {
(i, 15 * 256);
}
//Set to center and bold, formatting time formatting
HSSFCellStyle style = ();
HSSFFont font = ();
(true);
(font);
(("m/d/yy h:mm"));
//Creating Header Names
HSSFCell cell;
for (int j = 0; j <str.length; j++) {
cell = (j);
(str[j]);
(style);
}
} catch (Exception e) {
("Failed to set table header on export!");
();
}
}
/**
* :: Method name: 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++;
}
("Form assignment successful!");
}catch (Exception e){
("Form assignment failed!");
();
}
}
/**
* :: Method name: setBrowser
* :: Functionality: download using a browser
* Description:
* :: Created by: TYP
* Created: 2018/10/19 16:20
* Modifier:
* :: Revise the description:
* :: Modification time:
*/
private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
try {
//Empty response
response.reset();
//Set the Header of the response
("Content-Disposition", "attachment;filename=" + fileName);
OutputStream os = new BufferedOutputStream(());
("application/-excel;charset=gb2312");
//Writing excel to an output stream
workbook.write(os);
();
os.close();
("Setting the browser to download successfully!");
} catch (Exception e) {
("Setting the browser to download failed!");
();
}
}
/**
* :: Method name: importExcel
* :: Function: import
*/
public static List<Object[]> importExcel(String fileName) {
("Import parsing started, fileName:{}",fileName);
try {
List<Object[]> list = new ArrayList<>();
InputStream inputStream = new FileInputStream(fileName);
Workbook workbook = (inputStream);
Sheet sheet = (0);
//Get the number of rows in the sheet
int rows = ();
for (int i = 0; i < rows; i++) {
//Filter table header rows
if (i == 0) {
continue;
}
//Get the current row's data
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);
}
("Import file parsed successfully!");
return list;
}catch (Exception e){
("Import file parsing failed!");
();
}
return null;
}
//Test Import
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) {
();
}
}
}