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);
}
/**
* 取得列数
*
* @return 列数
*/
public int getCols() {
if (readOnlyWBook == true) {
return currentSheet.getColumns();
} else {
return wrCurrentSheet.getColumns();
}
}
/**
* @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 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;
}
/**
* 获得工作表中特定行的数据,比如表头,隐藏行
*
* @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;
}
/** @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);
}
}
/**
* 生成一张盘点表格
*
* @return
* @throws Exception
*/
public String createCheckingExcel() throws Exception {
if (date == null || date.trim().equals("")) {
date = DateTool.getInstance().DateToPattern1(new Date());
}
warehouses = this.warehouseService.findByKeyword("");
if (warehouse != null && warehouse.getId() != 0) {
warehouse = this.warehouseService.get(Warehouse.class, warehouse.getId());
stockCheckingList =
this.stockCheckingService.findCheckingListByDateAndWarehouse(date, warehouse.getId());
} else {
stockCheckingList = this.stockCheckingService.findCheckingListByDate(date);
}
ByteOutputStream outputStream = new ByteOutputStream();
WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
WritableSheet sheet = workbook.createSheet("盘点记录表", 0);
int row = 0;
{
Label label01 = new Label(0, row, "仓库");
sheet.addCell(label01);
if (warehouse.getId() != 0) {
Label label02 = new Label(1, row, warehouse.getWnickname());
sheet.addCell(label02);
} else {
Label label02 = new Label(1, row, "全部");
sheet.addCell(label02);
}
}
row++;
{
Label label01 = new Label(0, row, "行");
Label label02 = new Label(1, row, "产品编码*");
Label label03 = new Label(2, row, "产品型号*");
Label label04 = new Label(3, row, "盘点前数量");
Label label05 = new Label(4, row, "盘点数量");
Label label06 = new Label(5, row, "差额");
Label label07 = new Label(6, row, "盘点人");
Label label08 = new Label(7, row, "时间");
sheet.addCell(label01);
sheet.addCell(label02);
sheet.addCell(label03);
sheet.addCell(label04);
sheet.addCell(label05);
sheet.addCell(label06);
sheet.addCell(label07);
sheet.addCell(label08);
}
row++;
for (int i = 0; i < stockCheckingList.size(); i++) {
StockChecking checking = stockCheckingList.get(i);
Label label01 = new Label(0, row, "" + (i + 1));
Label label02 = new Label(1, row, checking.getProductInfo().getBarcode());
Label label03 = new Label(2, row, checking.getProductInfo().getPdesc());
jxl.write.Number label04 = new jxl.write.Number(3, row, checking.getQuantity_before());
jxl.write.Number label05 = new jxl.write.Number(4, row, checking.getQuantity_after());
jxl.write.Number label06 =
new jxl.write.Number(
5, row, checking.getQuantity_after() - checking.getQuantity_before());
Label label07 = new Label(6, row, checking.getOperator());
Label label08 = new Label(7, row, checking.getUpdatetime());
sheet.addCell(label01);
sheet.addCell(label02);
sheet.addCell(label03);
sheet.addCell(label04);
sheet.addCell(label05);
sheet.addCell(label06);
sheet.addCell(label07);
sheet.addCell(label08);
row++;
}
for (int i = 0; i < sheet.getColumns(); i++) {
sheet.setColumnView(i, 30);
}
for (int i = 0; i < sheet.getRows(); i++) {
sheet.setRowView(i, 300);
}
workbook.write();
workbook.close();
response.reset();
response.setContentType("application/vn.ms-xls");
response.setCharacterEncoding("utf-8");
this.inputStream = new ByteArrayInputStream(outputStream.getBytes()); // 关键,以 inputstream 输出
return SUCCESS;
}