// エクセルシートへのルールログのエクスポート
public boolean exportLogXLS(String filename) {
try {
WritableWorkbook workbook = Workbook.createWorkbook(new File(filename));
// 制約エラーの書き込み
WritableSheet sheet = workbook.createSheet("BrokenRule", 1);
int i = 0;
sheet.addCell(new Label(0, i, "Broken Rule Name"));
sheet.addCell(new Label(1, i, "Broken Elements"));
i++;
for (RuleLogger brokenRule : ruleLog) {
sheet.addCell(new Label(0, i, brokenRule.getRuleID()));
sheet.addCell(new Label(1, i, brokenRule.getMessage()));
i++;
}
workbook.write();
workbook.close();
return true;
} catch (IOException e) {
logger.error("Output " + filename + " cause IOException!");
return false;
} catch (WriteException e) {
logger.error("Output " + filename + " cause WriteException!");
return false;
}
}
public static void addRow(
WritableSheet sheet,
int startRow,
CellValue[] cellValues,
WritableCellFormat cellFormat,
WritableCellFormat intCellFormat,
WritableCellFormat doubleCellFormat,
WritableCellFormat dateFormat)
throws WriteException {
for (int i = 0; i < cellValues.length; i++) {
if (cellValues[i] != null && cellValues[i].getValue() != null) {
if (cellValues[i].getType().equals(CellDataType.STRING)) {
Label label =
new Label(i, startRow, String.valueOf(cellValues[i].getValue()), cellFormat);
sheet.addCell(label);
} else if (cellValues[i].getType().equals(CellDataType.INT)) {
Number number =
new Number(i, startRow, (Integer) cellValues[i].getValue(), intCellFormat);
sheet.addCell(number);
} else if (cellValues[i].getType().equals(CellDataType.DOUBLE)) {
Number number =
new Number(i, startRow, (Double) cellValues[i].getValue(), doubleCellFormat);
sheet.addCell(number);
} else if (cellValues[i].getType().equals(CellDataType.DATE)) {
Date now = new Date(((Timestamp) cellValues[i].getValue()).getTime());
DateTime dateCell = new DateTime(i, startRow, now, dateFormat);
sheet.addCell(dateCell);
}
} else {
Label label = new Label(i, startRow, "", cellFormat);
sheet.addCell(label);
}
}
}
/**
* 生成固定格式的excel,表格都为文本,水平居左,垂直居中
*
* @param sheetName sheet名称,默认为sheet1
* @param content Map,要生成excel的数据来源
* @param os excel输出流
*/
public void exportFormatExcel(
Map<String, String[][]> content,
String[] salary_name_array,
String sheetName,
OutputStream os) {
if (VerifyUtil.isNullObject(content, os) || content.size() == 0) {
return;
}
// 默认名称
if (VerifyUtil.isNullObject(sheetName)) {
sheetName = "sheet1";
}
WritableWorkbook workbook = null;
try {
workbook = Workbook.createWorkbook(os);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
int index = 0;
for (int k = 0; k < salary_name_array.length; k++) {
String[][] value = (String[][]) content.get(salary_name_array[k]);
if (value != null && value.length > 0) {
if (index != 0) {
index++;
}
WritableCellFormat format1 = new WritableCellFormat();
format1.setAlignment(jxl.format.Alignment.LEFT);
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label1 = new Label(0, index, salary_name_array[k], format1);
sheet.addCell(label1);
for (int i = 0; i < value.length; i++) {
index++;
for (int j = 0; j < value[i].length; j++) {
if (value[i][j] == null) {
value[i][j] = "";
}
WritableCellFormat format = new WritableCellFormat();
format.setAlignment(jxl.format.Alignment.LEFT);
format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label label = new Label(j, index, value[i][j], format);
sheet.addCell(label);
}
}
}
}
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
private int nadbavka(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException {
sheet.addCell(new Label(column, row, "Размер надбавки % Калуга", font.tahomaValue));
sheet.addCell(new Label(column + 1, row, "Размер надбавки % Область", font.tahomaValue));
row++;
for (int tau = 0; tau < time.length; tau++) {
for (int i = 0; i < 4; i++) {
String fl = toColumnExcel(column - 1) + Integer.toString(row + tau * 7 + i + 1);
String fkaluga = toColumnExcel(column + 2) + Integer.toString(row + tau * 7 + i + 1);
String foblast = toColumnExcel(column + 3) + Integer.toString(row + tau * 7 + i + 1);
sheet.addCell(
new Formula(
column,
row + tau * 7 + i,
"IF(ISERROR(SUM("
+ fkaluga
+ "-"
+ fl
+ ") / "
+ fl
+ "),\"-\",SUM("
+ fkaluga
+ "-"
+ fl
+ ") / "
+ fl
+ ")",
font.tahomaValuePer));
sheet.addCell(
new Formula(
column + 1,
row + tau * 7 + i,
"IF(ISERROR(SUM("
+ foblast
+ "-"
+ fl
+ ") / "
+ fl
+ "),\"-\",SUM("
+ foblast
+ "-"
+ fl
+ ") / "
+ fl
+ ")",
font.tahomaValuePer));
}
}
return column + 2;
}
/**
* 添加单元格&内容.
*
* @param ws
* @param cel
* @param row
* @param valObject
*/
private void addCell(WritableSheet ws, int cel, int row, Object valObj) throws WriteException {
if (valObj == null) {
Label cr = new Label(cel, row, "");
ws.addCell(cr);
return;
}
@SuppressWarnings("rawtypes")
Class clazz2 = valObj.getClass();
if (clazz2 == String.class) {
Label cr = new Label(cel, row, (String) valObj);
ws.addCell(cr);
return;
} else if (clazz2 == int.class || clazz2 == Integer.class) {
Number cr = new Number(cel, row, Double.parseDouble(((Integer) valObj).toString()));
ws.addCell(cr);
return;
} else if (clazz2 == long.class || clazz2 == Long.class) {
Number cr = new Number(cel, row, Double.parseDouble(((Long) valObj).toString()));
ws.addCell(cr);
return;
} else if (clazz2 == float.class || clazz2 == Float.class) {
Number cr = new Number(cel, row, Double.parseDouble(((Float) valObj).toString()));
ws.addCell(cr);
return;
} else if (clazz2 == double.class || clazz2 == Double.class) {
Number cr = new Number(cel, row, Double.parseDouble(((Double) valObj).toString()));
ws.addCell(cr);
return;
} else if (clazz2 == Date.class) {
DateTime cr = new DateTime(cel, row, (Date) valObj);
ws.addCell(cr);
return;
} else if (clazz2 == boolean.class || clazz2 == Boolean.class) {
jxl.write.Boolean cr = new jxl.write.Boolean(cel, row, (Boolean) valObj);
ws.addCell(cr);
return;
} else if (clazz2 == BigDecimal.class) {
NumberFormat nf = new NumberFormat("0.00");
WritableCellFormat wcf = new WritableCellFormat(nf);
wcf.setFont(new WritableFont(WritableFont.createFont("宋体"), PS));
Number cr = new Number(cel, row, ((BigDecimal) valObj).doubleValue(), wcf);
ws.addCell(cr);
return;
} else if (clazz2 == Short.class) {
Number cr = new Number(cel, row, Double.parseDouble(((Short) valObj).toString()));
ws.addCell(cr);
return;
}
}
public void fillReportRow1(
int row, WritableWorkbook workbook, Org org, Cun cun, Report report, boolean leiji)
throws Exception {
WritableSheet sheet1 = workbook.getSheet(0);
WritableSheet sheet2 = workbook.getSheet(1);
// 装填sheet1
if (leiji) {
sheet1.addCell(new Label(0, row, "累计"));
} else {
sheet1.addCell(new Label(0, row, org.getOrgName()));
sheet1.addCell(new Label(1, row, cun.getName()));
}
for (int i = 2; i <= 30; i++) {
sheet1.addCell(new Label(i, row, report.getItem(i - 1)));
}
// 装填sheet2
if (leiji) {
sheet2.addCell(new Label(0, row, "累计"));
} else {
sheet2.addCell(new Label(0, row, org.getOrgName()));
sheet2.addCell(new Label(1, row, cun.getName()));
}
for (int i = 2; i <= 30; i++) {
sheet2.addCell(new Label(i, row, report.getItem(i + 28)));
}
}
private static void toXlsInternal(
Grid grid, WritableWorkbook workbook, String sheetName, int sheetNo) throws Exception {
if (grid == null) {
return;
}
WritableSheet sheet = workbook.createSheet(sheetName, sheetNo);
int rowNumber = 1;
int columnIndex = 0;
if (StringUtils.isNotEmpty(grid.getTitle())) {
sheet.addCell(new Label(0, rowNumber++, grid.getTitle(), XLS_FORMAT_TTTLE));
rowNumber++;
}
if (StringUtils.isNotEmpty(grid.getSubtitle())) {
sheet.addCell(new Label(0, rowNumber++, grid.getSubtitle(), XLS_FORMAT_TTTLE));
rowNumber++;
}
for (GridHeader header : grid.getVisibleHeaders()) {
sheet.addCell(new Label(columnIndex++, rowNumber, header.getName(), XLS_FORMAT_LABEL));
}
rowNumber++;
for (List<Object> row : grid.getVisibleRows()) {
columnIndex = 0;
for (Object column : row) {
if (column != null && MathUtils.isNumeric(String.valueOf(column))) {
sheet.addCell(
new Number(
columnIndex++,
rowNumber,
Double.valueOf(String.valueOf(column)),
XLS_FORMAT_TEXT));
} else {
String content = column != null ? String.valueOf(column) : EMPTY;
sheet.addCell(new Label(columnIndex++, rowNumber, content, XLS_FORMAT_TEXT));
}
}
rowNumber++;
}
}
public String getExcelStatFilePath(List<String[]> list) throws Exception {
// 文件拷贝
String path = Thread.currentThread().getContextClassLoader().getResource("/").getPath();
path = path.substring(0, path.indexOf("WEB-INF"));
path += "excel" + File.separator + "export";
String srcFileName = "stat";
String sourceFile = path + File.separator + srcFileName + ".xls";
String targetFile = path + File.separator + "stat_" + Util.getDate(new Date()) + ".xls";
// 打开excel文件
Workbook rw = Workbook.getWorkbook(new File(sourceFile));
WritableWorkbook workbook = Workbook.createWorkbook(new File(targetFile), rw);
WritableSheet sheet1 = workbook.getSheet(0);
WritableSheet sheet2 = workbook.getSheet(1);
WritableSheet sheet3 = workbook.getSheet(2);
WritableSheet sheet4 = workbook.getSheet(3);
WritableSheet sheet5 = workbook.getSheet(4);
int row = 2;
for (String[] arr : list) {
for (int i = 0; i <= 1; i++) {
for (int j = 0; j < 5; j++) {
workbook.getSheet(j).addCell(new Label(i, row, arr[i]));
}
}
for (int i = 2; i <= 15; i++) {
sheet1.addCell(new Label(i, row, arr[i]));
}
for (int i = 16; i <= 29; i++) {
sheet2.addCell(new Label(i - 14, row, arr[i]));
}
for (int i = 30; i <= 43; i++) {
sheet3.addCell(new Label(i - 14 * 2, row, arr[i]));
}
for (int i = 44; i <= 58; i++) {
sheet4.addCell(new Label(i - 14 * 3, row, arr[i]));
}
for (int i = 100; i <= 108; i++) {
sheet5.addCell(new Label(i - 98, row, arr[i]));
}
row++;
}
// 关闭
workbook.write();
workbook.close();
rw.close();
return targetFile;
}
private int pro1(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ОАО \"Калуганефтепродукт\"", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 11, row);
column = opt(column, row + 1, sheet, 1);
column = nadbavka(column, row + 1, sheet);
ave_k.add(column);
column = vill(column, row + 1, sheet, 250, "г. Калуга розница");
ave_obl_all.add(column);
column = vill(column, row + 1, sheet, 251, "Область розница");
column = vill(column, row + 1, sheet, 23, "г. Боровск");
column = vill(column, row + 1, sheet, 212, "г. Обнинск");
column = vill(column, row + 1, sheet, 58, "Жиздринский р-н");
column = vill(column, row + 1, sheet, 5, "Бабынинский р-н");
column = vill(column, row + 1, sheet, 40, "Дзержинский р-н");
column = vill(column, row + 1, sheet, 90, "Козельский р-н");
return column;
}
public static void addRowTop(WritableSheet ws) throws Exception {
WritableFont Bwf =
new WritableFont(
WritableFont.ARIAL, 16, WritableFont.BOLD, false); // 创建大字体：Arial,大小为18号,粗体，非斜体
Bwf.setColour(jxl.format.Colour.BLACK); // //字体颜色为红色
// 创建单元格格式：
jxl.write.WritableCellFormat CwcfF = new jxl.write.WritableCellFormat(Bwf);
CwcfF.setAlignment(jxl.write.Alignment.CENTRE); // 设置水平对齐为居中对齐
CwcfF.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置垂直对齐为居中对齐
CwcfF.setBorder(jxl.format.Border.TOP, BorderLineStyle.MEDIUM); // 设置顶部边框线为实线(默认是黑色－－也可以设置其他颜色)
CwcfF.setBorder(jxl.format.Border.RIGHT, BorderLineStyle.MEDIUM); // 设置右边框线为实线
CwcfF.setBorder(jxl.format.Border.BOTTOM, BorderLineStyle.MEDIUM); // 设置顶部框线为实线
List<Label> labelList = new ArrayList<Label>();
labelList.add(new Label(0, 0, "商品名称", CwcfF)); //
labelList.add(new Label(1, 0, "单位", CwcfF)); //
labelList.add(new Label(2, 0, "销售数量", CwcfF)); //
labelList.add(new Label(3, 0, "销售成本", CwcfF));
labelList.add(new Label(4, 0, "销售金额", CwcfF));
labelList.add(new Label(5, 0, "利润", CwcfF));
labelList.add(new Label(6, 0, "利润率", CwcfF));
for (int j = 0; j < labelList.size(); j++) {
ws.addCell(labelList.get(j));
}
for (int i = 0; i < ws.getColumns(); i++) {
Cell cell = ws.getCell(i, 0);
ws.setColumnView(i, cell.getContents().length() * 4);
}
// ws.setRowView(0, 80*4);
}
public void putHeaderDefault(
WritableCellFormat cellFormat, WritableSheet sheet, Object value, int row, int column)
throws RowsExceededException, WriteException {
sheet.setColumnView(column, 20);
Label label = new Label(column, row, "" + value, cellFormat);
sheet.addCell(label);
}
@SuppressWarnings("unused")
private int oblast(int column, int row, WritableSheet sheet, int commId)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "Область розница", font.tahomaValue));
/** хранит id всех АЗС одного сетевого поставщика по Калуги */
Vector<Integer> stationsComm = new Vector<Integer>();
/** Получение id всех станций АЗС одного сетевого поставщика по Калуги */
ResultSet bdStations =
DriverManager.getConnection("jdbc:sqlite:" + Oil.PATH)
.createStatement()
.executeQuery(
"SELECT id FROM station WHERE comm_id LIKE '"
+ commId
+ "' AND district_id != '24';");
while (bdStations.next()) {
stationsComm.add(bdStations.getInt("id"));
}
bdStations.close();
String[][] value = ave(stationsComm);
osn(column, row + 1, sheet, value);
return column + 1;
}
/**
* 采用List<List<String>>方式写WritableSheet
*
* @param sheet
* @param xlsList
* @throws Exception
*/
private static void CreateXlsSheet(WritableSheet sheet, List<List<String>> xlsList)
throws Exception {
WritableCellFormat cf = new WritableCellFormat();
cf.setWrap(true);
cf.setAlignment(jxl.format.Alignment.LEFT);
cf.setVerticalAlignment(VerticalAlignment.TOP);
/** 填充数据 */
for (int row = 0; row < xlsList.size(); row++) {
List<String> rowList = xlsList.get(row);
// 如果该行为空,则跳过(一般跳过两行)
if (isEmpty(rowList)) continue;
// 单元格 Object.field
for (int col = 0; col < rowList.size(); col++) {
// 跳过无属性行
if (isEmpty(rowList.get(col))) continue;
// 写数据
String value = C.EMPTY_STRING;
value = StringValue(rowList.get(col));
WritableCell c = sheet.getWritableCell(col, row);
c.setCellFormat(cf);
if (CellType.EMPTY.equals(c.getType())) {
Label label = new Label(col, row, value);
label.setCellFormat(cf);
sheet.addCell(label);
if (row == 0) label.setCellFormat(GetCellFormat());
} else {
Label label = (Label) c;
label.setString(value);
}
}
}
}
public static synchronized void putIntoWorkbook(HashMap<String, String> data, int index) {
String[] Keys = {
"School",
"Level",
"Title",
"Type",
"Application Fee",
"Tuition Fee",
"Academic Entry Requirement",
"IELTS Average Requirement",
"IELTS Lowest Requirement",
"Structure",
"Length (months)",
"Month of Entry",
"Scholarship"
};
for (int j = 0; j < 13; j++) {
// label = new Label(j, i, data.get(Keys[j]));
Label label = new Label(j, index, data.get(Keys[j]));
try {
sheet.addCell(label);
} catch (Exception ee) {
ee.printStackTrace();
}
}
}
public static void writeToCell(WritableSheet sheet, String value, int col, int row) {
if (sheet == null) return;
Label label = new Label(col, row, value);
try {
sheet.addCell(label);
} catch (WriteException e) {
e.printStackTrace();
}
}
private int pro13(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ООО \"Экоресурс\"", font.tahoma9ptBoldMedion));
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 142, "Спас-Деменский р-н");
return column;
}
public void setNumber(int x, int y, double n) {
Number number = new Number(x, y, n);
try {
excelPage.addCell(number);
} catch (Exception e) {
System.err.println(
"Error while writing Xls data (XlsManager.java) File Already open by an other app ?");
}
}
private int pro15(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ОАО \"АЗС Хвастовичи\"", font.tahoma9ptBoldMedion));
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 160, "Хвастовичский р-н");
return column;
}
private int pro9(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ИП Журавлева", font.tahoma9ptBoldMedion));
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 13, "Барятинский р-н");
return column;
}
private int pro7(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ООО \"Октан\"", font.tahoma9ptBoldMedion));
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 218, "г. Обнинск");
return column;
}
private int pro12(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ООО \"Солид\"", font.tahoma9ptBoldMedion));
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 157, "Ферзиковский р-н");
return column;
}
public void setLabel(int x, int y, String texte) {
Label label = new Label(x, y, texte);
try {
excelPage.addCell(label);
} catch (Exception e) {
System.err.println(
"Error while writing Xls data (XlsManager.java) File Already open by an other app ?");
}
}
/** 导出JTable到Excel */
public void exportTable(JTable table, File file) throws IOException {
try {
OutputStream out = new FileOutputStream(file);
TableModel model = table.getModel();
WritableWorkbook wwb = Workbook.createWorkbook(out);
WritableSheet ws = wwb.createSheet("关联规则", 0);
for (int i = 0; i < model.getColumnCount() - 1; i++) {
jxl.write.Label labelN = new jxl.write.Label(i, 0, model.getColumnName(i + 1));
try {
ws.addCell(labelN);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException we) {
we.printStackTrace();
}
}
int row = 1;
for (int j = 1; j <= model.getRowCount(); ++j) {
if (model.getValueAt(j - 1, 0).equals(true)) {
for (int i = 0; i < model.getColumnCount() - 1; ++i) {
jxl.write.Label labelN =
new jxl.write.Label(i, row, model.getValueAt(j - 1, i + 1).toString());
try {
ws.addCell(labelN);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException we) {
we.printStackTrace();
}
}
row++;
}
}
wwb.write();
try {
wwb.close();
out.close();
} catch (WriteException e) {
e.printStackTrace();
}
} catch (Exception e) {
}
}
public static void addRow(
WritableSheet sheet, int startRow, CellValue[] cellValues, WritableFont writableFont)
throws WriteException {
WritableCellFormat cellFormat = new WritableCellFormat(writableFont);
cellFormat.setWrap(true);
cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
NumberFormat towdps = new NumberFormat("#,##0");
WritableCellFormat towdpsFormat = new WritableCellFormat(writableFont, towdps);
towdpsFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
NumberFormat towdps2 = new NumberFormat("#,##0.00");
WritableCellFormat towdpsFormat2 = new WritableCellFormat(writableFont, towdps2);
towdpsFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
DateFormat customDateFormat = new DateFormat("dd MMM yyyy");
WritableCellFormat dateFormat = new WritableCellFormat(writableFont, customDateFormat);
dateFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
for (int i = 0; i < cellValues.length; i++) {
if (cellValues[i] != null && cellValues[i].getValue() != null) {
if (cellValues[i].getType().equals(CellDataType.STRING)) {
Label label =
new Label(i, startRow, String.valueOf(cellValues[i].getValue()), cellFormat);
sheet.addCell(label);
} else if (cellValues[i].getType().equals(CellDataType.INT)) {
Number number = new Number(i, startRow, (Integer) cellValues[i].getValue(), towdpsFormat);
sheet.addCell(number);
} else if (cellValues[i].getType().equals(CellDataType.DOUBLE)) {
Number number = new Number(i, startRow, (Double) cellValues[i].getValue(), towdpsFormat2);
sheet.addCell(number);
} else if (cellValues[i].getType().equals(CellDataType.DATE)) {
Date now = new Date(((Timestamp) cellValues[i].getValue()).getTime());
DateTime dateCell = new DateTime(i, startRow, now, dateFormat);
sheet.addCell(dateCell);
}
} else {
Label label = new Label(i, startRow, "", cellFormat);
sheet.addCell(label);
}
}
}
public void exportall() {
try {
ByteArrayOutputStream b = new ByteArrayOutputStream();
WritableWorkbook w = Workbook.createWorkbook(b);
WritableSheet s = w.createSheet("Sheet", 0);
int row = 0;
s.addCell(new Label(0, row++, "Country Code"));
s.addCell(new Label(0, row++, "Country Name: "));
w.write();
w.close();
byte[] d = b.toByteArray();
redirectUrl(d);
} catch (Exception ex) {
ex.printStackTrace();
}
}
private int vill(int column, int row, WritableSheet sheet, int station_id, String title)
throws SQLException, RowsExceededException, WriteException {
sheet.addCell(new Label(column, row, title, font.tahomaLabelTitle));
Vector<Integer> stationsComm = new Vector<Integer>();
stationsComm.add(station_id);
String[][] value = ave(stationsComm);
osn(column, row + 1, sheet, value);
return column + 1;
}
/**
* @param sheet 工作表
* @param list 数据源数据
* @param fieldMap 中英文属性对照关系map
* @param firstIndex 开始索引
* @param lastIndex 结束索引
* @param <E>
* @throws Exception @MethodName : fillSheet @Description : 向工作表中填充数据
*/
private static <E> void fillSheet(
WritableSheet sheet,
List<E> list,
LinkedHashMap<String, String> fieldMap,
int firstIndex,
int lastIndex)
throws Exception {
// 定义存放英文字段名和中文字段名的数组
int size = fieldMap.size();
String[] enFields = new String[size];
String[] cnFields = new String[size];
// 填充数组
int count = 0;
for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
enFields[count] = entry.getKey();
cnFields[count] = entry.getValue();
count++;
}
// 填充表头
for (int i = 0; i < cnFields.length; i++) {
Label label = new Label(i, 0, cnFields[i]);
sheet.addCell(label);
}
// 填充内容
int rowNo = 1;
for (int index = firstIndex; index <= lastIndex; index++) {
E item = list.get(index);
for (int i = 0; i < enFields.length; i++) {
Object objValue = getFieldValueByNameSequence(enFields[i], item);
String fieldValue = objValue == null ? "" : objValue.toString();
Label label = new Label(i, rowNo, fieldValue);
sheet.addCell(label);
}
rowNo++;
}
// 设置自动列宽
setColumnAutoSize(sheet, 5);
}
public static void getFailWord() throws Exception {
ArrayList<String> failword = getWord();
WritableWorkbook wb = Workbook.createWorkbook(new File("imagedata/baike/HTML/failWord1.xls"));
WritableSheet sheet = wb.createSheet("failWords", 0);
if (failword.size() != 0) {
for (int i = 0; i < failword.size(); i++) {
sheet.addCell(new Label(0, i, failword.get(i)));
}
}
wb.write();
wb.close();
}
/**
* 写入公式
*
* @param colNum 列号
* @param rowNum 行号
* @param formula 写入公式内容
*/
public void setFormula(int colNum, int rowNum, String formula) {
try {
wrCurrentSheet.addCell(new Formula(6, 3, formula));
} catch (RowsExceededException e) {
logger.error(e.getMessage());
throw new BaseException(e);
} catch (WriteException e) {
logger.error(e.getMessage());
throw new BaseException(e);
}
}
public static void write2Cell(WritableSheet ws, int c, int r, String item) {
// System.out.println(index+r);
// 这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行
Label labelC = new Label(c, r, item);
try {
// 将生成的单元格添加到工作表中
ws.addCell(labelC);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}