导入
private static void copy(File src, File dst) {
try {
InputStream in = null ;
OutputStream out = null ;
try {
in = new BufferedInputStream( new FileInputStream(src), BUFFER_SIZE);
out = new BufferedOutputStream( new FileOutputStream(dst), BUFFER_SIZE);
byte [] buffer = new byte [BUFFER_SIZE];
while (in.read(buffer) > 0 ) {
out.write(buffer);
}
} finally {
if ( null != in) {
in.close();
}
if ( null != out) {
out.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public String execute() {
imageFileName = new Date().getTime()+getExtention(fileName);
File imageFile = new File(ServletActionContext.getServletContext().getRealPath( "/UploadImages" ) + "/" + imageFileName);
copy(myFile, imageFile);
File f = imageFile; // 获得文件对象
if (f.exists()) {
// 如果文件存在建立流读取文件
try {
InputStream fis = new FileInputStream(f); // 建立字节流
// 如何取得Excel的操作对象
POIFSFileSystem poifs = new POIFSFileSystem(fis); // 建立套接流
// HSSFWorkbook 对象,是我们最想得到的对象。
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifs); /*
* 将Excl文件转换为一个对象
* HSSFWorkbook
*/
List retList = new ArrayList();//
List<Model> ModelList = new ArrayList<Model>();
System.out.println("此文件中表的数量是: "
+ hssfWorkbook.getNumberOfSheets());
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0); // 获取Excel中sheet页的对象
System.out.println("表的对象是 : " + hssfSheet);
// wb.getNumberOfSheets():取得sheet的数目
for (int h = 0; h < hssfWorkbook.getNumberOfSheets(); ++h) {
System.out.println("sheet表的数量是:"
+ hssfWorkbook.getNumberOfSheets());
List list = new ArrayList();
HSSFSheet sheet = hssfWorkbook.getSheetAt(h); // 根据h取得sheet对象,有了Sheet就相当于取得了一张表一样。
int rowcount = sheet.getLastRowNum(); // 取得有效的行数(以0开始计数的)
rowcount++;
System.out.print("第" + h + "张表 : 行数= " + rowcount);
int colcount = 0;
int q=1;
for (int i = 0; i < rowcount; ++i) {
HSSFRow row = sheet.getRow(i); // 根据index取得行对象,有了行对象,就可以取得每一个单元对象
if (row == null)
continue;
if (colcount == 0) {
// 知道一个行有多少个单元
colcount = row.getLastCellNum();
System.out.println(", 列数= " + colcount);
}
String[] fieldValue = new String[colcount];
if (i > 0) {
String[] strs = new String[colcount];
for (int j = 0; j < colcount; ++j) {
// 取得一个单元对象
HSSFCell cell = row.getCell(j);
// fieldValue[j] = getCellStringValue(cell);
if (cell != null) {
strs[j] = cell.getStringCellValue();
System.out.println(strs[j]);
}
if (j == colcount - 1) {
Model model = new Model();
model.setDepartement(strs[0]);
model.setName(strs[1]);
model.setDate(strs[2]);
model.setAm(strs[3]);
model.setPm(strs[4]);
model.setTimes(strs[5]);
model.setRemark(strs[6]);
ModelList.add(model);
System.out.println("列号"+q++);
}
}
}
}
retList.add(list);
}
System.out.println(ModelList.get(15));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return SUCCESS;
}
return SUCCESS ;
}
导出
package com.ss.util.excel;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel实体BEAN的属性注解
* @author MrGao
* 2010-7-12下午05:10:37
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAnnotation {
String name();//Excel列名
int width();//Excel列宽
int id();//Excel列ID
}
package com.ss.util.excel;
import java.lang.reflect.Field;
import java.util.Comparator;
@SuppressWarnings("unchecked")
public class FieldComparator implements Comparator {
public int compare(Object arg0, Object arg1) {
Field fieldOne = (Field)arg0;
Field fieldTwo = (Field)arg1;
ExcelAnnotation annoOne = fieldOne.getAnnotation(ExcelAnnotation.class);
ExcelAnnotation annoTwo = fieldTwo.getAnnotation(ExcelAnnotation.class);
return annoOne.id()-annoTwo.id();
}
}
package com.ss.util.excel;
import java.awt.Color;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Arrays;
import java.util.List;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.Pattern;
import jxl.format.RGB;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
* 导出Excel文件
*
* @author MrGao 2010-7-13下午02:19:51
*/
public class ExcelExport {
/**
* 生成Excel
*
* @param models
* 封装需要到处的数据BEAN结合
* @param className
* 导成Excel的实体BEAN包名.类名
* @param tempPath
* 生成Excel存放的临时路径
* @param excelName
* 生成的Excel名
*/
@SuppressWarnings("unchecked")
public static void createExcel(List models, String className,
String tempPath, String excelName) {
Class clasVo = null;
try {
clasVo = Class.forName(className);
OutputStream os = new FileOutputStream(tempPath + "\\" + excelName
+ ".xls");
WritableWorkbook workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(excelName, 0);
// 用于标题
WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 17,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
jxl.format.Colour.WHITE);
WritableCellFormat wcf_title = new WritableCellFormat(titleFont);
wcf_title.setBackground(Colour.TEAL,Pattern.SOLID);
wcf_title.setBorder(Border.ALL, BorderLineStyle.DOUBLE, Colour.OCEAN_BLUE);
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_title.setAlignment(Alignment.CENTRE);
// 用于正文
WritableFont NormalFont = new WritableFont(WritableFont.TAHOMA, 11);
WritableCellFormat wcf_center = new WritableCellFormat(NormalFont);
wcf_center.setBorder(Border.ALL, BorderLineStyle.DOUBLE, Colour.GRAY_25);
wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_center.setAlignment(Alignment.CENTRE);
wcf_center.setWrap(true); // 是否换行
sheet.addCell(new Label(0, 0, excelName, wcf_title));
sheet.mergeCells(0, 0, clasVo.getDeclaredFields().length - 1, 0);
// 获取属性
Field[] fields = clasVo.getDeclaredFields();
//按照注解id排序Excel列
Arrays.sort(fields,new FieldComparator());
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
if (field.isAnnotationPresent(ExcelAnnotation.class)) {
//获取该字段的注解对象
ExcelAnnotation anno = field
.getAnnotation(ExcelAnnotation.class);
sheet.setColumnView(i, anno.width());
sheet.addCell(new Label(i, 1, anno.name(), wcf_center));
}
}
int rowId = 2;// 写入第几行 第一行为列头 数据从第二行开始写
for (Object ssTopModel : models) {
int columnId = 0;// 写入第几列 第一列为自动计算的行号 数据从第二列开始写
// 获取该类 并获取自身方法
Class clazz = ssTopModel.getClass();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
if (field.isAnnotationPresent(ExcelAnnotation.class)) {
String methodName = "get"+field.getName().substring(0,1).toUpperCase()+field.getName().substring(1);
Method method = clazz.getMethod(methodName);
try {
sheet.addCell(new Label(columnId, rowId, method
.invoke(ssTopModel) == null ? ""
: method.invoke(ssTopModel)
.toString(), wcf_center));
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
columnId++;
}
}
rowId++;
}
workbook.write();
workbook.close();
os.flush();
os.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
}
}