雖然說Excel操作在安卓上面不多見踢关，但是也是有用途的，比如說將一些數(shù)據(jù)導出到excel文件粘茄，可以在外部查看數(shù)據(jù)签舞。經(jīng)過大量的框架對比（實際上僅僅看了ApachePOI），我選擇了ApachePOI這個office處理的框架柒瓣。說干就干儒搭，我拿起了我500塊錢的機械鍵盤，花了無數(shù)個晚上芙贫，完成了這個框架搂鲫。
1.引入ApachePOI框架
dependencies {
api "org.apache.poi:poi:3.14-beta1"
}
2.編寫注解類
A.Excel導入注解類（ExcelInputAttribute）
@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.FIELD})
public @interface ExcelInputAttribute {
/**
* 列名對應的A,B,C,D...,不指定按照默認順序排序
*
* @return
*/
int column() default -1;
}
B.Excel導出注解類（ExcelOutputAttribute）
@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.FIELD})
public @interface ExcelOutputAttribute {
/**
* Excel中的列名
*
* @return
*/
String name();
/**
* 列名對應的A,B,C,D...,不指定按照默認順序排序
*
* @return
*/
double column() default -1;
String[] enumdata() default {};
/**
* 表頭字體顏色
*
* @return
*/
short titletextcolor() default HSSFColor.BLACK.index;
/**
* 表頭背景顏色
*
* @return
*/
IndexedColors titlebgcolor() default IndexedColors.WHITE;
short titleboldweight() default HSSFFont.BOLDWEIGHT_NORMAL;
/**
* 表頭文字是否粗體
*
* @return
*/
boolean isTitleBold() default false;
/**
* 表頭文字是否斜體
*
* @return
*/
boolean isTitleItalic() default false;
/**
* 內(nèi)容文字水平對齊方向
*
* @return
*/
short titleHorAlignment() default HSSFCellStyle.ALIGN_LEFT;
/**
* 內(nèi)容文字垂直對齊方向
*
* @return
*/
short titleVerAlignment() default HSSFCellStyle.VERTICAL_CENTER;
/**
* 表頭字體大小
*
* @return
*/
short titleTextSize() default 10;
/**
* 是否使用默認的表頭單元格樣式
*
* @return
*/
boolean isTitleNormalStyle() default true;
/**
* 內(nèi)容字體顏色
*
* @return
*/
short contenttextcolor() default HSSFColor.BLACK.index;
/**
* 內(nèi)容背景顏色
*
* @return
*/
IndexedColors contentbgcolor() default IndexedColors.WHITE;
short contentboldweight() default HSSFFont.BOLDWEIGHT_NORMAL;
/**
* 內(nèi)容文字是否粗體
*
* @return
*/
boolean isContentBold() default false;
/**
* 內(nèi)容文字是否斜體
*
* @return
*/
boolean isContentItalic() default false;
/**
* 內(nèi)容文字水平對齊方向
*
* @return
*/
short contentHorAlignment() default HSSFCellStyle.ALIGN_LEFT;
/**
* 內(nèi)容文字垂直對齊方向
*
* @return
*/
short contentVerAlignment() default HSSFCellStyle.VERTICAL_CENTER;
/**
* 內(nèi)容字體大小
*
* @return
*/
short contentTextSize() default 10;
/**
* 是否使用默認的內(nèi)容單元格樣式
*
* @return
*/
boolean isContentNormalStyle() default true;
}
3.定義實體類
A.導入基礎數(shù)據(jù)類（BaseInputModel）
public abstract class BaseInputModel {
/**
* 編寫忽略的條件
* @param row
* @return
*/
public abstract boolean ignore(Row row);
public abstract BaseInputModel create();
}
B.導入數(shù)據(jù)字段實體類（InputFieldModel）
public class InputFieldModel {
public int column;
public Field field;
public ExcelInputAttribute attribute;
}
C.導入數(shù)據(jù)字段實體類（OutputFieldModel）
public class OutputFieldModel {
public double column;
public Field field;
public ExcelOutputAttribute attribute;
}
D.多表導出數(shù)據(jù)的信息類（MultiExcelBean）
public class MultiExcelBean {
public int sheetlx; //sheet的index
public String sheetName; //sheet的名稱
public List datas; //sheet的數(shù)據(jù)
public Class clazz; //類
}
4.定義回調(diào)函數(shù)
A.excel導入處理回調(diào)（ExcelInputCallback）
public interface ExcelInputCallback<T> {
void onStartInput();
void onErrorInput(Exception e);
void onSuccessInput(List<T> singleSheetDatas);
}
B.excel導出處理回調(diào)（ExcelOutputCallback）
public interface ExcelOutputCallback {
void onStartOutput();
void onSuccessOutput();
void onErrorOutput(Exception e);
}
C.導入的方法接口
public interface IExcelInputControl<T extends BaseInputModel> {
/**
* 將excel單個sheet導入到數(shù)據(jù)庫中
*
* @param filePath 文件路徑
* @param ignore 忽略的行數(shù)
* @param callback 回調(diào)
*/
public void inputSingleSheetExcel(String filePath, ArrayList<Integer> ignore, final ExcelInputCallback callback);
/**
* 根據(jù)sheet名稱將excel單個sheet導入到數(shù)據(jù)庫中
*
* @param filePath 文件路徑
* @param sheetName sheet名稱
* @param ignore 忽略的行數(shù)
* @param callback 回調(diào)
*/
public void inputSingleSheetExcelBySheetName(String filePath, String sheetName, ArrayList<Integer> ignore, final ExcelInputCallback callback);
/**
* 根據(jù)sheet位置將excel單個sheet導入到數(shù)據(jù)庫中
*
* @param filePath 文件路徑
* @param position sheet位置
* @param ignore 忽略的行數(shù)
* @param callback 回調(diào)
*/
public void inputSingleSheetExcelBySheetPosition(String filePath, int position, ArrayList<Integer> ignore, final ExcelInputCallback callback);
}
D.導出的方法接口
public interface IExcelOutputControl<T> {
/**
* 導出單個sheet的excel文件
*
* @param list
* @param sheetName
* @param filePath
* @param fileName
* @param callback
*/
void outputExcelToFile(List<T> list, String sheetName, String filePath, String fileName, ExcelOutputCallback callback);
/**
* 導出多個sheet的文件
*
* @param multiExcelBeanList
* @param filePath
* @param fileName
* @param callback
*/
void outputMultiSheetExcelToFile(List<MultiExcelBean> multiExcelBeanList, String filePath,
String fileName, ExcelOutputCallback callback);
}
5.字段排序的類
A.導入類的字段排序（ExcelFiledInputSortCollections）
public class ExcelFiledInputSortCollections implements Comparator<InputFieldModel> {
private int type = 0;
public static int ASC_TYPE = 0;//升序，新的文件在下面
public static int DESC_TYPE = 1;//降序磺平，新的文件在上面
public ExcelFiledInputSortCollections(int type) {
this.type = type;
}
@Override
public int compare(InputFieldModel o1, InputFieldModel o2) {
if (type == DESC_TYPE) {
return descSort(o1, o2);
} else {
return ascSort(o1, o2);
}
}
private int ascSort(InputFieldModel o1, InputFieldModel o2) {
if (o1.equals(o2)) {
return 0;
} else if (o1.column > o2.column) {
return 1;
} else {
return -1;
}
}
private int descSort(InputFieldModel o1, InputFieldModel o2) {
if (o1.equals(o2)) {
return 0;
} else if (o1.column < o2.column) {
return 1;
} else {
return -1;
}
}
}
B.導出類的字段排序（ExcelFiledOutputSortCollections）
public class ExcelFiledOutputSortCollections implements Comparator<OutputFieldModel> {
private int type = 0;
public static int ASC_TYPE = 0;//升序魂仍，新的文件在下面
public static int DESC_TYPE = 1;//降序拐辽，新的文件在上面
public ExcelFiledOutputSortCollections(int type) {
this.type = type;
}
@Override
public int compare(OutputFieldModel o1, OutputFieldModel o2) {
if (type == DESC_TYPE) {
return descSort(o1, o2);
} else {
return ascSort(o1, o2);
}
}
private int ascSort(OutputFieldModel o1, OutputFieldModel o2) {
if (o1.equals(o2)) {
return 0;
} else if (o1.column > o2.column) {
return 1;
} else {
return -1;
}
}
private int descSort(OutputFieldModel o1, OutputFieldModel o2) {
if (o1.equals(o2)) {
return 0;
} else if (o1.column < o2.column) {
return 1;
} else {
return -1;
}
}
}
6.操作類
A.導入操作類（ExcelInputUtil）
public class ExcelInputUtil<T extends BaseInputModel> implements IExcelInputControl<T> {
private Class<T> clazz;
private static final String TAG = "ExcelInputUtil";
public ExcelInputUtil(Class<T> clazz) {
this.clazz = clazz;
}
/**
* 默認讀取第一個sheet
*
* @param filePath
* @param callback
*/
@Override
public void inputSingleSheetExcel(String filePath, ArrayList<Integer> ignore, ExcelInputCallback callback) {
inputSingleSheetExcelBySheetPosition(filePath, 0, ignore, callback);
}
/**
* 知道sheet名字的情況下使用
*
* @param filePath
* @param sheetName
* @param callback
*/
@Override
public void inputSingleSheetExcelBySheetName(String filePath, String sheetName, ArrayList<Integer> ignore, ExcelInputCallback callback) {
try {
callback.onStartInput();
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath));
HSSFSheet sheet = workbook.getSheet(sheetName);
if (sheet != null) {
callback.onSuccessInput(getSingleSheetDatas(sheet, ignore));
} else {
callback.onSuccessInput(null);
}
} catch (Exception e) {
System.out.println(TAG + " error--->" + e.getMessage());
callback.onErrorInput(e);
}
}
/**
* 知道位置的情況下使用
*
* @param filePath
* @param position sheet位置
* @param callback
*/
@Override
public void inputSingleSheetExcelBySheetPosition(String filePath, int position, ArrayList<Integer> ignore, ExcelInputCallback callback) {
try {
callback.onStartInput();
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath));
HSSFSheet sheet = workbook.getSheetAt(position);
if (sheet != null) {
callback.onSuccessInput(getSingleSheetDatas(sheet, ignore));
} else {
callback.onSuccessInput(null);
}
} catch (Exception e) {
System.out.println(TAG + " error--->" + e.getMessage());
callback.onErrorInput(e);
}
}
/**
* 獲取單個sheet的數(shù)據(jù)
*
* @param sheet
* @param ignore
* @return
* @throws Exception
*/
private List<T> getSingleSheetDatas(HSSFSheet sheet, ArrayList<Integer> ignore) throws Exception {
//解開合并單元格
releaseSheetMergeRegion(sheet);
List<T> result = new ArrayList<>();
Field[] fields = clazz.getDeclaredFields();
T bean = clazz.newInstance();
List<InputFieldModel> fieldModels = getFieldsAndSort(fields);
StringBuffer json = new StringBuffer("[");
boolean isNeedDeletePoint = false;
for (Row row : sheet) {
// 判斷是不是忽略該行數(shù)據(jù)
if (ignore.contains(row.getRowNum())) {
continue;
}
try {
// 跳過空行
if (row.getCell(0) == null || StringUtil.isBlank(row.getCell(0).getStringCellValue())) {
continue;
}
} catch (Exception e) {
e.printStackTrace();
if (row.getCell(0) != null) {
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
}
if (StringUtil.isBlank(row.getCell(0).getStringCellValue())) {
continue;
}
}
boolean isIgnore = bean.ignore(row);
if (isIgnore) {
continue;
}
//拼接為json
buildSingleJsonText(fieldModels, json, row);
isNeedDeletePoint = true;
}
if (isNeedDeletePoint) {
json.deleteCharAt(json.length() - 1);
}
json.append("]");
System.out.println(TAG + " " + fields.length + " json = " + json);
result.addAll(GsonUtils.getInstance().getListEntityByString(json.toString(), clazz));
return result;
}
/**
* 構建單個數(shù)據(jù)
*
* @param fieldModels
* @param json
* @param row
*/
private void buildSingleJsonText(List<InputFieldModel> fieldModels, StringBuffer json, Row row) {
json.append("{");
for (int i = 0; i < fieldModels.size(); i++) {
Field field = fieldModels.get(i).field;
int position = fieldModels.get(i).column;
String name = field.getName();
Class<?> type = field.getType();
json.append("\"");
json.append(name);
json.append("\"");
json.append(":");
Cell cell = row.getCell(position);
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
short format = cell.getCellStyle().getDataFormat();
SimpleDateFormat sdf = null;
if (format == 14 || format == 31 || format == 57 || format == 58) {
//日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
} else if (format == 20 || format == 32) {
//時間
sdf = new SimpleDateFormat("HH:mm");
}
if (sdf != null) {
//如果不為空，說明是時間格式
double value = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(value);
String time = sdf.format(date);
json.append("\"");
//內(nèi)容
json.append(time);
json.append("\"");
} else {
//說明是普通數(shù)據(jù)格式
appendData(json, type, cell);
}
} else {
appendData(json, type, cell);
}
if (i < fieldModels.size() - 1) {
json.append(",");
}
}
json.append("}");
json.append(",");
}
/**
* 釋放單元格合并
*
* @param sheet
*/
private void releaseSheetMergeRegion(HSSFSheet sheet) {
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
// if (mergedRegion.getFirstRow() == mergedRegion.getLastRow()) {
// //橫向合并
// System.out.println(TAG + i + " 單元格合并是橫向 " + mergedRegion.getFirstColumn() + " " + mergedRegion.getLastColumn());
// //需要解開單元格并賦值
// }
if (mergedRegion.getFirstColumn() == mergedRegion.getLastColumn()) {
//縱向合并
int firstColumn = mergedRegion.getFirstColumn();
int firstRow = mergedRegion.getFirstRow();
HSSFRow row = sheet.getRow(mergedRegion.getFirstRow());
HSSFCell cell = row.getCell(firstColumn);
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
double numericCellValue = cell.getNumericCellValue();
//需要解開單元格并賦值
for (int j = firstRow + 1; j < mergedRegion.getLastRow() + 1; j++) {
row = sheet.getRow(j);
row.getCell(firstColumn).setCellValue(numericCellValue);
}
} else {
String stringCellValue = cell.getStringCellValue();
//需要解開單元格并賦值
for (int j = firstRow + 1; j < mergedRegion.getLastRow() + 1; j++) {
row = sheet.getRow(j);
row.getCell(firstColumn).setCellValue(stringCellValue);
}
}
}
}
}
private void appendData(StringBuffer json, Class<?> type, Cell cell) {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (type == String.class) {
json.append("\"");
//內(nèi)容
json.append(cell.getStringCellValue());
json.append("\"");
} else if (type == Long.class) {
json.append(cell.getStringCellValue());
} else if (type == Integer.class) {
json.append(cell.getStringCellValue());
} else if (type == Double.class) {
json.append(cell.getStringCellValue());
} else {
json.append("\"");
//內(nèi)容
json.append(cell.getStringCellValue());
json.append("\"");
}
}
/**
* 獲取全部的字段并排序
*
* @param allFields
* @return
*/
private List<InputFieldModel> getFieldsAndSort(Field[] allFields) {
List<InputFieldModel> fieldModels = new ArrayList<InputFieldModel>();
for (Field field : allFields) {
if (field.isAnnotationPresent(ExcelInputAttribute.class)) {
InputFieldModel fieldModel = new InputFieldModel();
fieldModel.field = field;
fieldModel.attribute = field.getAnnotation(ExcelInputAttribute.class);
fieldModel.column = fieldModel.attribute.column();
fieldModels.add(fieldModel);
}
}
Collections.sort(fieldModels, new ExcelFiledInputSortCollections(ASC_TYPE));
return fieldModels;
}
B.導出操作類（ExcelOutputUtil）
public class ExcelOutputUtil<T> implements IExcelOutputControl<T> {
private Class<T> clazz;
private static final String TAG = "ExcelOutputUtil";
public ExcelOutputUtil(Class<T> clazz) {
this.clazz = clazz;
}
/**
* 將list數(shù)據(jù)導出到excel文檔
*
* @param list 需要添加的數(shù)據(jù)
* @param sheetName 工作表的名稱
* @param filePath 文件路徑
* @param fileName 文件名稱
* @param callback 操作回調(diào)
* @return
*/
@Override
public void outputExcelToFile(List<T> list, String sheetName, String filePath, String fileName, final ExcelOutputCallback callback) {
callback.onStartOutput();
try {
// 產(chǎn)生工作薄對象
HSSFWorkbook workbook = new HSSFWorkbook();
// 得到所有定義字段
Field[] allFields = clazz.getDeclaredFields();
// 得到所有field并存放到一個list中擦酌，排序,根據(jù)column大小排序
List<OutputFieldModel> outputFieldModels = getFieldsAndSort(allFields);
createSingleSheet(list, sheetName, workbook, outputFieldModels, 0);
outputToExcelFile(filePath, fileName, workbook);
callback.onSuccessOutput();
} catch (final Exception e) {
System.out.println(TAG + " error--->" + e.getMessage());
callback.onErrorOutput(e);
}
}
/**
* 導出多個sheet
*
* @param multiExcelBeanList 多個sheet信息
* @param filePath 文件路徑
* @param fileName 文件名稱
* @param callback 操作回調(diào)
*/
@Override
public void outputMultiSheetExcelToFile(List<MultiExcelBean> multiExcelBeanList, String filePath,
String fileName, final ExcelOutputCallback callback) {
callback.onStartOutput();
try {
// 產(chǎn)生工作薄對象
HSSFWorkbook workbook = new HSSFWorkbook();
for (int index = 0; index < multiExcelBeanList.size(); index++) {
// 得到所有定義字段
Field[] allFields = multiExcelBeanList.get(index).clazz.getDeclaredFields();
// 得到所有field并存放到一個list中薛训，排序,根據(jù)column大小排序
List<OutputFieldModel> outputFieldModels = getFieldsAndSort(allFields);
//獲取sheet信息
List<T> list = multiExcelBeanList.get(index).datas;
String sheetName = multiExcelBeanList.get(index).sheetName;
// 產(chǎn)生工作表對象
createSingleSheet(list, sheetName, workbook, outputFieldModels, multiExcelBeanList.get(index).sheetlx);
}
outputToExcelFile(filePath, fileName, workbook);
callback.onSuccessOutput();
} catch (final Exception e) {
System.out.println(TAG + " error--->" + e.getMessage());
callback.onErrorOutput(e);
}
}
/**
* 將數(shù)據(jù)導出到文件
*
* @param filePath
* @param fileName
* @param workbook
* @throws IOException
*/
private void outputToExcelFile(String filePath, String fileName, HSSFWorkbook workbook) throws IOException {
File file = new File(filePath);
if (!file.exists()) {
file.mkdirs();
}
System.out.println(TAG + " 操作--->數(shù)據(jù)填寫完畢");
OutputStream output = new FileOutputStream(filePath + "/" + fileName);
output.flush();
workbook.write(output);
output.close();
}
/**
* 創(chuàng)建單個sheet
*
* @param list
* @param sheetName
* @param workbook
* @param outputFieldModels
* @param sheetlx
* @throws IllegalAccessException
*/
private void createSingleSheet(List<T> list, String sheetName, HSSFWorkbook workbook,
List<OutputFieldModel> outputFieldModels, int sheetlx) throws IllegalAccessException {
// 產(chǎn)生工作表對象
HSSFSheet sheet = workbook.createSheet();
sheet.createFreezePane(0, 1, 0, 1);
// 設置工作表的名稱.
workbook.setSheetName(sheetlx, sheetName);
HSSFRow row;
row = sheet.createRow(0);// 產(chǎn)生一行
//普通列樣式/
HSSFFont font = workbook.createFont();
HSSFCellStyle cellStyle = workbook.createCellStyle();
font.setFontName("Arail narrow"); // 字體
font.setColor(HSSFFont.COLOR_NORMAL); // 字體顏色
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 字體寬度
cellStyle.setFont(font);
// 創(chuàng)建表格頭
createTableTitle(workbook, outputFieldModels, sheet, row, cellStyle);
//將數(shù)據(jù)寫入表格，寫入各條記錄,每條記錄對應excel表中的一行
inputSheetDatas(list, workbook, outputFieldModels, sheet, cellStyle);
}
/**
* 寫入單個sheet數(shù)據(jù)
*
* @param list
* @param workbook
* @param outputFieldModels
* @param sheet
* @param cellStyle
* @throws IllegalAccessException
*/
private void inputSheetDatas(List<T> list, HSSFWorkbook workbook, List<OutputFieldModel> outputFieldModels, HSSFSheet sheet, HSSFCellStyle cellStyle) throws IllegalAccessException {
HSSFRow row;
HSSFCell cell;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 1);
T vo = (T) list.get(i); // 得到導出對象.
for (int j = 0; j < outputFieldModels.size(); j++) {
//填寫每個單元格內(nèi)容
// 獲得field
Field field = outputFieldModels.get(j).field;
// // 設置實體類私有屬性可訪問
ExcelOutputAttribute attr = outputFieldModels.get(j).attribute;
int col = j;
// 根據(jù)指定的順序獲得列號
// if (-1 != attr.column()) {
// col = attr.column();
// }
cell = row.createCell(col);
if (attr.isContentNormalStyle()) {
cell.setCellStyle(cellStyle);
} else {
cell.setCellStyle(getCellStyle(workbook, attr.contentbgcolor().getIndex(),
attr.contenttextcolor(), attr.contentboldweight(),
attr.isContentBold(), attr.isContentItalic(),
attr.contentHorAlignment(), attr.contentVerAlignment(),
attr.contentTextSize()));
}
// 如果數(shù)據(jù)存在就填入,不存在填入空格
Class<?> classType = (Class<?>) field.getType();
if (field.get(vo) != null) {
String str = String.valueOf(field.get(vo));
if (classType.isAssignableFrom(List.class)) {
if (str.length() > 1) {
cell.setCellValue(str.substring(1, str.length() - 1));
} else {
cell.setCellValue(str);
}
} else {
String[] enumdata = attr.enumdata();
if (enumdata != null && enumdata.length > 0) {
for (int k = 0; k < enumdata.length; k += 2) {
String enumdatum = enumdata[k];
if (str.equals(enumdatum)) {
str = enumdata[k + 1];
break;
}
}
}
cell.setCellValue(str);
}
}
}
}
}
/**
* 寫入表頭
*
* @param workbook
* @param outputFieldModels
* @param sheet
* @param row
* @param cellStyle
*/
private void createTableTitle(HSSFWorkbook workbook, List<OutputFieldModel> outputFieldModels, HSSFSheet sheet, HSSFRow row, HSSFCellStyle cellStyle) {
HSSFCell cell;
for (int i = 0; i < outputFieldModels.size(); i++) {
int col = i; //按照順序遍歷字段
// 根據(jù)指定的順序獲得列號
ExcelOutputAttribute excelOutputAttribute = outputFieldModels.get(i).attribute;
// 創(chuàng)建列
cell = row.createCell(col);
if (excelOutputAttribute.isTitleNormalStyle()) {
cell.setCellStyle(cellStyle);
} else {
cell.setCellStyle(getCellStyle(workbook, excelOutputAttribute.titlebgcolor().getIndex(),
excelOutputAttribute.titletextcolor(), excelOutputAttribute.titleboldweight(),
excelOutputAttribute.isTitleBold(), excelOutputAttribute.isTitleItalic(),
excelOutputAttribute.titleHorAlignment(), excelOutputAttribute.titleVerAlignment(),
excelOutputAttribute.titleTextSize()));
}
sheet.setColumnWidth(col, (int) ((excelOutputAttribute.name().getBytes().length <= 4 ? 6 : excelOutputAttribute.name().getBytes().length) * 1.0 * 256));
// 設置列中寫入內(nèi)容為String類型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 寫入列名
cell.setCellValue(excelOutputAttribute.name());
}
}
/**
* 獲取全部的字段并排序
*
* @param allFields
* @return
*/
private List<OutputFieldModel> getFieldsAndSort(Field[] allFields) {
List<OutputFieldModel> outputFieldModels = new ArrayList<OutputFieldModel>();
for (Field field : allFields) {
if (field.isAnnotationPresent(ExcelOutputAttribute.class)) {
OutputFieldModel outputFieldModel = new OutputFieldModel();
outputFieldModel.field = field;
outputFieldModel.attribute = field.getAnnotation(ExcelOutputAttribute.class);
outputFieldModel.column = outputFieldModel.attribute.column();
outputFieldModels.add(outputFieldModel);
}
}
//排序,根據(jù)column大小排序
Collections.sort(outputFieldModels, new ExcelFiledOutputSortCollections(ASC_TYPE));
return outputFieldModels;
}
/**
* 獲取單元格樣式
*
* @param workbook
* @param bgcolor
* @param textcolor
* @param boldweight
* @param isBold
* @param isItalic
* @param horAlignment
* @param verAlignment
* @param textsize
* @return
*/
private HSSFCellStyle getCellStyle(HSSFWorkbook workbook, short bgcolor, short textcolor, short boldweight,
boolean isBold, boolean isItalic, short horAlignment, short verAlignment, short textsize) {
HSSFFont font = workbook.createFont();
HSSFCellStyle cellStyle = workbook.createCellStyle();
font.setFontName("Arail narrow"); // 字體
font.setColor(textcolor); // 字體顏色
font.setBoldweight(boldweight); // 字體寬度
font.setBold(isBold);
font.setItalic(isItalic);
font.setFontHeightInPoints(textsize);
cellStyle.setFont(font);
//設置背景色
cellStyle.setFillForegroundColor(bgcolor);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
//設置水平對齊的樣式為居中對齊;
cellStyle.setAlignment(horAlignment);
//設置垂直對齊的樣式為居中對齊;
cellStyle.setVerticalAlignment(verAlignment);
return cellStyle;
}
}
-------------------------------------------------------------------------------------------這里是華麗的分割線-------------------------------------------------------------------------------------------
下面就到了使用的環(huán)節(jié)了
1.導入excel數(shù)據(jù)
A.定義數(shù)據(jù)結(jié)構
public class CdExcelInfoModel extends BaseInputModel {
/**
* 編號
*/
@ExcelInputAttribute(column = 0)
public String no;
/**
* 演唱者名稱仑氛，歌手
*/
@ExcelInputAttribute(column = 1)
public String playerName;
/**
* cd名稱
*/
@ExcelInputAttribute(column = 2)
public String cdName;
/**
* cd購買價格
*/
@ExcelInputAttribute(column = 3)
public double price;
/**
* 碟片數(shù)量
*/
@ExcelInputAttribute(column = 4)
public int discNum;
@Override
public boolean ignore(Row row) {
if ("總計".equals(row.getCell(0).getStringCellValue())) {
return true;
}
return false;
}
@Override
public BaseInputModel create() {
return this;
}
}
B.調(diào)用方法
IExcelInputControl<CdExcelInfoModel> inputControl = new ExcelInputUtil<CdExcelInfoModel>(CdExcelInfoModel.class);
//獲取第一個sheet的數(shù)據(jù)
inputControl.inputSingleSheetExcel("filePath", new ArrayList<Integer>(), callback);
//獲取指定sheet名字的數(shù)據(jù)
inputControl.inputSingleSheetExcelBySheetName("filePath", "sheetname", new ArrayList<Integer>(), callback);
//獲取指定位置的sheet的數(shù)據(jù)，sheetPosition從0開始
inputControl.inputSingleSheetExcelBySheetPosition("filePath", sheetPosition, new ArrayList<Integer>(), callback);
2.導出excel數(shù)據(jù)
A.定義數(shù)據(jù)結(jié)構
public class ExportSsqInfoModel {
/**
* 期數(shù)
*/
@ExcelOutputAttribute(name = "期數(shù)", column = 1)
public String lottery_no;
/**
* 顯示的開獎結(jié)果
*/
@ExcelOutputAttribute(name = "紅球1", column = 2)
public String lottery_red_one_txt;
@ExcelOutputAttribute(name = "紅球2", column = 3)
public String lottery_red_two_txt;
@ExcelOutputAttribute(name = "紅球3", column = 4)
public String lottery_red_three_txt;
@ExcelOutputAttribute(name = "紅球4", column = 5)
public String lottery_red_four_txt;
@ExcelOutputAttribute(name = "紅球5", column = 6)
public String lottery_red_five_txt;
@ExcelOutputAttribute(name = "紅球6", column = 7)
public String lottery_red_six_txt;
@ExcelOutputAttribute(name = "藍球", column = 8)
public String lottery_blue_txt;
@ExcelOutputAttribute(name = "開獎時間", column = 9)
public String time;
}
B.調(diào)用方法
IExcelOutputControl<ExportSsqInfoModel> util = new ExcelOutputUtil<>(ExportSsqInfoModel.class);
List<MultiExcelBean> multiExcelBeans = new ArrayList<>();
MultiExcelBean historyModel = new MultiExcelBean();
historyModel.datas = historyData;
historyModel.clazz = ExportSsqInfoModel.class;
historyModel.sheetName = "雙色球開獎結(jié)果";
historyModel.sheetlx = 0;
multiExcelBeans.add(historyModel);
MultiExcelBean statisticsModelBlue = new MultiExcelBean();
statisticsModelBlue.datas = blueStatisticsData;
statisticsModelBlue.clazz = ExportSsqStatisticsModel.class;
statisticsModelBlue.sheetName = "藍球統(tǒng)計";
statisticsModelBlue.sheetlx = 1;
multiExcelBeans.add(statisticsModelBlue);
MultiExcelBean statisticsModelRed = new MultiExcelBean();
statisticsModelRed.datas = redStatisticsData;
statisticsModelRed.clazz = ExportSsqStatisticsModel.class;
statisticsModelRed.sheetName = "紅球統(tǒng)計";
statisticsModelRed.sheetlx = 2;
multiExcelBeans.add(statisticsModelRed);
util.outputMultiSheetExcelToFile(multiExcelBeans, path, fileName, new ExcelOutputCallback() {
@Override
public void onStartOutput() {
}
@Override
public void onSuccessOutput() {
}
@Override
public void onErrorOutput(Exception e) {
}
});
3.其他說明
A.導出注解描述
name 是列名闸英；如例子所示锯岖，表頭列名是“收費類型”。
column 是列數(shù)（0代表第一列甫何，1代表第二列出吹，以此類推）；如例子所示辙喂，該字段是在excel的第21列捶牢。如果字段不填寫column，則按照默認的字段名根據(jù)字母排序巍耗。
enumdata 是映射表（奇數(shù)位置的是key秋麸；偶數(shù)位置的是value,也就是導出數(shù)據(jù)的顯示文字）；enumdata的數(shù)組可以為空炬太，但是數(shù)組長度必須為偶數(shù)灸蟆。
titletextcolor
titlebgcolor
titleboldweight
isTitleBold
isTitleItalic
titleHorAlignment
titleVerAlignment
titleTextSize
isTitleNormalStyle
B.如果字段的類型是自定義的model，則需要重寫model的toString方法亲族，用于在excel顯示必要的內(nèi)容炒考。
C.如果需要添加字段，則把column的值寫成需要插進的字段之間霎迫，斋枢，，如20.5表示插在20-21之間的字段知给。
別問我使用了什么設計模式瓤帚，老子寫代碼從來都是一把梭（滑稽.jpg）
當初寫這個庫目的只是熟悉注解以及反射的使用，還有個人項目里面要用到炼鞠，這才花時間寫了這個代碼缘滥。
