package com.zjasm.util;
import jxl.Sheet;
import jxl.format.CellFormat;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import org.apache.commons.lang.StringUtils;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Print {
/**
* 验证数字
*
* @param str
* @return
*/
public static boolean isNum(String str) {
return str.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");
}
/**
* 设置单元格
*
* @param sheet
* @param column
* @param row
* @param value
* @throws Exception
*/
public static void setCell(WritableSheet sheet, int column, int row, String value) throws Exception {
Print.setCellWithFormatBold(sheet, column, row, value); // 单元格设置
CellFormat ecf = sheet.getCell(column, row).getCellFormat(); // 获取指定单元格的设置格式
if (ecf == null) {
ecf = sheet.getCell(0, 0).getCellFormat();
}
WritableCell cl = new Label(column, row, value, ecf); // 创建一个label
sheet.addCell(cl);
}
/**
* 设置单元格格式
* 12号宋体 加粗 边框细线 单元格居中
* @param sheet
* @param column
* @param row
* @param value
* @throws Exception
*/
public static void setCellWithFormat(WritableSheet sheet, int column, int row, String value) throws Exception {
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD); // 字体设置 宋体 12号 加粗
WritableCellFormat format = new WritableCellFormat(font);
format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); // 边框线条
format.setAlignment(jxl.format.Alignment.CENTRE); // 单元格居中
format.setWrap(true); // 设置单元格内容自动换行
WritableCell cl = new Label(column, row, value, format);
sheet.addCell(cl);
}
/**
* 设置单元格格式--bold
*10号宋体 边框细线 单元格居中
* @param sheet
* @param column
* @param row
* @param value
* @throws Exception
*/
public static void setCellWithFormatBold(WritableSheet sheet, int column, int row, String value) throws Exception {
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false);
WritableCellFormat format = new WritableCellFormat(font);
format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); // 边框线条
format.setAlignment(jxl.format.Alignment.CENTRE); // 单元格居中
format.setWrap(true); // 设置单元格内容自动换行
WritableCell cl = new Label(column, row, value, format);
sheet.addCell(cl);
}
/**
* 合并单元格
* @param sheet
* @param startcol
* @param startrow
* @param endcol
* @param endrow
* @param value
*/
public static void setCellWithMerge(WritableSheet sheet, int startcol, int startrow, int endcol, int endrow, String value) {
try {
// mergeCells(a,b,c,d) 单元格合并函数 a单元格的列号 b单元格的行号 c从单元格[a,b]起,向下合并的列数 d从单元格[a,b]起,向下合并的行数
sheet.mergeCells(startcol, startrow, endcol, endrow);
// 设置单元格格式
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 14, WritableFont.BOLD); // 字体设置
WritableCellFormat format = new WritableCellFormat(font);
format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); // 边框线条
format.setAlignment(jxl.format.Alignment.CENTRE); // 单元格居中
WritableCell cell = new Label(startcol, startrow, value, format);
sheet.addCell(cell);
} catch (WriteException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
}
/**
* 获取当前日期字符串
*
* @return
*/
public static String getStringDate() {
Date currentTime = new Date();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateString = formatter.format(currentTime);
System.out.println("TIME:::" + dateString);
return dateString.substring(0, 10);
}
/**
* 获取Excel表中有效行数
*
* @param sheet
* @return
*/
public static int getRightRows(Sheet sheet) {
int rsCols = sheet.getColumns(); //列数
int rsRows = sheet.getRows(); //行数
int nullCellNum;
int afterRows = rsRows;
for (int i = 1; i < rsRows; i++) { //统计行中为空的单元格数
nullCellNum = 0;
for (int j = 0; j < rsCols; j++) {
String val = sheet.getCell(j, i).getContents();
val = StringUtils.trimToEmpty(val);
if (StringUtils.isBlank(val))
nullCellNum++;
}
if (nullCellNum >= rsCols) { //如果nullCellNum大于或等于总的列数
afterRows--; //行数减一
}
}
return afterRows;
}
}
import jxl.Sheet;
import jxl.format.CellFormat;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import org.apache.commons.lang.StringUtils;
import java.text.SimpleDateFormat;
import java.util.Date;
public class Print {
/**
* 验证数字
*
* @param str
* @return
*/
public static boolean isNum(String str) {
return str.matches("^[-+]?(([0-9]+)([.]([0-9]+))?|([.]([0-9]+))?)$");
}
/**
* 设置单元格
*
* @param sheet
* @param column
* @param row
* @param value
* @throws Exception
*/
public static void setCell(WritableSheet sheet, int column, int row, String value) throws Exception {
Print.setCellWithFormatBold(sheet, column, row, value); // 单元格设置
CellFormat ecf = sheet.getCell(column, row).getCellFormat(); // 获取指定单元格的设置格式
if (ecf == null) {
ecf = sheet.getCell(0, 0).getCellFormat();
}
WritableCell cl = new Label(column, row, value, ecf); // 创建一个label
sheet.addCell(cl);
}
/**
* 设置单元格格式
* 12号宋体 加粗 边框细线 单元格居中
* @param sheet
* @param column
* @param row
* @param value
* @throws Exception
*/
public static void setCellWithFormat(WritableSheet sheet, int column, int row, String value) throws Exception {
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD); // 字体设置 宋体 12号 加粗
WritableCellFormat format = new WritableCellFormat(font);
format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); // 边框线条
format.setAlignment(jxl.format.Alignment.CENTRE); // 单元格居中
format.setWrap(true); // 设置单元格内容自动换行
WritableCell cl = new Label(column, row, value, format);
sheet.addCell(cl);
}
/**
* 设置单元格格式--bold
*10号宋体 边框细线 单元格居中
* @param sheet
* @param column
* @param row
* @param value
* @throws Exception
*/
public static void setCellWithFormatBold(WritableSheet sheet, int column, int row, String value) throws Exception {
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false);
WritableCellFormat format = new WritableCellFormat(font);
format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); // 边框线条
format.setAlignment(jxl.format.Alignment.CENTRE); // 单元格居中
format.setWrap(true); // 设置单元格内容自动换行
WritableCell cl = new Label(column, row, value, format);
sheet.addCell(cl);
}
/**
* 合并单元格
* @param sheet
* @param startcol
* @param startrow
* @param endcol
* @param endrow
* @param value
*/
public static void setCellWithMerge(WritableSheet sheet, int startcol, int startrow, int endcol, int endrow, String value) {
try {
// mergeCells(a,b,c,d) 单元格合并函数 a单元格的列号 b单元格的行号 c从单元格[a,b]起,向下合并的列数 d从单元格[a,b]起,向下合并的行数
sheet.mergeCells(startcol, startrow, endcol, endrow);
// 设置单元格格式
WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 14, WritableFont.BOLD); // 字体设置
WritableCellFormat format = new WritableCellFormat(font);
format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN); // 边框线条
format.setAlignment(jxl.format.Alignment.CENTRE); // 单元格居中
WritableCell cell = new Label(startcol, startrow, value, format);
sheet.addCell(cell);
} catch (WriteException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
}
/**
* 获取当前日期字符串
*
* @return
*/
public static String getStringDate() {
Date currentTime = new Date();
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateString = formatter.format(currentTime);
System.out.println("TIME:::" + dateString);
return dateString.substring(0, 10);
}
/**
* 获取Excel表中有效行数
*
* @param sheet
* @return
*/
public static int getRightRows(Sheet sheet) {
int rsCols = sheet.getColumns(); //列数
int rsRows = sheet.getRows(); //行数
int nullCellNum;
int afterRows = rsRows;
for (int i = 1; i < rsRows; i++) { //统计行中为空的单元格数
nullCellNum = 0;
for (int j = 0; j < rsCols; j++) {
String val = sheet.getCell(j, i).getContents();
val = StringUtils.trimToEmpty(val);
if (StringUtils.isBlank(val))
nullCellNum++;
}
if (nullCellNum >= rsCols) { //如果nullCellNum大于或等于总的列数
afterRows--; //行数减一
}
}
return afterRows;
}
}