#ToolClass

[simplexianpo]

Java : Excel tool class

Maven import package:

  <dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>3.17-beta1</version>

</dependency>

 Code:

public class ExcelUtil {

private static int sheetSize = 5000;

/**

* @throws Exception

* @Title: listToExcel

* @Description: Write entity classes out to excel with io stream

* @param data List of data to be exported (data source)

* @param out excel: The destination to which the file will be output

* @param fields :There are column names and Chinese names corresponding to each attribute

* @return void : Return type

* @throws

*/

public static <T> void listToExcel(List<T> data,OutputStream out,Map<String,String> fields) throws Exception{

if(data==null||data.size()==0){

throw new Exception("Please pass in data for data export");

}

//xls stores 65,536 sheet at most

HSSFWorkbook workBook = new HSSFWorkbook();

//Calculate how many sheet are needed to store

int sheetNum  = data.size()/sheetSize;

if(data.size()%sheetSize!=0){

sheetNum++;

}

//Two arrays are used to store field names and Chinese names respectively

String[] fieldNames = new String[fields.size()];

String[] chinaNames = new String[fields.size()];

int count = 0;

for(Entry<String,String> entry : fields.entrySet()){

//Entry Map's internal class map < attribute name, Chinese name)

String fieldName = entry.getKey();

String chinaName = entry.getValue();

fieldNames[count]=fieldName;

chinaNames[count] = chinaName;

count++;

}

// padding data

for(int i = 0 ; i < sheetNum;i++){

int rowNum = 0; // Start with the first line

HSSFSheet sheet = workBook.createSheet();

//What is the subscript of the data that each sheet should store 0-4999 5000-9999 10000-14999

//sheet.addMergedRegion(CellRangeAddress.valueOf("$A1:"))

int start =i*sheetSize , end = (i+1)*sheetSize-1>data.size()?data.size():(i+1)*sheetSize-1;

//The first row of column names

HSSFRow row = sheet.createRow(rowNum);

for(int j = 0 ; j < chinaNames.length ; j++){

HSSFCell cell = row.createCell(j);

cell.setCellValue(chinaNames[j]);

}

rowNum++; // Move to the next line

// Fill in the data section

for(int index=start ; index<end;index++){

T item  = data.get(index);

row = sheet.createRow(rowNum);

rowNum++;

for(int j = 0; j < chinaNames.length ; j++){

//Obtain the corresponding value through reflection mechanism

Field field = item.getClass().getDeclaredField(fieldNames[j]);

field.setAccessible(true);

//Through this attribute object, get the attribute value in the specific item

Object o = field.get(item);

String value = o==null?"":o.toString();

HSSFCell cell = row.createCell(j);

cell.setCellValue(value);

}

}

}

workBook.write(out);

}

/**

*

* @Title: excelToList

* @Description: Encapsulate data in excel into entity classes

* @param file

* @param startRow

* @param fields

* @return    Setup file

* @return List<T>    Return type

* @throws

*/

public static <T> List<T> excelToList(Class<T> clazz,InputStream file,int startRow,String[] fields)throws Exception{

HSSFWorkbook workBook = new HSSFWorkbook(file);

List<T> data = new ArrayList<T>();

//for(HSSFSheet  sheet : workBook.iterator())

for(int i = 0; i < workBook.getNumberOfSheets() ;i++){

HSSFSheet sheet = workBook.getSheetAt(i);

if(sheet==null){

continue;

}

// Circular row

for(int j = startRow ; j <=sheet.getLastRowNum();j++){

HSSFRow row = sheet.getRow(j);

if(row==null){

continue;

}

T  item = clazz.newInstance();

for(int k = 0;k < fields.length;k++){

Field field = clazz.getDeclaredField(fields[k]);

field.setAccessible(true);

try{

String value = row.getCell(k).getStringCellValue();

field.set(item,row.getCell(k).getStringCellValue());

}catch(Exception ex){

//ex.printStackTrace();

System.out.println("Failed to set string value. Set it numerically!");

field.set(item,Integer.valueOf(row.getCell(k).getStringCellValue()));

}

}

data.add(item);

}

}

return data;

}

｝