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;
}
/**
* 采用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 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 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 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);
}
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)));
}
}
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();
}
}
}
/**
* @param sheet
* @param numrow
* @return
*/
public static ArrayList readrow(WritableSheet sheet, int numrow) {
ArrayList contentlist = new ArrayList();
for (int numcol = 0; numcol < sheet.getColumns(); numcol++) {
Cell cell = sheet.getCell(numcol, numrow);
String content = cell.getContents();
contentlist.add(content);
}
return contentlist;
}
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();
}
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;
}
public String exportReaderBorrowedExcel(String rootDir, ReaderView readerView) {
List<Reader> readers = new ArrayList<Reader>();
WritableWorkbook ww;
String fileName = "upload" + File.separator + "readerBorrowedsStatics.xls";
File file = new File(rootDir + fileName);
try {
readers = readerDao.selectReaders(readerView);
ww = Workbook.createWorkbook(file);
WritableSheet ws = ww.createSheet("读者借阅信息统计", 0);
ExcelOperate.addLabelToSheet(ws, 0, 0, 9, 0, "读者借阅信息统计", ExcelStyle.getHeaderStyle());
ExcelOperate.addLabelToSheet(ws, 0, 1, "借阅证号", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 1, 1, "条形码", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 2, 1, "读者姓名", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 3, 1, "姓别", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 4, 1, "当前借阅数量", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 5, 1, "累计借阅数量", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 6, 1, "读者单位", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 7, 1, "读者类别", ExcelStyle.getTitleStyle());
int count = 2;
for (Reader reader : readers) {
ExcelOperate.addLabelToSheet(
ws, 0, count, reader.getCardNo(), ExcelStyle.getContentStyle());
ExcelOperate.addLabelToSheet(
ws, 1, count, reader.getBarCode(), ExcelStyle.getContentStyle());
ExcelOperate.addLabelToSheet(
ws, 2, count, reader.getReaderName(), ExcelStyle.getContentStyle());
ExcelOperate.addLabelToSheet(ws, 3, count, reader.getSex(), ExcelStyle.getContentStyle());
ExcelOperate.addLabelToSheet(
ws, 4, count, reader.getBorrowedQuantiy(), ExcelStyle.getContentStyle());
ExcelOperate.addLabelToSheet(
ws, 5, count, reader.getTotalBQuantity(), ExcelStyle.getContentStyle());
ExcelOperate.addLabelToSheet(
ws, 6, count, reader.getReaderUnits().getUnitName(), ExcelStyle.getContentStyle());
ExcelOperate.addLabelToSheet(
ws, 7, count, reader.getReaderType().getReaderCateName(), ExcelStyle.getContentStyle());
count++;
}
for (int i = 0; i < 8; i++) {
ws.setColumnView(i, 16);
}
ws.setRowView(0, 20);
ww.write();
ww.close();
System.out.println("写入excel成功！");
} catch (Exception e) {
System.out.println("写入excel失败！");
e.printStackTrace();
}
return fileName;
}
public static int nextFreeColNumber(WritableSheet sheet) {
if (sheet == null) return 0;
int colCount = sheet.getColumns();
Cell[] cells;
int i = 0;
for (; i < colCount; i++) {
cells = sheet.getColumn(i);
for (Cell cell : cells) {
if (cell.getContents() == null || cell.getContents().isEmpty()) return i;
}
}
return i;
}
private int pro5(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ИП Палашичев", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 1, row);
ave_k.add(column);
column = vill(column, row + 1, sheet, 199, "г. Калуга");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 9, "Бабынинский р-н");
return column;
}
/**
* 添加单元格&内容.
*
* @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;
}
}
private int pro8(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ИП Пешков", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 2, row);
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 144, "Сухиничский р-н");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 155, "Ульяновский р-н");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 138, "Перемышельский р-н");
return column;
}
private int pro3(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ООО ТД \"Альфа-Трейд\"", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 1, row);
ave_k.add(column);
column = vill(column, row + 1, sheet, 195, "г. Калуга");
// sheet.addCell(new Label(column, row + 1, "в т.ч. мини АЗС",
// font.tahomaLabelTitle));
// column++;
column = vill(column, row + 1, sheet, 254, "в т.ч. мини АЗС");
return column;
}
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;
}
/** @param ws @MethodName : setColumnAutoSize @Description : 设置工作表自动列宽和首行加粗 */
private static void setColumnAutoSize(WritableSheet ws, int extraWith) {
// 获取本列的最宽单元格的宽度
for (int i = 0; i < ws.getColumns(); i++) {
int colWith = 0;
for (int j = 0; j < ws.getRows(); j++) {
String content = ws.getCell(i, j).getContents().toString();
int cellWith = content.length();
if (colWith < cellWith) {
colWith = cellWith;
}
}
// 设置单元格的宽度为最宽宽度+额外宽度
ws.setColumnView(i, colWith + extraWith);
}
}
private int pro4(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(
new Label(column, row, "ООО \"Луйкойл-Центрнефтепродукт\"", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 1, row);
ave_k.add(column);
column = vill(column, row + 1, sheet, 255, "г. Калуга розница");
ave_obl_all.add(column);
column = vill(column, row + 1, sheet, 256, "Область розница");
return column;
}
/**
* 获得工作表中特定行的数据,比如表头,隐藏行
*
* @param row
* @param wb
* @param clazz
* @return
*/
public static String[] GetXlsSheetRowData(int row, WritableSheet sheet, Class clazz) {
List<String> list = new ArrayList<String>();
int cols = sheet.getColumns();
// 添加该行所有元素
for (int col = 0; col < cols; col++) {
String value = StringValue(sheet.getCell(col, row).getContents());
list.add(value);
}
// 删除末尾空列
for (int col = list.size() - 1; col >= 0; col--) if (isEmpty(list.get(col))) list.remove(col);
// 转换为字符串数组
String[] stringArray = new String[list.size()];
list.toArray(stringArray);
return stringArray;
}
public String exportExcel(String rootDir, SysLogView sysLogView) {
List<SysLog> sysLogs = new ArrayList<SysLog>();
WritableWorkbook ww;
String fileName = "upload" + File.separator + "sysLogs.xls";
File file = new File(rootDir + fileName);
try {
sysLogs = sysLogDao.selectSysLogs(sysLogView);
ww = Workbook.createWorkbook(file);
WritableSheet ws = ww.createSheet("日志信息", 0);
ExcelOperate.addLabelToSheet(ws, 0, 0, 9, 0, "日志信息", ExcelStyle.getHeaderStyle());
ExcelOperate.addLabelToSheet(ws, 0, 1, "操作类型", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 1, 1, "事件", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 2, 1, "操作人", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 3, 1, "日期", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 4, 1, "ip地址", ExcelStyle.getTitleStyle());
ExcelOperate.addLabelToSheet(ws, 5, 1, "业务描述", ExcelStyle.getTitleStyle());
int count = 2;
for (SysLog sysLog : sysLogs) {
ExcelOperate.addLabelToSheet(
ws, 0, count, sysLog.getOperateType(), ExcelStyle.getContentStyle());
ExcelOperate.addLabelToSheet(
ws, 1, count, sysLog.getFuncName(), ExcelStyle.getContentStyle());
ExcelOperate.addLabelToSheet(
ws, 2, count, sysLog.getOperator(), ExcelStyle.getContentStyle());
String operateDate = DateFormatUtil.format(sysLog.getOperateDate(), "yyyy-MM-dd");
ExcelOperate.addLabelToSheet(
ws, 3, count, DateFormatUtil.convertToDate(operateDate), ExcelStyle.getDateStyle());
ExcelOperate.addLabelToSheet(
ws, 4, count, sysLog.getOperateIPAddress(), ExcelStyle.getContentStyle());
ExcelOperate.addLabelToSheet(
ws, 5, count, sysLog.getOperateDescription(), ExcelStyle.getContentStyle());
count++;
}
for (int i = 0; i < 6; i++) {
ws.setColumnView(i, 16);
}
ws.setRowView(0, 20);
ww.write();
ww.close();
System.out.println("写入excel成功！");
} catch (Exception e) {
System.out.println("写入excel失败！");
e.printStackTrace();
}
return fileName;
}
public void executeLast(Hashtable _tagLibrary, Hashtable _beanLibrary) {
try {
bean _sysPdfCC = (bean) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_CurrentCELL);
bean _sysPdfCR = (bean) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_CurrentROW);
int X = 0;
try {
X = ((Integer) _sysPdfCC.getContent()).intValue();
} catch (Exception e) {
}
int Y = 0;
try {
Y = ((Integer) _sysPdfCR.getContent()).intValue();
} catch (Exception e) {
}
WritableSheet document =
(WritableSheet)
(((report_element_base) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_Document))
.getContent());
((java.util.Vector)
(((report_element_base) _beanLibrary.get("SYSTEM:" + iConst.iHORT_SYSTEM_Canvas))
.getContent()))
.add(this.getCellC(document.getWritableCell(X, Y), X, Y));
try {
if (!internal_style.getWIDTH().equals(""))
document.setColumnView(X, new Integer(internal_style.getWIDTH()).intValue());
} catch (Exception e) {
}
if (!internal_style.getHEIGHT().equals("") && parent != null && parent instanceof table_row)
((table_row) parent).setHEIGHT(internal_style.getHEIGHT());
X++;
_sysPdfCC.setContent(new Integer(X));
_beanLibrary.put(_sysPdfCC.getName() + ":" + _sysPdfCC.getID(), _sysPdfCC);
if (_tagLibrary.get(getName() + ":" + getID()) == null)
_tagLibrary.remove(getName() + ":" + getID() + "_ids_" + this.motore.hashCode());
else _tagLibrary.remove(getName() + ":" + getID());
} catch (Exception e) {
setError(e, iStub.log_WARN);
}
}
/**
* 取得列数
*
* @return 列数
*/
public int getCols() {
if (readOnlyWBook == true) {
return currentSheet.getColumns();
} else {
return wrCurrentSheet.getColumns();
}
}
/**
* @param ws
* @param numrow
* @param contentList
* @return
*/
public static boolean writebyrow(WritableSheet ws, int numrow, ArrayList contentList) {
try {
for (int i = 0; i < contentList.size(); i++) {
System.out.println(".....ws=" + ws.getName());
String cellcontent = (String) contentList.get(i);
System.out.println(".....cellcontent=" + cellcontent);
jxl.write.Label labelC = new jxl.write.Label(i, numrow, cellcontent);
ws.addCell(labelC);
}
} catch (Exception e) {
System.out.println("Exception is " + e);
}
return true;
}
/**
* 替换xls模板中${vo.id}的字段
*
* @param book
* @param dataVO
* @param voName
* @throws Exception
*/
public static void createCellByOgnl(WritableSheet sheet, Object dataVO, String voName)
throws Exception {
// OGNL替换
String field;
for (int row = 0; row < 100; row++) {
for (int col = 0; col < 50; col++) {
WritableCell c = sheet.getWritableCell(col, row);
if (c.getClass() == EmptyCell.class || c.getClass() == Blank.class) continue;
Label label = (Label) c;
String label_str = GetTrimString(label.getString());
if (!isEmpty(label.getString()) && label_str.startsWith("${" + voName + ".")) {
String value = C.EMPTY_STRING;
try {
field =
label_str.replaceAll("\\$\\{vo\\.", C.EMPTY_STRING).replaceAll("}", C.EMPTY_STRING);
value = (String) UtilTool.GetFieldValue(dataVO, field);
} catch (Exception e) {
e.printStackTrace();
}
// ognl表达式的单元格必须被替换
label.setString(value);
}
}
}
}
/**
* 取得一列的cell
*
* @param rowNum 行号
* @return 一列的cell
*/
public Cell[] getColCells(int colNum) {
if (readOnlyWBook == true) {
return currentSheet.getColumn(colNum);
} else {
return wrCurrentSheet.getColumn(colNum);
}
}
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();
}
}
}
private int pro11(int column, int row, WritableSheet sheet)
throws RowsExceededException, WriteException, SQLException {
sheet.addCell(new Label(column, row, "ООО \"Восток-Ойл\"", font.tahoma9ptBoldMedion));
sheet.mergeCells(column, row, column + 3, row);
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 133, "г. Мосальск");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 123, "Малоярославецкий р-н");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 130, "Мещовский р-н");
ave_obl_reg.add(column);
column = vill(column, row + 1, sheet, 94, "Куйбышевский р-н");
return column;
}
@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;
}