private void createStyles(HSSFWorkbook workBook, HSSFSheet workSheet) {
/*Font bold = workBook.createFont();
bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
bold.setFontHeightInPoints((short) 10);
bold.setColor(Font.COLOR_RED);*/
HSSFFont hssfFont = workBook.createFont();
hssfFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
hssfFont.setColor(Font.COLOR_RED);
boldStyle = workBook.createCellStyle();
boldStyle.setBorderBottom(CellStyle.BORDER_THIN);
boldStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
boldStyle.setFont(hssfFont);
boldStyle.setFillBackgroundColor(HSSFColor.BLUE.index);
defaultFont = workBook.createFont();
defaultFont.setFontHeightInPoints((short) 10);
defaultFont.setFontName("Arial");
defaultFont.setColor(IndexedColors.BLACK.getIndex());
defaultFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
defaultFont.setItalic(true);
newStyle = workBook.createCellStyle();
// newStyle.setFillBackgroundColor(IndexedColors.DARK_GREEN.getIndex());
// newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
newStyle.setAlignment(CellStyle.ALIGN_CENTER);
newStyle.setFont(defaultFont);
/* hssfCellStyle = workBook.createCellStyle();
hssfCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
hssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
hssfCellStyle.setFont(bold);*/
}
/**
* ************************************************************************* Public methods
* ************************************************************************
*/
@Override
public void open(String filepath) throws Exception {
this.filepath = filepath;
String author = ini.getValue("Excel", "Author", "Toël Hartmann");
String keywords = ini.getValue("Excel", "Keywords", "");
wb = new HSSFWorkbook();
// Set some properties
wb.createInformationProperties();
wb.getSummaryInformation().setAuthor(author);
wb.getSummaryInformation().setKeywords(keywords);
wb.getSummaryInformation().setCreateDateTime(new Date());
// Create the cell style for column titles
titleStyle = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(font);
String sheetName = getConfigValue("Excel", "sheet", null);
if (sheetName == null || sheetName.isEmpty())
sheetName = FileUtils.getFileNameWithoutExtention(filepath);
sheet = wb.createSheet(sheetName);
if (getConfigValue("Excel", "freezeFirstRow", "true").equalsIgnoreCase("true")) {
sheet.createFreezePane(0, 1);
}
}
public void createExcelFile(String filename) throws Exception {
out = new FileOutputStream(filename);
wb = new HSSFWorkbook();
ws = this.wb.createSheet();
cs1 = this.wb.createCellStyle();
cs2 = this.wb.createCellStyle();
cs3 = this.wb.createCellStyle();
df = this.wb.createDataFormat();
f1 = this.wb.createFont();
f2 = this.wb.createFont();
f1.setFontHeightInPoints((short) 10);
f1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
f2.setFontHeightInPoints((short) 13);
f2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
f2.setColor(HSSFFont.COLOR_RED);
cs1.setFont(f1);
cs1.setDataFormat(df.getFormat("text"));
cs2.setFont(f2);
cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
cs3.setFont(f1);
cs3.setDataFormat(df.getFormat("#,##0.0"));
wb.setSheetName(0, "QryDetail", HSSFWorkbook.ENCODING_UTF_16);
}
public void initStyles() {
// HSSFDataFormat format = workbook.createDataFormat();
redStyle = workbook.createCellStyle();
setBorder(redStyle, 1);
redStyle.setFillForegroundColor(HSSFColor.RED.index);
yellowStyle = workbook.createCellStyle();
setBorder(yellowStyle, 1);
yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
blueStyle = workbook.createCellStyle();
setBorder(blueStyle, 1);
blueStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
whiteStyle = workbook.createCellStyle();
setBorder(whiteStyle, 1);
whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index);
normalStyle = workbook.createCellStyle();
setBorder(normalStyle, 0);
// normalStyle.setFillForegroundColor(HSSFColor.WHITE.index);
normalPStyle = workbook.createCellStyle();
setBorder(normalPStyle, 0);
normalPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
// normalPStyle.setFillForegroundColor(HSSFColor.WHITE.index);
headerStyle = workbook.createCellStyle();
setBorder(headerStyle, 0);
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(font);
redPStyle = workbook.createCellStyle();
setBorder(redPStyle, 1);
redPStyle.setFillForegroundColor(HSSFColor.RED.index);
// redPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
redPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
yellowPStyle = workbook.createCellStyle();
setBorder(yellowPStyle, 1);
yellowPStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
// yellowPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
yellowPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
_fonts = new HashMap<String, HSSFFont>(2);
HSSFFont t13y_red = workbook.createFont();
t13y_red.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
t13y_red.setColor(HSSFFont.COLOR_RED);
_fonts.put("t13yr", t13y_red);
HSSFFont t13y_blue = workbook.createFont();
t13y_blue.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
t13y_blue.setColor(HSSFColor.BLUE_GREY.index);
_fonts.put("t13yb", t13y_blue);
}
private CoverageResults() {
fileName = "Coverage-results.xls";
workbook = new HSSFWorkbook();
allElementsSheet = workbook.createSheet("All Elements");
packagesSheet = workbook.createSheet("Packages");
boundClassesSheet = workbook.createSheet("Bound Classes");
allClassesSheet = workbook.createSheet("All Classes");
boundMethodsSheet = workbook.createSheet("Bound Methods");
allMethodsSheet = workbook.createSheet("All Methods");
normalFont = workbook.createFont();
normalFont.setFontName(HSSFFont.FONT_ARIAL);
normalFont.setFontHeightInPoints((short) 10);
boldFont = workbook.createFont();
boldFont.setFontName(HSSFFont.FONT_ARIAL);
boldFont.setFontHeightInPoints((short) 10);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
centerAlignmentCellStyle = workbook.createCellStyle();
centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
leftAlignmentCellStyle = workbook.createCellStyle();
leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
rightAlignmentCellStyle = workbook.createCellStyle();
rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
dataFormatCellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
}
/**
* Fills given row with headers
*
* @param wb
* @param sheet
* @param row
*/
private void fillHeaderRow(HSSFWorkbook wb, HSSFSheet sheet, HSSFRow row) {
// create style of header font
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 11);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
// here we could create array of strings, I mean headers
String[] headers = {
this.iwrb.getLocalizedString("school.child", "Child"),
this.iwrb.getLocalizedString("school.personal_id", "Personal ID"),
this.iwrb.getLocalizedString("school.e-mail", "Email"),
this.iwrb.getLocalizedString("school.address", "Address"),
this.iwrb.getLocalizedString("school.zip_code", "Zip code"),
this.iwrb.getLocalizedString("school.city", "City"),
this.iwrb.getLocalizedString("school.phone", "Phone"),
this.iwrb.getLocalizedString("school.gender", "Gender"),
this.iwrb.getLocalizedString("school.last_provider", "Last provider"),
this.iwrb.getLocalizedString("school.rejection_date", "Rejection date")
};
int[] headerWidths = {30, 14, 25, 25, 10, 16, 16, 8, 30, 16};
HSSFCell cell;
for (int i = 0; i < headers.length; i++) {
cell = row.createCell((short) i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
sheet.setColumnWidth((short) i, (short) (headerWidths[i] * 256));
}
}
/**
* Метод проверяет наличие в рабочей книге зарегистрированного фонта с указанными характеристиками
* и если он отсутствует то регистрирует его.
*
* @param wb рабочая книга в которой должен быть зарегистрирован требуемый фонт.
* @param font характеристики требуемого фонта.
* @return зарегистрированный в рабочей книге фонт с требуемыми характеристиками.
*/
public static HSSFFont ensureFontExists(final HSSFWorkbook wb, final Font font) {
final short colorId = font.getColor() != null ? font.getColor().getId() : 0;
HSSFFont f =
wb.findFont(
font.getBoldWeight(),
colorId,
font.getFontHeight(),
font.getFontName(),
font.isItalic(),
font.isStrikeout(),
font.getTypeOffset(),
font.getUnderline());
if (f == null) {
f = wb.createFont();
f.setBoldweight(font.getBoldWeight());
f.setCharSet(font.getCharSet());
f.setColor(colorId);
f.setFontHeight(font.getFontHeight());
f.setFontName(font.getFontName());
f.setItalic(font.isItalic());
f.setStrikeout(font.isStrikeout());
f.setTypeOffset(font.getTypeOffset());
f.setUnderline(font.getUnderline());
}
return f;
}
public void applyTo(HSSFFont font) {
font.setFontName(fontName);
font.setFontHeight(fontHeight);
font.setBoldweight(boldweight);
font.setItalic(italic);
font.setStrikeout(strikeout);
font.setTypeOffset(typeOffset);
font.setUnderline(underline);
font.setColor(color);
}
public ExcelSupport() {
super();
workbook = new HSSFWorkbook();
numericCellStyle = workbook.createCellStyle();
numericData = workbook.createDataFormat();
numericCellStyle.setDataFormat(numericData.getFormat("#,##0.00"));
percentageCellStyle = workbook.createCellStyle();
percentageCellStyle.setDataFormat((short) 4);
decimalCellStyle = workbook.createCellStyle();
decimalCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.0#####"));
multiLineTextStyle = workbook.createCellStyle();
multiLineTextStyle.setWrapText(true);
multiLineTextStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
genericStyle = workbook.createCellStyle();
genericStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
dollarStyle = workbook.createCellStyle();
dollarStyle.setDataFormat((short) 8);
headlineStyle = workbook.createCellStyle();
HSSFFont headlineFont = workbook.createFont();
// set font 1 to 12 point type
headlineFont.setFontHeightInPoints((short) 24);
// make it bold
// arial is the default font
headlineFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headlineStyle.setFont(headlineFont);
boldFont = workbook.createFont();
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
boldStyle = workbook.createCellStyle();
boldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
boldStyle.setFont(boldFont);
}
public HSSFCellStyle createInfoCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
/**
* Копирует все свойства исходного фонта в целевой. И исходный и целевой фонты должны относиться к
* одному и тому же документу Excel. Данный метод используется для того чтобы в последствии
* поменять в новом фонте один или несколько свойств не затрагивая при этом свойства исходного
* фонта.
*
* @param wb документ Excel.
* @param src фонт взятый в качестве шаблона.
* @return созданная в этом же документе копия шрифта.
*/
public static HSSFFont copyFont(final HSSFWorkbook wb, final HSSFFont src) {
final HSSFFont dst = wb.createFont();
dst.setBoldweight(src.getBoldweight());
dst.setCharSet(src.getCharSet());
dst.setColor(src.getColor());
dst.setFontHeight(src.getFontHeight());
dst.setFontName(src.getFontName());
dst.setItalic(src.getItalic());
dst.setStrikeout(src.getStrikeout());
dst.setTypeOffset(src.getTypeOffset());
dst.setUnderline(src.getUnderline());
return dst;
}
public HSSFCellStyle createTitleCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 16);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setUnderline(HSSFFont.U_SINGLE);
font.setColor(HSSFColor.GREY_80_PERCENT.index);
style.setFont(font);
return style;
}
public HSSFCellStyle createSubTitleCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 14);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.DARK_BLUE.index);
style.setFont(font);
return style;
}
public ExcelUtils(List items, ItemSearch itemSearch) {
this.wb = new HSSFWorkbook();
this.sheet = wb.createSheet("jtrac");
this.sheet.setDefaultColumnWidth((short) 12);
HSSFFont fBold = wb.createFont();
fBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
this.csBold = wb.createCellStyle();
this.csBold.setFont(fBold);
this.csDate = wb.createCellStyle();
this.csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
this.items = items;
this.itemSearch = itemSearch;
}
private static Map readFonts(NodeList fontList, HSSFWorkbook workbook)
throws ExcelTransformerException {
if (LOG.isLoggable(Level.FINE)) {
LOG.entering(
SimpleExcelRenderer.class.getName(), "readFonts", String.valueOf(fontList.getLength()));
}
Map results = new HashMap();
for (int i = 0; i < fontList.getLength(); i++) {
Element fontNode = (Element) fontList.item(i);
String name = fontNode.getAttribute("name");
HSSFFont font = workbook.createFont();
NodeList children = fontNode.getChildNodes();
for (int j = 0; j < children.getLength(); j++) {
Node child = children.item(j);
if (child.getNodeType() == Node.ELEMENT_NODE) {
Element childE = (Element) child;
String value = XmlUtils.getElementText(childE);
if (childE.getNodeName().equals("family")) {
font.setFontName(value);
} else {
try {
Field field = HSSFFont.class.getField(value);
if (childE.getNodeName().equals("boldweight")) {
font.setBoldweight(field.getShort(null));
}
} catch (Throwable th) {
throw new ExcelTransformerException(
"An error occurred while processing " + value + " on the excel font.");
}
}
}
}
results.put(name, font);
}
if (LOG.isLoggable(Level.FINE)) {
LOG.exiting(SimpleExcelRenderer.class.getName(), "readFonts");
}
return results;
}
private void initContentCellStyle() {
// 生成并设置另一个样式
style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
}
public void exportToExcel(OutputStream fileOut) throws IOException {
HSSFWorkbook wb;
try {
wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("table-colums");
HSSFFont boldFont = wb.createFont();
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle boldStyle = wb.createCellStyle();
boldStyle.setFont(boldFont);
HSSFRow row = sheet.createRow(0);
HSSFCell cell0 = row.createCell(0);
cell0.setCellValue("字段名称");
cell0.setCellStyle(boldStyle);
HSSFCell cell1 = row.createCell(1);
cell1.setCellValue("列名");
cell1.setCellStyle(boldStyle);
HSSFCell cell2 = row.createCell(2);
cell2.setCellValue("字段别名");
cell2.setCellStyle(boldStyle);
HSSFCell cell3 = row.createCell(3);
cell3.setCellValue("字段类型");
cell3.setCellStyle(boldStyle);
HSSFCell cell4 = row.createCell(4);
cell4.setCellValue("默认值");
cell4.setCellStyle(boldStyle);
HSSFCell cell5 = row.createCell(5);
cell5.setCellValue("最大长度");
cell5.setCellStyle(boldStyle);
HSSFCell cell6 = row.createCell(6);
cell6.setCellValue("显示顺序");
cell6.setCellStyle(boldStyle);
String value = Struts2Utils.getParameter("subTableVals");
String[] arr = value.split("=");
String jsonString = arr[1];
if (jsonString != null && StringUtils.isNotEmpty(jsonString.toString())) {
getSheetContent(jsonString, sheet);
}
wb.write(fileOut);
} catch (IOException exception) {
}
}
private void initHeadCellStyle() {
style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
}
/**
* 创建头文件
*
* @param headNames 对象数组的描述
*/
private void createSheet(String sheetname, String[] headNames) {
try {
HSSFSheet sheet = wb.createSheet(sheetname);
HSSFRow row = sheet.createRow(0);
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
for (int i = 0; i < headNames.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(cellStyle);
HSSFRichTextString hts = new HSSFRichTextString(headNames[i]);
cell.setCellValue(hts);
}
} catch (Exception ex) {
System.out.print(ex);
}
}
/**
* runs a xls file where the user insert a row within a worksheet where two fields are set: CODE,
* NAME.
*
* @param document: Document to modify the name and code field.
*/
public void postProcessXLS(Object document) {
HSSFWorkbook book = (HSSFWorkbook) document;
HSSFSheet sheet = book.getSheetAt(0); // Se toma hoja del libro
HSSFRow row;
HSSFCellStyle cellStyle = book.createCellStyle();
HSSFFont font = book.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
row = sheet.createRow(0); // Se crea una fila dentro de la hoja
createCell(cellStyle, row, 0, "CODIGO"); // "100">#{rowX.column1}</p:column>
createCell(
cellStyle, row, 1,
"NOMBRE"); // "100">#{rowX.column23}</p:column>
countriesList = countriesFacade.findAll();
for (int i = 0; i < countriesList.size(); i++) {
row = sheet.createRow(i + 1);
createCell(row, 0, countriesList.get(i).getIdCountry().toString()); // CODIGO
createCell(row, 1, countriesList.get(i).getName()); // NOMBRE
}
}
/** 單元格的字体 workbook excel font --字体模型 */
public static HSSFFont getHSSFFont(HSSFWorkbook workbook, Font f) {
HSSFFont font = workbook.createFont();
// 字体名称
font.setFontName(f.getFontNm());
// 字号
font.setFontHeightInPoints(f.getFontSize());
// 字体颜色
font.setColor(f.getFontColor());
// 下划线
font.setUnderline(f.getUnderline());
// 上标下标
font.setTypeOffset(f.getOffset());
// 删除线
font.setStrikeout(f.getDelLine());
// 加粗
font.setBoldweight(f.getBold());
// 斜线
font.setItalic(f.getItalic());
// 字体高度
// font.setFontHeight(arg0);
return font;
}
private Map initStyles(HSSFWorkbook wb, short fontHeight) {
Map result = new HashMap();
HSSFCellStyle titleStyle = wb.createCellStyle();
HSSFCellStyle textStyle = wb.createCellStyle();
HSSFCellStyle boldStyle = wb.createCellStyle();
HSSFCellStyle numericStyle = wb.createCellStyle();
HSSFCellStyle numericStyleBold = wb.createCellStyle();
HSSFCellStyle moneyStyle = wb.createCellStyle();
HSSFCellStyle moneyStyleBold = wb.createCellStyle();
HSSFCellStyle percentStyle = wb.createCellStyle();
HSSFCellStyle percentStyleBold = wb.createCellStyle();
result.put("titleStyle", titleStyle);
result.put("textStyle", textStyle);
result.put("boldStyle", boldStyle);
result.put("numericStyle", numericStyle);
result.put("numericStyleBold", numericStyleBold);
result.put("moneyStyle", moneyStyle);
result.put("moneyStyleBold", moneyStyleBold);
result.put("percentStyle", percentStyle);
result.put("percentStyleBold", percentStyleBold);
HSSFDataFormat format = wb.createDataFormat();
// Global fonts
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setColor(HSSFColor.BLACK.index);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setFontHeightInPoints(fontHeight);
HSSFFont fontBold = wb.createFont();
fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontBold.setColor(HSSFColor.BLACK.index);
fontBold.setFontName(HSSFFont.FONT_ARIAL);
fontBold.setFontHeightInPoints(fontHeight);
// Money Style
moneyStyle.setFont(font);
moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyle.setDataFormat(format.getFormat(moneyFormat));
// Money Style Bold
moneyStyleBold.setFont(fontBold);
moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));
// Percent Style
percentStyle.setFont(font);
percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
percentStyle.setDataFormat(format.getFormat(percentFormat));
// Percent Style Bold
percentStyleBold.setFont(fontBold);
percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
percentStyleBold.setDataFormat(format.getFormat(percentFormat));
// Standard Numeric Style
numericStyle.setFont(font);
numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// Standard Numeric Style Bold
numericStyleBold.setFont(fontBold);
numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// Title Style
titleStyle.setFont(font);
titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Standard Text Style
textStyle.setFont(font);
textStyle.setWrapText(true);
// Standard Text Style
boldStyle.setFont(fontBold);
boldStyle.setWrapText(true);
return result;
}
/**
* 在服务端生成Excel文件
*
* @param sourcedata
* @param sheetName
* @param fileName
* @param path
* @param otherParams
*/
public void createExcel(
List sourcedata, String sheetName, String fileName, String path, String otherParams) {
String xmlData = "";
long rownum = 0;
int colnum = 0;
String xml = "";
String filterrow = "";
String rows = ""; // 总行数,客户端分页时使用
int gridType = 0; // 列表类型
int listKind = 0; // 列表性质
int isOperater = 0; // 是否有操作列
int isTotalRow = 0; // 是否有统计行
String tcolname = ""; // 统计列的列名
int isStatusCol = 0; // 是否有行状态列
String colVal;
String tmp1[] = null;
String tmp2[] = null;
String tmp3[] = null;
String tmp4[] = null;
String[] colName = null; // 列名
int[] isToalcol = null; // 对应的列是否进行合计
double[] colTotal = null; // 对应的列的合计值
List outXml = new ArrayList();
long srdrows = sourcedata.size();
System.out.println("导出数据到excel文件时查询的记录条数:" + (srdrows - 1));
if (srdrows < 2) {
return;
}
try {
/*分析客户端提交的综合参数信息
*如:gridType^3@@@listKind^101@@@isOperater^1@@@isTotalRow^1@@@goodsid^0,,goodsname^0,goodstype^0,goodsunit^0,,prodarea^0,,sutotal^1,,lastsudate^0
*/
if (otherParams != null) {
tmp1 = otherParams.split("@@@");
for (int m = 0; m < tmp1.length; m++) {
tmp2 = tmp1[m].split("\\^");
if (m < (tmp1.length - 1)) {
if (tmp2[0].equalsIgnoreCase("gridType")) {
if (tmp2[1] == null || tmp2[1] == "") {
gridType = 0;
System.out.println("不能没有列表类型!");
return;
} else {
gridType = Integer.valueOf(tmp2[1]).intValue();
}
} else if (tmp2[0].equalsIgnoreCase("listKind")) {
if (tmp2[1] == null || tmp2[1] == "") {
listKind = 0;
System.out.println("不能没有列表性质!");
return;
} else {
listKind = Integer.valueOf(tmp2[1]).intValue();
}
} else if (tmp2[0].equalsIgnoreCase("isOperater")) {
if (tmp2[1] == null || tmp2[1] == "") {
isOperater = 0;
} else {
isOperater = Integer.valueOf(tmp2[1]).intValue();
}
} else if (tmp2[0].equalsIgnoreCase("isTotalRow")) {
if (tmp2[1] == null || tmp2[1] == "") {
isTotalRow = 0;
} else {
isTotalRow = Integer.valueOf(tmp2[1]).intValue();
}
} else if (tmp2[0].equalsIgnoreCase("isStatusCol")) {
if (tmp2[1] == null || tmp2[1] == "") {
isStatusCol = 0;
} else {
isStatusCol = Integer.valueOf(tmp2[1]).intValue();
}
}
} else {
tmp3 = tmp1[m].split(",");
// 初始化数组
colName = new String[tmp3.length];
isToalcol = new int[tmp3.length];
colTotal = new double[tmp3.length];
for (int n = 0; n < tmp3.length; n++) {
tmp4 = tmp3[n].split("\\^");
colName[n] = tmp4[0];
if (tmp4[1] == null) {
System.out.println("判断列是否进行合计时出错了!");
return;
}
isToalcol[n] = Integer.valueOf(tmp4[1]).intValue();
}
}
}
}
tmp4 = null;
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建工作簿字体
HSSFFont font = workbook.createFont();
// 设置字体颜色为 ,粗体
font.setColor(HSSFFont.COLOR_NORMAL);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 新建指定名称的工作表
HSSFSheet sheet = workbook.createSheet();
// 设置工作表的名称,字符集
workbook.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16);
// 创建单元格格式
HSSFCellStyle cellstyle = workbook.createCellStyle();
// 定义全部单元格字体样式
cellstyle.setFont(font);
for (int i = 0; i < sourcedata.size(); i++) {
if (i == sourcedata.size() - 1) {
// 总数据行数,不参与数据的导出
Map map = (Map) sourcedata.get(i);
Set keys = map.keySet();
for (Iterator iter = keys.iterator(); iter.hasNext(); ) {
String key = (String) iter.next();
rows = map.get(key).toString();
}
continue;
}
Map map = (LinkedHashMap) sourcedata.get(i);
Set keys = map.keySet();
// 记录行号
rownum++;
colnum = 0;
// 在索引i的位置创建行
HSSFRow row = sheet.createRow((short) i);
// 增加各列的列值
for (Iterator iter = keys.iterator(); iter.hasNext(); ) {
String key = (String) iter.next();
tmp4 = key.split("\\.");
tcolname = tmp4[1];
// 进行指定列的列值的合计
if (isTotalRow == 1) {
for (int w = 0; w < colName.length; w++) {
if (tcolname.equalsIgnoreCase(colName[w]) && isToalcol[w] == 1) {
colTotal[w] = Double.valueOf(map.get(key).toString()).doubleValue() + colTotal[w];
}
}
}
// 列值
colVal = (String) map.get(key).toString();
// 去掉左右的空格
colVal = colVal.trim();
// 替换回车
colVal = colVal.replaceAll("[\\n\\r]*", "");
// 在索引0的位置创建单元格
HSSFCell cell = row.createCell((short) colnum);
/*
//创建单元格格式
HSSFCellStyle cellstyle = workbook.createCellStyle();
//定义全部单元格字体样式
cellstyle.setFont(font);
*/
// 应用单元格格式
cell.setCellStyle(cellstyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// 第一个单元格中的值
cell.setCellValue(colVal);
// 记录列数
colnum++;
}
}
// 将Excel文件存盘
FileOutputStream fileOut = new FileOutputStream(path + fileName);
workbook.write(fileOut);
fileOut.flush();
// 导出结束,关闭输出流
fileOut.close();
} catch (Exception ex) {
System.out.println("系统生成EXCEL发生异常" + ex.getMessage());
ex.printStackTrace();
}
}
public void exportJgjExcel(
String title,
List<String[]> header,
List<Object[]> dataset,
OutputStream out,
short[] width,
List<int[]> merge) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.WHITE.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < header.size(); i++) {
row = sheet.createRow(i);
for (int m = 0; m < header.get(i).length; m++) {
HSSFCell cell = row.createCell(m);
cell.setCellStyle(style);
String headValue = header.get(i)[m];
// HSSFRichTextString text = new HSSFRichTextString(header.get(i)[m]);
cell.setCellValue(headValue);
}
}
if (merge != null && merge.size() > 0) {
for (int m = 0; m < merge.size(); m++) {
sheet.addMergedRegion(
new CellRangeAddress(
merge.get(m)[0], merge.get(m)[1], merge.get(m)[2], merge.get(m)[3])); // 合并行
}
}
// sheet.addMergedRegion(new CellRangeAddress(0,0,9,10));
// sheet.addMergedRegion(new CellRangeAddress(0,0,11,12));
for (int j = 0; j < dataset.size(); j++) {
row = sheet.createRow(j + header.size());
for (short i = 0; i < dataset.get(j).length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
HSSFRichTextString richString =
new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", ""));
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLACK.index);
// richString.applyFont(font3);
cell.setCellValue(richString);
}
}
if (width != null) {
for (short i = 0; i < width.length; i++) {
sheet.setColumnWidth(i, width[i]);
}
}
try {
workbook.write(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void exportExcel(
String headline,
String title,
String[] headers,
List<Object[]> dataset,
OutputStream out,
short[] width) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.WHITE.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
if (row != null) {
HSSFCell cell = row.createCell(0);
HSSFFont f = workbook.createFont();
f.setColor(HSSFColor.DARK_BLUE.index);
f.setFontHeightInPoints((short) 24);
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
HSSFCellStyle s = workbook.createCellStyle();
s.setAlignment(HSSFCellStyle.ALIGN_CENTER);
s.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
s.setFont(f);
cell.setCellStyle(s);
cell.setCellValue(headline);
}
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headers.length - 1)));
row = sheet.createRow(1);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
for (int j = 0; j < dataset.size(); j++) {
row = sheet.createRow(j + 2);
for (short i = 0; i < dataset.get(j).length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
HSSFRichTextString richString =
new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", ""));
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLACK.index);
// richString.applyFont(font3);
cell.setCellValue(richString);
}
}
if (width != null) {
for (short i = 0; i < width.length; i++) {
sheet.setColumnWidth(i, width[i]);
}
}
try {
workbook.write(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@SuppressWarnings("deprecation")
public Reporteconcepto(
Rgenerador objeto,
Rgenerador objeto2,
Boolean siinicial,
String partida,
String ruta,
String ruta2,
String path)
throws IOException {
this.FILE = path;
// creacion del libro que contedra nuestro reporte
libro = new HSSFWorkbook();
// cracion de la hoja que estara contenida en nuestro libro
hoja = libro.createSheet("new sheet");
// Definicion de estilo que contendra nuestro encabezado
// *****************************************************************************************************************************************************
// definicion estilos de celdas, establecimineto del tipo de fuente
fuenteen = libro.createFont();
fuenteen.setFontHeightInPoints((short) 12);
fuenteen.setFontName(fuenteen.FONT_ARIAL);
fuenteen.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Creacion del objeto que se encargara de aplicar el estilo a la celda
esceldaen = libro.createCellStyle();
esceldaen.setWrapText(true);
esceldaen.setAlignment(HSSFCellStyle.ALIGN_LEFT);
esceldaen.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
esceldaen.setFont(fuenteen);
// establecimiento de sombreado de nuestra celda
esceldaen.setFillForegroundColor((short) 44);
esceldaen.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// Definicion de estilo que contendra los tiulos
// *****************************************************************************************************************************************************
// definicion estilos de celdas, establecimineto del tipo de fuente
fuentet = libro.createFont();
fuentet.setFontHeightInPoints((short) 11);
fuentet.setFontName(fuentet.FONT_ARIAL);
fuentet.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Creacion del objeto que se encargara de aplicar el estilo a la celda
esceldat = libro.createCellStyle();
esceldat.setWrapText(true);
esceldat.setAlignment(HSSFCellStyle.ALIGN_CENTER);
esceldat.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
esceldat.setFont(fuentet);
// establecimiento de bordes
esceldat.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setBottomBorderColor((short) 8);
esceldat.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setLeftBorderColor((short) 8);
esceldat.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setRightBorderColor((short) 8);
esceldat.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setRightBorderColor((short) 8);
// establecimiento de sombreado de nuestra celda
esceldat.setFillForegroundColor((short) 22);
esceldat.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// *****************************************************************************************************************************************************
// Definicion del estilo de la celda de nuestros datos que contendra el
// reporte
// definicion estilos de celdas, establecimineto del tipo de fuente
fuentein = libro.createFont();
fuentein.setFontHeightInPoints((short) 10);
fuentein.setFontName(fuentein.FONT_ARIAL);
fuentein.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Creacion del objeto que se encargara de aplicar el estilo a la celda
esceldain = libro.createCellStyle();
esceldain.setWrapText(true);
esceldain.setAlignment(HSSFCellStyle.ALIGN_CENTER);
esceldain.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
esceldain.setFont(fuentet);
// establecimiento de bordes
esceldain.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setBottomBorderColor((short) 8);
esceldain.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setLeftBorderColor((short) 8);
esceldain.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setRightBorderColor((short) 8);
esceldain.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setRightBorderColor((short) 8);
// definimos el numero de filas que contedra nuestro decumento.
encabezado = hoja.createRow((short) 5);
Cencabezado = encabezado.createCell((short) 2);
Cencabezado.setCellValue("Datos Verificados: Partida(" + partida + ")");
hoja.addMergedRegion(new Region(5, (short) 2, 6, (short) 4));
Cencabezado.setCellStyle(esceldaen);
// ********************************************************************************************************************
HSSFRow fila1 = hoja.createRow((short) 2);
HSSFCell ccontrato = fila1.createCell((short) 8);
ccontrato.setCellValue("contrato:");
ccontrato.setCellStyle(esceldain);
HSSFCell rcontrato = fila1.createCell((short) 9);
rcontrato.setCellValue(" ");
rcontrato.setCellStyle(esceldain);
HSSFCell cgenrencia = fila1.createCell((short) 10);
cgenrencia.setCellValue("Gerencia:");
cgenrencia.setCellStyle(esceldain);
HSSFCell rgerencia = fila1.createCell((short) 11);
rgerencia.setCellValue(" ");
rgerencia.setCellStyle(esceldain);
HSSFCell choja = fila1.createCell((short) 12);
choja.setCellValue("Hoja:");
choja.setCellStyle(esceldain);
HSSFCell rhoja = fila1.createCell((short) 13);
rhoja.setCellValue(" ");
rhoja.setCellStyle(esceldain);
// *******************************************************************************************************************************************************
HSSFRow fila2 = hoja.createRow((short) 3);
HSSFCell cnc = fila2.createCell((short) 8);
cnc.setCellValue("N.C. :");
cnc.setCellStyle(esceldain);
HSSFCell rcnc = fila2.createCell((short) 9);
rcnc.setCellValue(" ");
rcnc.setCellStyle(esceldain);
HSSFCell ctipo = fila2.createCell((short) 10);
ctipo.setCellValue("Tipo de obra:");
ctipo.setCellStyle(esceldain);
HSSFCell rtipo = fila2.createCell((short) 11);
rtipo.setCellValue(" ");
rtipo.setCellStyle(esceldain);
HSSFCell cunidad = fila2.createCell((short) 12);
cunidad.setCellValue("Unidad:");
cunidad.setCellStyle(esceldain);
HSSFCell runidad = fila2.createCell((short) 13);
runidad.setCellValue(" ");
runidad.setCellStyle(esceldain);
// *******************************************************************************************************************************************************
HSSFRow fila3 = hoja.createRow((short) 4);
HSSFCell clocalidad = fila3.createCell((short) 8);
clocalidad.setCellValue("localidad:");
clocalidad.setCellStyle(esceldain);
HSSFCell rlocalidad = fila3.createCell((short) 9);
rlocalidad.setCellValue(" ");
rlocalidad.setCellStyle(esceldain);
hoja.addMergedRegion(new Region(4, (short) 9, 4, (short) 11));
rlocalidad.setCellStyle(esceldain);
HSSFCell runo = fila3.createCell((short) 10);
runo.setCellValue(" ");
runo.setCellStyle(esceldain);
HSSFCell rdos = fila3.createCell((short) 11);
rdos.setCellValue(" ");
rdos.setCellStyle(esceldain);
HSSFCell cfecha = fila3.createCell((short) 12);
cfecha.setCellValue("Fecha:");
cfecha.setCellStyle(esceldain);
HSSFCell rfecha = fila3.createCell((short) 13);
rfecha.setCellValue(" ");
rfecha.setCellStyle(esceldain);
// ***************************************************************************************************************************************************************
HSSFRow fila4 = hoja.createRow((short) 5);
HSSFCell ccontratista = fila4.createCell((short) 8);
ccontratista.setCellValue("Contratista:");
ccontratista.setCellStyle(esceldain);
HSSFCell rcontratista = fila4.createCell((short) 9);
rcontratista.setCellValue(" ");
rcontratista.setCellStyle(esceldain);
hoja.addMergedRegion(new Region(5, (short) 9, 5, (short) 13));
rcontratista.setCellStyle(esceldain);
HSSFCell runoc = fila4.createCell((short) 10);
runoc.setCellValue(" ");
runoc.setCellStyle(esceldain);
HSSFCell rdosc = fila4.createCell((short) 11);
rdosc.setCellValue(" ");
rdosc.setCellStyle(esceldain);
HSSFCell rtres = fila4.createCell((short) 12);
rtres.setCellValue(" ");
rtres.setCellStyle(esceldain);
HSSFCell rcuatro = fila4.createCell((short) 13);
rcuatro.setCellValue(" ");
rcuatro.setCellStyle(esceldain);
// ***************************************************************************************************************************************************************
HSSFRow fila5 = hoja.createRow((short) 6);
HSSFCell cperiodo = fila5.createCell((short) 8);
cperiodo.setCellValue("Consultor:");
cperiodo.setCellStyle(esceldain);
HSSFCell rperiodo = fila5.createCell((short) 9);
rperiodo.setCellValue(" ");
rperiodo.setCellStyle(esceldain);
hoja.addMergedRegion(new Region(6, (short) 9, 6, (short) 13));
rperiodo.setCellStyle(esceldain);
HSSFCell runop = fila5.createCell((short) 10);
runop.setCellValue(" ");
runop.setCellStyle(esceldain);
HSSFCell rdosp = fila5.createCell((short) 11);
rdosp.setCellValue(" ");
rdosp.setCellStyle(esceldain);
HSSFCell rtresp = fila5.createCell((short) 12);
rtresp.setCellValue(" ");
rtresp.setCellStyle(esceldain);
HSSFCell rcuatrop = fila5.createCell((short) 13);
rcuatrop.setCellValue(" ");
rcuatrop.setCellStyle(esceldain);
// crear un una columna
HSSFRow row1 = hoja.createRow((short) 7);
// create de las celdas
HSSFCell cc = row1.createCell((short) 2);
HSSFCell cd = row1.createCell((short) 3);
HSSFCell cu = row1.createCell((short) 4);
HSSFCell cx = row1.createCell((short) 5);
HSSFCell cy = row1.createCell((short) 6);
HSSFCell cz = row1.createCell((short) 7);
HSSFCell ca = row1.createCell((short) 8);
HSSFCell cl = row1.createCell((short) 9);
HSSFCell cal = row1.createCell((short) 10);
HSSFCell cca = row1.createCell((short) 11);
HSSFCell cpz = row1.createCell((short) 12);
HSSFCell cim = row1.createCell((short) 13);
// writing data to the cells
cc.setCellValue("Clave");
cc.setCellStyle(esceldat);
cd.setCellValue("Descripción");
cd.setCellStyle(esceldat);
cu.setCellValue("Unidad");
cu.setCellStyle(esceldat);
cx.setCellValue("X");
cx.setCellStyle(esceldat);
cy.setCellValue("Y");
cy.setCellStyle(esceldat);
cz.setCellValue("Z");
cz.setCellStyle(esceldat);
ca.setCellValue("Alto");
ca.setCellStyle(esceldat);
cl.setCellValue("Largo");
cl.setCellStyle(esceldat);
cal.setCellValue("ancho");
cal.setCellStyle(esceldat);
cca.setCellValue("Cantidad");
cca.setCellStyle(esceldat);
cpz.setCellValue("Piezas");
cpz.setCellStyle(esceldat);
cim.setCellValue("Importe");
cim.setCellStyle(esceldat);
// crear un una columna
HSSFRow row = hoja.createRow((short) 8);
// create de las celdas
HSSFCell cc1 = row.createCell((short) 2);
HSSFCell cd1 = row.createCell((short) 3);
HSSFCell cu1 = row.createCell((short) 4);
HSSFCell cx1 = row.createCell((short) 5);
HSSFCell cy1 = row.createCell((short) 6);
HSSFCell cz1 = row.createCell((short) 7);
HSSFCell ca1 = row.createCell((short) 8);
HSSFCell cl1 = row.createCell((short) 9);
HSSFCell cal1 = row.createCell((short) 10);
HSSFCell cca1 = row.createCell((short) 11);
HSSFCell cpz1 = row.createCell((short) 12);
HSSFCell cim1 = row.createCell((short) 13);
cc1.setCellValue(objeto.getClave());
cc1.setCellStyle(esceldain);
cd1.setCellValue(objeto.getDescripcion());
cd1.setCellStyle(esceldain);
cu1.setCellValue(objeto.getUnidad());
cu1.setCellStyle(esceldain);
cx1.setCellValue(objeto.getX());
cx1.setCellStyle(esceldain);
cy1.setCellValue(objeto.getY());
cy1.setCellStyle(esceldain);
cz1.setCellValue(objeto.getZ());
cz1.setCellStyle(esceldain);
ca1.setCellValue(objeto.getAlto());
ca1.setCellStyle(esceldain);
cl1.setCellValue(objeto.getLargo());
cl1.setCellStyle(esceldain);
cal1.setCellValue(objeto.getAncho());
cal1.setCellStyle(esceldain);
cca1.setCellValue(objeto.getCantidad());
cca1.setCellStyle(esceldain);
cpz1.setCellValue(objeto.getPiezas());
cpz1.setCellStyle(esceldain);
cim1.setCellValue(objeto.getImporte());
cim1.setCellStyle(esceldain);
HSSFSheet sheet = libro.getSheetAt(0);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// contendor que contiene las imagenes
HSSFClientAnchor anchor;
if (ruta != null) {
File fis = new File(ruta);
anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 11, (short) 5, 24);
anchor.setAnchorType(2);
HSSFPicture imagen = patriarch.createPicture(anchor, Cargarimagen(fis, libro));
}
if (ruta2 != null) {
File fis2 = new File(ruta2);
anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 9, 11, (short) 12, 24);
anchor.setAnchorType(2);
HSSFPicture imagen2 = patriarch.createPicture(anchor, Cargarimagen(fis2, libro));
}
sheet.autoSizeColumn((short) 2);
sheet.autoSizeColumn((short) 3);
sheet.autoSizeColumn((short) 4);
sheet.autoSizeColumn((short) 5);
sheet.autoSizeColumn((short) 6);
sheet.autoSizeColumn((short) 7);
sheet.autoSizeColumn((short) 8);
sheet.autoSizeColumn((short) 9);
sheet.autoSizeColumn((short) 10);
sheet.autoSizeColumn((short) 11);
sheet.autoSizeColumn((short) 12);
sheet.autoSizeColumn((short) 13);
if (siinicial == true) {
// ************************************************************************************
HSSFRow encabezado2 = hoja.createRow((short) 26);
HSSFCell Cencabezado2 = encabezado2.createCell((short) 2);
Cencabezado2.setCellValue("Datos: Partida(" + partida + ")");
hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4));
Cencabezado2.setCellStyle(esceldaen);
// crear un una columna
HSSFRow row27 = hoja.createRow((short) 28);
// create de las celdas
HSSFCell cc2 = row27.createCell((short) 2);
HSSFCell cd2 = row27.createCell((short) 3);
HSSFCell cu2 = row27.createCell((short) 4);
HSSFCell cx2 = row27.createCell((short) 5);
HSSFCell cy2 = row27.createCell((short) 6);
HSSFCell cz2 = row27.createCell((short) 7);
HSSFCell ca2 = row27.createCell((short) 8);
HSSFCell cl2 = row27.createCell((short) 9);
HSSFCell cal2 = row27.createCell((short) 10);
HSSFCell cca2 = row27.createCell((short) 11);
HSSFCell cpz2 = row27.createCell((short) 12);
HSSFCell cim2 = row27.createCell((short) 13);
// writing data to the cells
cc2.setCellValue("Clave");
cc2.setCellStyle(esceldat);
cd2.setCellValue("Descripción");
cd2.setCellStyle(esceldat);
cu2.setCellValue("Unidad");
cu2.setCellStyle(esceldat);
cx2.setCellValue("X");
cx2.setCellStyle(esceldat);
cy2.setCellValue("Y");
cy2.setCellStyle(esceldat);
cz2.setCellValue("Z");
cz2.setCellStyle(esceldat);
ca2.setCellValue("Alto");
ca2.setCellStyle(esceldat);
cl2.setCellValue("Largo");
cl2.setCellStyle(esceldat);
cal2.setCellValue("ancho");
cal2.setCellStyle(esceldat);
cca2.setCellValue("Cantidad");
cca2.setCellStyle(esceldat);
cpz2.setCellValue("Piezas");
cpz2.setCellStyle(esceldat);
cim2.setCellValue("Importe");
cim2.setCellStyle(esceldat);
// crear un una columna
HSSFRow row28 = hoja.createRow((short) 29);
// create de las celdas
HSSFCell cc12 = row28.createCell((short) 2);
HSSFCell cd12 = row28.createCell((short) 3);
HSSFCell cu12 = row28.createCell((short) 4);
HSSFCell cx12 = row28.createCell((short) 5);
HSSFCell cy12 = row28.createCell((short) 6);
HSSFCell cz12 = row28.createCell((short) 7);
HSSFCell ca12 = row28.createCell((short) 8);
HSSFCell cl12 = row28.createCell((short) 9);
HSSFCell cal12 = row28.createCell((short) 10);
HSSFCell cca12 = row28.createCell((short) 11);
HSSFCell cpz12 = row28.createCell((short) 12);
HSSFCell cim12 = row28.createCell((short) 13);
cc12.setCellValue(objeto2.getClave());
cc12.setCellStyle(esceldain);
cd12.setCellValue(objeto2.getDescripcion());
cd12.setCellStyle(esceldain);
cu12.setCellValue(objeto2.getUnidad());
cu12.setCellStyle(esceldain);
cx12.setCellValue(objeto2.getX());
cx12.setCellStyle(esceldain);
cy12.setCellValue(objeto2.getY());
cy12.setCellStyle(esceldain);
cz12.setCellValue(objeto2.getZ());
cz12.setCellStyle(esceldain);
ca12.setCellValue(objeto2.getAlto());
ca12.setCellStyle(esceldain);
cl12.setCellValue(objeto2.getLargo());
cl12.setCellStyle(esceldain);
cal12.setCellValue(objeto2.getAncho());
cal12.setCellStyle(esceldain);
cca12.setCellValue(objeto2.getCantidad());
cca12.setCellStyle(esceldain);
cpz12.setCellValue(objeto2.getPiezas());
cpz12.setCellStyle(esceldain);
cim12.setCellValue(objeto2.getImporte());
cim12.setCellStyle(esceldain);
float uno = 0, dos = 0, resultado = 0;
uno = Float.parseFloat(objeto.getImporte());
dos = Float.parseFloat(objeto2.getImporte());
if (uno > dos) {
HSSFRow row31 = hoja.createRow((short) 33);
// create de las celdas
HSSFCell cc131 = row31.createCell((short) 3);
resultado = uno - dos;
cc131.setCellValue("Execedente: " + String.valueOf(resultado));
cc131.setCellStyle(esceldain);
} else {
if (dos > uno) {
HSSFRow row31 = hoja.createRow((short) 33);
// create de las celdas
HSSFCell cc131 = row31.createCell((short) 3);
resultado = dos - uno;
cc131.setCellValue("Restante: " + String.valueOf(resultado));
cc131.setCellStyle(esceldain);
}
}
// ****************************************************************************************
} else {
HSSFRow encabezado2 = hoja.createRow((short) 26);
HSSFCell Cencabezado2 = encabezado2.createCell((short) 2);
Cencabezado2.setCellValue(
"El aspecto:"
+ objeto.getDescripcion()
+ " no esta comtemplado en la estimacion inicial");
hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4));
Cencabezado2.setCellStyle(esceldaen);
HSSFRow row28 = hoja.createRow((short) 29);
// create de las celdas
HSSFCell cc12 = row28.createCell((short) 3);
cc12.setCellValue("Execedente: " + objeto.getImporte());
cc12.setCellStyle(esceldain);
}
try {
FileOutputStream elFichero = new FileOutputStream(FILE);
libro.write(elFichero);
elFichero.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack)
throws RuntimeException, IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
// create a sheet with specified name
HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName());
for (int i = 0; i < xlscaCallBack.getColumnWidth().length; i++) {
sheet.setColumnWidth(i, xlscaCallBack.getColumnWidth()[i] * 256);
}
// create a title for sheet title
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, xlscaCallBack.getHeaders().length - 1));
HSSFCellStyle titleCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell
titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont titleFont = workbook.createFont(); // set font
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleFont.setFontName("宋体");
titleFont.setFontHeight((short) (240));
titleFont.setColor(HSSFColor.AUTOMATIC.index);
titleCellStyle.setFont(titleFont);
titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
HSSFRow title = sheet.createRow(0);
title.setHeight((short) 300);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title
HSSFCell cell = title.createCell(i);
cell.setCellValue(xlscaCallBack.getTitle());
cell.setCellStyle(titleCellStyle);
}
HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell
headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont headerFont = workbook.createFont(); // set font
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontName("幼圆");
headerFont.setColor(HSSFColor.AUTOMATIC.index);
headerCellStyle.setFont(headerFont);
headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
HSSFRow header = sheet.createRow(1);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for header
HSSFCell cell = header.createCell(i);
cell.setCellValue(xlscaCallBack.getHeaders()[i]);
cell.setCellStyle(headerCellStyle);
}
HSSFCellStyle rowCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell
rowCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont rowFont = workbook.createFont(); // set font
rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
rowFont.setFontName("幼圆");
rowFont.setColor(HSSFColor.AUTOMATIC.index);
rowCellStyle.setFont(rowFont);
rowCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
rowCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
rowCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
rowCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
for (int i = 0; i < lists.size(); i++) {
HSSFRow row = sheet.createRow(i + 2);
String values[] = xlscaCallBack.getValue(lists.get(i));
for (int j = 0; j < values.length; j++) {
HSSFCell cell = row.createCell(j);
cell.setCellValue(values[j]);
cell.setCellStyle(rowCellStyle);
}
}
HSSFCellStyle bottomCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell
bottomCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFont bottomFont = workbook.createFont(); // set font
bottomFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
bottomFont.setFontName("幼圆");
bottomFont.setColor(HSSFColor.AUTOMATIC.index);
bottomCellStyle.setFont(bottomFont);
bottomCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
bottomCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
sheet.addMergedRegion(
new CellRangeAddress(
lists.size() + 2,
lists.size() + 2,
0,
xlscaCallBack.getHeaders().length - 1)); // creaet bootom for xls
HSSFRow bottomRow = sheet.createRow(lists.size() + 2);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title
HSSFCell cell = bottomRow.createCell(i);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
cell.setCellValue("共计导出 " + lists.size() + " 条记录 ,导出日期:" + sf.format(new Date()));
cell.setCellStyle(bottomCellStyle);
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("create Excel failed due to some unkonw reasion~");
}
}
public static boolean writeArray2ExcelFile1(
String FileName, String[] titleNames, List<String[]> arry) throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("Data");
int columnNum = titleNames.length;
int rowNum = arry.size();
// for (int i = 0; i < columnNum; i++) {
// sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20)));
// }
HSSFCellStyle headStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
HSSFFont headFont = wb.createFont();
headFont.setFontName("Courier New");
headFont.setFontHeightInPoints((short) 10);
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setColor(HSSFColor.BLACK.index);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
headStyle.setFillForegroundColor(HSSFColor.BLACK.index);
headStyle.setLocked(true);
headStyle.setFont(headFont);
headStyle.setBorderTop((short) 2);
headStyle.setBorderBottom((short) 1);
HSSFCellStyle contentStyle = wb.createCellStyle();
// apply custom headFont to the text in the comment
HSSFFont contentFont = wb.createFont();
contentFont.setFontName("Courier New");
contentFont.setFontHeightInPoints((short) 9);
// headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
contentFont.setColor(HSSFColor.BLACK.index);
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
contentStyle.setFillForegroundColor(HSSFColor.BLACK.index);
contentStyle.setFont(contentFont);
// create titile row
HSSFRow row = sheet1.createRow(0);
int heandLine = 0;
HSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
heandLine++;
}
for (int i = 0; i < rowNum; i++) {
row = sheet1.createRow((i + heandLine));
String[] line = (String[]) arry.get(i);
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?hssf.?usermodel.?HSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue(".");
}
}
}
// Write the output to a inFile
FileOutputStream fileOut = new FileOutputStream(FileName);
wb.write(fileOut);
fileOut.close();
return true;
}
/**
* 读取xml定义的模板，创建excel，感觉没啥用
*
* @author David
* @param args
*/
public static void main(String[] args) {
// 获取解析xml文件路径
String path = System.getProperty("user.dir") + "/bin/student2.xml";
File file = new File(path);
SAXBuilder builder = new SAXBuilder();
try {
// 解析xml文件
Document parse = builder.build(file);
// 创建Excel
HSSFWorkbook wb = new HSSFWorkbook();
// 创建sheet
HSSFSheet sheet = wb.createSheet("Sheet0");
// 获取xml文件跟节点
Element root = parse.getRootElement();
// 获取模板名称
String templateName = root.getAttribute("name").getValue();
int rownum = 0;
int column = 0;
// 设置列宽
Element colgroup = root.getChild("colgroup");
setColumnWidth(sheet, colgroup);
// 设置标题
Element title = root.getChild("title");
List<Element> trs = title.getChildren("tr");
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
List<Element> tds = tr.getChildren("td");
HSSFRow row = sheet.createRow(rownum);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (column = 0; column < tds.size(); column++) {
Element td = tds.get(column);
HSSFCell cell = row.createCell(column);
Attribute rowSpan = td.getAttribute("rowspan");
Attribute colSpan = td.getAttribute("colspan");
Attribute value = td.getAttribute("value");
if (value != null) {
String val = value.getValue();
cell.setCellValue(val);
int rspan = rowSpan.getIntValue() - 1;
int cspan = colSpan.getIntValue() - 1;
// 设置字体
HSSFFont font = wb.createFont();
font.setFontName("仿宋_GB2312");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
// font.setFontHeight((short)12);
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
// 合并单元格居中
sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
}
}
rownum++;
}
// 设置表头
Element thead = root.getChild("thead");
trs = thead.getChildren("tr");
for (int i = 0; i < trs.size(); i++) {
Element tr = trs.get(i);
HSSFRow row = sheet.createRow(rownum);
List<Element> ths = tr.getChildren("th");
for (column = 0; column < ths.size(); column++) {
Element th = ths.get(column);
Attribute valueAttr = th.getAttribute("value");
HSSFCell cell = row.createCell(column);
if (valueAttr != null) {
String value = valueAttr.getValue();
cell.setCellValue(value);
}
}
rownum++;
}
// 设置数据区域样式
Element tbody = root.getChild("tbody");
Element tr = tbody.getChild("tr");
int repeat = tr.getAttribute("repeat").getIntValue();
List<Element> tds = tr.getChildren("td");
for (int i = 0; i < repeat; i++) {
HSSFRow row = sheet.createRow(rownum);
for (column = 0; column < tds.size(); column++) {
Element td = tds.get(column);
HSSFCell cell = row.createCell(column);
setType(wb, cell, td);
}
rownum++;
}
// 生成Excel导入模板
File tempFile = new File("e:/" + templateName + ".xls");
tempFile.delete();
tempFile.createNewFile();
FileOutputStream stream = FileUtils.openOutputStream(tempFile);
wb.write(stream);
stream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static boolean writeArray2ExcelSheet1(
HSSFSheet sheet1, HSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception {
int rowNum = arry.size();
if (rowNum == 0) {
System.err.println("No input data!");
return false;
}
String[] titleNames = null;
if (hasHead) {
titleNames = (String[]) arry.get(0);
}
int columnNum = ((String[]) arry.get(0)).length;
for (int i = 0; i < columnNum; i++) {
sheet1.setColumnWidth(i, (int) ((30 * 8) / ((double) 1 / 20)));
}
HSSFFont font = wb.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
HSSFCellStyle headStyle = wb.createCellStyle();
headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headStyle.setLocked(true);
headStyle.setFont(font);
HSSFCellStyle bodyStyle = wb.createCellStyle();
bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle markedBodyStyle = wb.createCellStyle();
markedBodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
markedBodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
markedBodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
markedBodyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
int rowIndex = 0;
// create titile row
HSSFRow row = sheet1.createRow(rowIndex);
HSSFCell cell = null;
if (titleNames != null) {
for (int i = 0; i < columnNum; i++) {
cell = row.createCell(i);
cell.setCellValue(titleNames[i]);
cell.setCellStyle(headStyle);
}
rowIndex++;
}
for (int i = rowIndex; i < rowNum; i++) {
row = sheet1.createRow(i);
String[] line = (String[]) arry.get(i);
columnNum = line.length;
for (int j = 0; j < columnNum; j++) {
cell = row.createCell(j);
if (line[0] != null) {
cell.setCellStyle(markedBodyStyle);
} else {
cell.setCellStyle(bodyStyle);
}
if (line[j] != null) {
if (Util.isNumeric(line[j])) {
// org.?apache.?poi.?hssf.?usermodel.?HSSFCell.CELL_TYPE_NUMERIC
cell.setCellType(0);
cell.setCellValue(Double.parseDouble(line[j]));
} else {
cell.setCellValue(line[j]);
}
} else {
cell.setCellValue("");
}
}
}
return true;
}