import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ReadWriteExcelFile {
private static Properties props = new Properties();
static {
try {
InputStream is = ReadWriteExcelFile.class.getClassLoader().getResourceAsStream("META-INF/ExcelHeader.properties");
props.load(is);
} catch (Exception e) {
e.printStackTrace();
}
}
public static String getValue(String key) {
String value = "";
if (props.containsKey(key)) {
value = props.getProperty(key, "");
}
return value;
}
private final static Logger logger = LoggerFactory.getLogger(ReadWriteExcelFile.class);
@SuppressWarnings({ "resource", "rawtypes" })
public static void readXLSFile() throws IOException
{
InputStream ExcelFileToRead = new FileInputStream("E:/source/Test.xls");
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
HSSFSheet sheet=wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
Iterator rows = sheet.rowIterator();
while (rows.hasNext())
{
row=(HSSFRow) rows.next();
Iterator cells = row.cellIterator();
while (cells.hasNext())
{
cell=(HSSFCell) cells.next();
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue()+" ");
}
else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
{
System.out.print(cell.getNumericCellValue()+" ");
}
else
{
//U Can Handel Boolean, Formula, Errors
}
}
System.out.println();
}
}
public static void writeXLSFile(List<? extends Recording> records) throws IOException{
//String directory=getValue("excel.file.directory");
String directory="E:/source";
Date d = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String date = sdf.format(d);
Recording record=records.get(0);
Class<? extends Recording> cls=record.getClass();
String className=cls.getCanonicalName();
String[] nameAlias=className.split("\\.");
String excelFileName=directory+File.separator+nameAlias[nameAlias.length-1]+date+".xls";
writeXLSFile(records,excelFileName);
}
@SuppressWarnings("resource")
public static void writeXLSFile(List<? extends Recording> records,String excelFileName) throws IOException{
//String excelFileName = "E:/source/Test.xls";
String sheetName = "Sheet1";//name of sheet
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName) ;
Recording record;
//excel header
HSSFRow row = sheet.createRow(0);
record=records.get(0);
Class<? extends Recording> cls=record.getClass();
String className=cls.getCanonicalName();
String[] nameAlias=className.split("\\.");
Field[] fields=cls.getDeclaredFields();
//去除serialVersionUID列,
List<Field> fieldsNoSer=new ArrayList<Field>();
for(int i=0;i<fields.length;i++){
String fieldName=fields[i].getName();
if(fieldName.equalsIgnoreCase("serialVersionUID")){
continue;
}else{
fieldsNoSer.add(fields[i]);
}
}
for(int i=0;i<fieldsNoSer.size();i++){
HSSFCell cell = row.createCell(i);
String fieldName=fieldsNoSer.get(i).getName();
cell.setCellValue(getValue(nameAlias[nameAlias.length-1]+"."+fieldName));
}
//iterating r number of rows
for (int r=0;r < records.size(); r++ )
{
row = sheet.createRow(r+1);
record=records.get(r);
//table content
for (int c=0;c < fieldsNoSer.size(); c++ )
{
HSSFCell cell = row.createCell(c);
//加header,方法总变量的首字母大写
String fieldName=fieldsNoSer.get(c).getName();
try {
Method method=cls.getDeclaredMethod("get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1));
Object ret=method.invoke(record);
if(null!=ret){
cell.setCellValue(method.invoke(record).toString());
}
} catch (Exception e) {
logger.info("write xls error,please check it");
}
}
}
FileOutputStream fileOut = new FileOutputStream(excelFileName);
//write this workbook to an Outputstream.
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}
@SuppressWarnings({ "resource", "unused", "rawtypes" })
public static void readXLSXFile() throws IOException
{
InputStream ExcelFileToRead = new FileInputStream("E:/source/Test1.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
XSSFWorkbook test = new XSSFWorkbook();
XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
Iterator rows = sheet.rowIterator();
while (rows.hasNext())
{
row=(XSSFRow) rows.next();
Iterator cells = row.cellIterator();
while (cells.hasNext())
{
cell=(XSSFCell) cells.next();
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue()+" ");
}
else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
{
System.out.print(cell.getNumericCellValue()+" ");
}
else
{
//U Can Handel Boolean, Formula, Errors
}
}
System.out.println();
}
}
@SuppressWarnings("resource")
public static void writeXLSXFile() throws IOException {
String excelFileName = "E:/source/Test1.xlsx";//name of excel file
String sheetName = "Sheet1";//name of sheet
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(sheetName) ;
//iterating r number of rows
for (int r=0;r < 5; r++ )
{
XSSFRow row = sheet.createRow(r);
//iterating c number of columns
for (int c=0;c < 5; c++ )
{
XSSFCell cell = row.createCell(c);
cell.setCellValue("Cell "+r+" "+c);
}
}
FileOutputStream fileOut = new FileOutputStream(excelFileName);
//write this workbook to an Outputstream.
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}
public static void main(String[] args) throws IOException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
List<Log> logs=new ArrayList<Log>();
for(int i=1;i<2;i++){
Log log=new Log();
log.setId(Long.parseLong(""+(i+6)));
log.setUserId(Long.parseLong(""+i));
log.setUserName("www"+i);
logs.add(log);
}
writeXLSFile(logs,"E:/source/aa.xls");
}
