/**
* 写入公式
*
* @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();
}
}
/**
* 写入数值
*
* @param colNum 列号
* @param rowNum 行号
* @param num 数值
*/
public void setNumber(int colNum, int rowNum, double num) {
jxl.write.Number number = new jxl.write.Number(colNum, rowNum, num);
try {
// 写入数值
wrCurrentSheet.addCell(number);
} catch (RowsExceededException e) {
logger.error(e.getMessage());
throw new BaseException(e);
} catch (WriteException e) {
logger.error(e.getMessage());
throw new BaseException(e);
}
}
/**
* 写入文本值
*
* @param colNum 列号
* @param rowNum 行号
* @param sText 文本内容
*/
public void setText(int colNum, int rowNum, String sText) {
Label labelc = new Label(colNum, rowNum, sText);
try {
// 写入文本值
wrCurrentSheet.addCell(labelc);
} catch (RowsExceededException e) {
logger.error(e.getMessage());
throw new BaseException(e);
} catch (WriteException e) {
logger.error(e.getMessage());
throw new BaseException(e);
}
}
@Test
public void testExportToCollection() throws BiffException, IOException {
ExcelUtil excelUtil = new ExcelUtil();
excelUtil.openUploadExcel("collectionTemplate.xls", 0);
ModelCollection collection = excelUtil.getCollection();
System.out.println(collection);
excelUtil.closeExcel();
excelUtil.openDownloadNewExcel("collectionTemplate.xls", 0);
try {
excelUtil.exoprtToExcel(collection);
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void removeAll(List<? extends Persistable> objList) {
try {
xclAdaptee.write(file, new ArrayList<ArrayList<String>>());
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/** 导出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) {
}
}
/**
* 写入日期型
*
* @param colNum 列号
* @param rowNum 行号
* @param dt 日期或时间
* @param dateformat “YYY-MM-dd” "YYYY-MM-dd hh:mm：ss" 日期或时间格式
*/
public void setDate(int colNum, int rowNum, Date dt, String dateformat) {
try {
// 定义日期格式
DateFormat df = new DateFormat(dateformat);
// 定义cell的日期格式
WritableCellFormat wcfDF = new WritableCellFormat(df);
DateTime labelDTF = new DateTime(colNum, rowNum, dt, wcfDF);
// 写入日期
wrCurrentSheet.addCell(labelDTF);
} catch (RowsExceededException e) {
logger.error(e.getMessage());
throw new BaseException(e);
} catch (WriteException e) {
logger.error(e.getMessage());
throw new BaseException(e);
}
}
/**
* 生成一个Excel文件
*
* @param fileName 要生成的Excel文件名
*/
public static void writeExcel(String fileName) {
WritableWorkbook wwb = null;
try {
// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
wwb = Workbook.createWorkbook(new File(fileName));
} catch (IOException e) {
e.printStackTrace();
}
if (wwb != null) {
// 创建一个可写入的工作表
// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
WritableSheet ws = wwb.createSheet("sheet1", 0);
// 下面开始添加单元格
for (int i = 0; i < 10; i++) {
for (int j = 0; j < 5; j++) {
// 这里需要注意的是，在Excel中，第一个参数表示列，第二个表示行
Label labelC = new Label(j, i, "这是第" + (i + 1) + "行，第" + (j + 1) + "列");
try {
// 将生成的单元格添加到工作表中
ws.addCell(labelC);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
try {
// 从内存中写入文件中
wwb.write();
// 关闭资源，释放内存
wwb.close();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public void addData(DataChannel c) {
try {
int col = 1;
addLabel(lineOffset, 0, c.toString() + " Time (ms)");
addLabel(lineOffset + 1, 0, c.toString() + " Value");
for (Object o : c.getSeries().getItems()) {
XYDataItem i = (XYDataItem) o;
addNumber(lineOffset, col, i.getXValue());
addNumber(lineOffset + 1, col, i.getYValue());
col++;
}
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
lineOffset += 2;
}
/**
* The doPost method of the servlet. <br>
* This method is called when a form has its tag value method equals to post.
*
* @param request the request send by the client to the server
* @param response the response send by the server to the client
* @throws ServletException if an error occurred
* @throws IOException if an error occurred
*/
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=gb2312");
request.setCharacterEncoding("gb2312");
HttpSession session = request.getSession();
ArrayList adminlogin = (ArrayList) session.getAttribute("adminlogin");
shuchu shu = new shuchu();
try {
shu.shuchu(adminlogin.get(1).toString());
request.setAttribute("message", "'成绩已打印（位置：D:/输出成绩.xls）'");
request
.getRequestDispatcher("/admin/myscore.jsp?verifyType=" + request.getParameter("name"))
.forward(request, response);
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void addAll(List<? extends Persistable> objList) {
ArrayList<ArrayList<String>> serializedList = new ArrayList<>();
for (Persistable p : objList) {
ArrayList<String> lineLs = p.serializeToStringArray();
serializedList.add(lineLs);
}
try {
xclAdaptee.write(file, serializedList);
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void run(IAction action) {
File file;
try {
view =
Activator.getDefault()
.getWorkbench()
.getActiveWorkbenchWindow()
.getActivePage()
.findView(AddressTreeView.ID);
file =
File.createTempFile(
"output_" + new Date().getTime(), ".xls"); // $NON-NLS-1$ //$NON-NLS-2$
WritableWorkbook workbook = Workbook.createWorkbook(file);
WritableSheet sheet = workbook.createSheet("Export", 0); // $NON-NLS-1$
sheet.addCell(new Label(0, 0, Messages.CompanyEditor_Label_Denomination));
sheet.addCell(new Label(1, 0, Messages.ScrolledCRMFormEditor_Label_Title));
sheet.addCell(new Label(2, 0, Messages.ScrolledCRMFormEditor_Label_Firstname));
sheet.addCell(new Label(3, 0, Messages.ScrolledCRMFormEditor_Label_Lastname));
sheet.addCell(new Label(4, 0, Messages.CompanyEditor_Label_Street));
sheet.addCell(new Label(5, 0, Messages.CompanyEditor_Label_Zip));
sheet.addCell(new Label(6, 0, Messages.CompanyEditor_Label_City));
sheet.addCell(new Label(7, 0, Messages.CompanyEditor_Label_State));
sheet.addCell(new Label(8, 0, Messages.CompanyEditor_Label_Country));
sheet.addCell(new Label(9, 0, Messages.CompanyEditor_Label_Phone));
sheet.addCell(new Label(10, 0, Messages.CompanyEditor_Label_Mobile));
sheet.addCell(new Label(11, 0, Messages.CompanyEditor_Label_Fax));
sheet.addCell(new Label(12, 0, Messages.CompanyEditor_Label_Email));
sheet.addCell(new Label(13, 0, Messages.CompanyEditor_Label_Website));
sheet.addCell(new Label(14, 0, Messages.CompanyEditor_Label_Note));
int i = 1;
Addressbook book =
(Addressbook) ConnectionFactory.getConnection("AddressbookBean"); // $NON-NLS-1$
for (Company company : ((AddressTreeView) view).getCompanies()) {
company = (Company) book.getAddress(company);
sheet.addCell(new Label(0, i, company.getDenomination()));
sheet.addCell(new Label(1, i, company.getContact_title()));
sheet.addCell(new Label(2, i, company.getContact_firstname()));
sheet.addCell(new Label(3, i, company.getContact_lastname()));
sheet.addCell(new Label(4, i, company.getStreet()));
sheet.addCell(new Label(5, i, company.getZip()));
sheet.addCell(new Label(6, i, company.getCity()));
sheet.addCell(new Label(7, i, company.getState()));
sheet.addCell(new Label(8, i, company.getCountry()));
sheet.addCell(new Label(9, i, company.getPhone()));
sheet.addCell(new Label(10, i, company.getMobilephone()));
sheet.addCell(new Label(11, i, company.getFax()));
sheet.addCell(new Label(12, i, company.getEmail()));
sheet.addCell(new Label(13, i, company.getWebsite()));
sheet.addCell(new Label(14, i, company.getNote()));
i++;
}
workbook.write();
workbook.close();
Program.launch(file.toString());
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ConnectionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Throwable t) {
// TODO Auto-generated catch block
t.printStackTrace();
}
}
/** {@inheritDoc} */
@Override
public String exportData(String baseUri, List<SecrecyStatisticsDto> list, District district) {
boolean flag = false;
int currentRow = 2;
JExcelUtils excelUtils = new JExcelUtils();
String path = baseUri + "exportExcel/staticDatas_" + System.currentTimeMillis() + ".xls";
File file = new File(path);
String sheetName = district.getDistrictName() + "级机关单位数据录入情况一览表";
try {
BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(file));
WritableWorkbook workbook = Workbook.createWorkbook(bos);
WritableSheet sheet = workbook.createSheet(sheetName, 0);
excelUtils.initialSheetSetting(sheet);
// 第一行 组装
Label labelIndex = new Label(0, 0, "单位名称", excelUtils.getTitleCellFormat());
sheet.addCell(labelIndex);
for (int i = 1; i < 5; i++) {
Label label = new Label(i, 0, "保密工作机构", excelUtils.getTitleCellFormat());
sheet.mergeCells(1, 0, 4, 0);
sheet.addCell(label);
}
for (int i = 5; i < 11; i++) {
Label label = new Label(i, 0, "保密业务", excelUtils.getTitleCellFormat());
sheet.mergeCells(5, 0, 10, 0);
sheet.addCell(label);
}
// 第二行 组装
Label label0 = new Label(0, 1, "单位名称", excelUtils.getTitleCellFormat());
sheet.addCell(label0);
for (int i = 1; i < 3; i++) {
Label label = new Label(i, 1, "机构信息", excelUtils.getTitleCellFormat());
sheet.mergeCells(1, 1, 2, 1);
sheet.addCell(label);
}
Label label3 = new Label(3, 1, "机构成员", excelUtils.getTitleCellFormat());
sheet.addCell(label3);
Label label4 = new Label(4, 1, "保密办成员", excelUtils.getTitleCellFormat());
sheet.addCell(label4);
for (int i = 5; i < 7; i++) {
Label label = new Label(i, 1, "要害部门", excelUtils.getTitleCellFormat());
sheet.mergeCells(5, 1, 6, 1);
sheet.addCell(label);
}
for (int i = 7; i < 9; i++) {
Label label = new Label(i, 1, "要害部位", excelUtils.getTitleCellFormat());
sheet.mergeCells(7, 1, 8, 1);
sheet.addCell(label);
}
for (int i = 9; i < 11; i++) {
Label label = new Label(i, 1, "涉密人员", excelUtils.getTitleCellFormat());
sheet.mergeCells(9, 1, 10, 1);
sheet.addCell(label);
}
// 第三行标题
for (int i = 0; i < dataTitles.length; i++) {
Label label = new Label(i, 2, dataTitles[i], excelUtils.getTitleCellFormat());
sheet.addCell(label);
}
// 统计数字
Integer num1 = 0;
Integer num2 = 0;
Integer num3 = 0;
Integer num4 = 0;
Integer num5 = 0;
Integer num6 = 0;
Integer num7 = 0;
Integer num8 = 0;
Integer num9 = 0;
Integer num10 = 0;
// 数据行
for (SecrecyStatisticsDto ob : list) {
currentRow += 1;
String[] data =
new String[] {
ob.getOrganName(),
ob.getNumGroupEntering() + "",
ob.getNumGroupReprot() + "",
ob.getNumGroupMember() + "",
ob.getNumSecrecyWorkOrganMember() + "",
ob.getNumKeysectionEntering() + "",
ob.getNumKeyPartReport() + "",
ob.getNumKeyPartEntering() + "",
ob.getNumKeyPartReport() + "",
ob.getNumSecrecyPersonEntering() + "",
ob.getNumSecrecyPersonReport() + ""
};
num1 += ob.getNumGroupEntering();
num2 += ob.getNumGroupReprot();
num3 += ob.getNumGroupMember();
num4 += ob.getNumSecrecyWorkOrganMember();
num5 += ob.getNumKeysectionEntering();
num6 += ob.getNumKeyPartReport();
num7 += ob.getNumKeyPartEntering();
num8 += ob.getNumKeyPartReport();
num9 += ob.getNumSecrecyPersonEntering();
num10 += ob.getNumSecrecyPersonReport();
excelUtils.insertRowData(
sheet, currentRow, data, excelUtils.getDataCellFormat(CellType.LABEL));
}
// 最后一行 组装
currentRow = currentRow + 1;
String[] dataLast =
new String[] {
"统计结果", num1 + "", num2 + "", num3 + "", num4 + "", num5 + "", num6 + "", num7 + "",
num8 + "", num9 + "", num10 + ""
};
excelUtils.insertRowData(
sheet, currentRow, dataLast, excelUtils.getDataCellFormat(CellType.LABEL));
workbook.write();
workbook.close();
flag = true;
} catch (FileNotFoundException e) {
LOGGER.error(e.getMessage(), e);
} catch (IOException e) {
LOGGER.error(e.getMessage(), e);
} catch (RowsExceededException e) {
LOGGER.error(e.getMessage(), e);
} catch (WriteException e) {
LOGGER.error(e.getMessage(), e);
}
return (flag ? path : null);
}