菜单

POI设置Excel的格式

2018年11月15日 - jQuery

图片 1

开拓a.xls发现结果未是自眷恋如果的,第一推行之可观都没有,没有报错说明代码有题目,为什么回没有惊人也?是未是单位不等同吗?我将row.setHeight((short)
25);改化了row.setHeight((short)
250);结果发现第一执出来了,但是就是怎一个换算关系也?我查了一下导出的Excel第一实行胜是16如从,换算一下汲取row.setHeight((short)
15.625);表示行高为一个像素,那么想要成几独如从即吓做了。比如
row.setHeight((short) (15.625*n));//n为履行胜的例如素数。
实在在API中还有一个HSSFRow
对象还有一个安装实行愈的函数setHeightInPoints(float
height);这个函数中参数就是推行胜的例如素数,比setHeight函数要方便多矣。
行高设置完了,接下设置列宽

package test;

import java.util.HashMap;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Transform {  

    private int lastColumn = 0;  
    private HashMap<Integer, HSSFCellStyle> styleMap = new HashMap();  

    public void transformXSSF(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew) {   
        HSSFSheet sheetNew;  
        XSSFSheet sheetOld;  

        workbookNew.setMissingCellPolicy(workbookOld.getMissingCellPolicy());  

        for (int i = 0; i < workbookOld.getNumberOfSheets(); i++) {  
            sheetOld = workbookOld.getSheetAt(i);  
            sheetNew = workbookNew.getSheet(sheetOld.getSheetName());  
            sheetNew = workbookNew.createSheet(sheetOld.getSheetName());  
            this.transform(workbookOld, workbookNew, sheetOld, sheetNew);  
        }    
    }  

    private void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            XSSFSheet sheetOld, HSSFSheet sheetNew) {  

        sheetNew.setDisplayFormulas(sheetOld.isDisplayFormulas());  
        sheetNew.setDisplayGridlines(sheetOld.isDisplayGridlines());  
        sheetNew.setDisplayGuts(sheetOld.getDisplayGuts());  
        sheetNew.setDisplayRowColHeadings(sheetOld.isDisplayRowColHeadings());  
        sheetNew.setDisplayZeros(sheetOld.isDisplayZeros());  
        sheetNew.setFitToPage(sheetOld.getFitToPage());  

        sheetNew.setHorizontallyCenter(sheetOld.getHorizontallyCenter());  
        sheetNew.setMargin(Sheet.BottomMargin,  
                sheetOld.getMargin(Sheet.BottomMargin));  
        sheetNew.setMargin(Sheet.FooterMargin,  
                sheetOld.getMargin(Sheet.FooterMargin));  
        sheetNew.setMargin(Sheet.HeaderMargin,  
                sheetOld.getMargin(Sheet.HeaderMargin));  
        sheetNew.setMargin(Sheet.LeftMargin,  
                sheetOld.getMargin(Sheet.LeftMargin));  
        sheetNew.setMargin(Sheet.RightMargin,  
                sheetOld.getMargin(Sheet.RightMargin));  
        sheetNew.setMargin(Sheet.TopMargin, sheetOld.getMargin(Sheet.TopMargin));  
        sheetNew.setPrintGridlines(sheetNew.isPrintGridlines());  
        sheetNew.setRightToLeft(sheetNew.isRightToLeft());  
        sheetNew.setRowSumsBelow(sheetNew.getRowSumsBelow());  
        sheetNew.setRowSumsRight(sheetNew.getRowSumsRight());  
        sheetNew.setVerticallyCenter(sheetOld.getVerticallyCenter());  

        HSSFRow rowNew;  
        for (Row row : sheetOld) {  
            rowNew = sheetNew.createRow(row.getRowNum());  
            if (rowNew != null)  
                this.transform(workbookOld, workbookNew, (XSSFRow) row, rowNew);  
        }  

        for (int i = 0; i < this.lastColumn; i++) {  
            sheetNew.setColumnWidth(i, sheetOld.getColumnWidth(i));  
            sheetNew.setColumnHidden(i, sheetOld.isColumnHidden(i));  
        }  

        for (int i = 0; i < sheetOld.getNumMergedRegions(); i++) {  
            CellRangeAddress merged = sheetOld.getMergedRegion(i);  
            sheetNew.addMergedRegion(merged);  
        }  
    }  

    private void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            XSSFRow rowOld, HSSFRow rowNew) {  
        HSSFCell cellNew;  
        rowNew.setHeight(rowOld.getHeight());  

        for (Cell cell : rowOld) {  
            cellNew = rowNew.createCell(cell.getColumnIndex(),  
                    cell.getCellType());  
            if (cellNew != null)  
                this.transform(workbookOld, workbookNew, (XSSFCell) cell,  
                        cellNew);  
        }  
        this.lastColumn = Math.max(this.lastColumn, rowOld.getLastCellNum());  
    }  

    private void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            XSSFCell cellOld, HSSFCell cellNew) {  
        cellNew.setCellComment(cellOld.getCellComment());  

        Integer hash = cellOld.getCellStyle().hashCode();  
        if (this.styleMap != null && !this.styleMap.containsKey(hash)) {  
            this.transform(workbookOld, workbookNew, hash,  
                    cellOld.getCellStyle(),  
                    (HSSFCellStyle) workbookNew.createCellStyle());  
        }  
        cellNew.setCellStyle(this.styleMap.get(hash));  

        switch (cellOld.getCellType()) {  
        case Cell.CELL_TYPE_BLANK:  
            break;  
        case Cell.CELL_TYPE_BOOLEAN:  
            cellNew.setCellValue(cellOld.getBooleanCellValue());  
            break;  
        case Cell.CELL_TYPE_ERROR:  
            cellNew.setCellValue(cellOld.getErrorCellValue());  
            break;  
        case Cell.CELL_TYPE_FORMULA:  
            cellNew.setCellValue(cellOld.getCellFormula());  
            break;  
        case Cell.CELL_TYPE_NUMERIC:  
            cellNew.setCellValue(cellOld.getNumericCellValue());  
            break;  
        case Cell.CELL_TYPE_STRING:  
            cellNew.setCellValue(cellOld.getStringCellValue());  
            break;  
        default:  
            System.out.println("transform: Unbekannter Zellentyp "  
                    + cellOld.getCellType());  
        }  
    }  

    private void transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            Integer hash, XSSFCellStyle styleOld, HSSFCellStyle styleNew) {  
        styleNew.setAlignment(styleOld.getAlignment());  
        styleNew.setBorderBottom(styleOld.getBorderBottom());  
        styleNew.setBorderLeft(styleOld.getBorderLeft());  
        styleNew.setBorderRight(styleOld.getBorderRight());  
        styleNew.setBorderTop(styleOld.getBorderTop());  
        styleNew.setDataFormat(this.transform(workbookOld, workbookNew,  
                styleOld.getDataFormat()));  
        styleNew.setFillBackgroundColor(styleOld.getFillBackgroundColor());  
        styleNew.setFillForegroundColor(styleOld.getFillForegroundColor());  
        styleNew.setFillPattern(styleOld.getFillPattern());  
        styleNew.setFont(this.transform(workbookNew,  
                (XSSFFont) styleOld.getFont()));  
        styleNew.setHidden(styleOld.getHidden());  
        styleNew.setIndention(styleOld.getIndention());  
        styleNew.setLocked(styleOld.getLocked());  
        styleNew.setVerticalAlignment(styleOld.getVerticalAlignment());  
        styleNew.setWrapText(styleOld.getWrapText());  
        this.styleMap.put(hash, styleNew);  
    }  

    private short transform(XSSFWorkbook workbookOld, HSSFWorkbook workbookNew,  
            short index) {  
        DataFormat formatOld = workbookOld.createDataFormat();  
        DataFormat formatNew = workbookNew.createDataFormat();  
        return formatNew.getFormat(formatOld.getFormat(index));  
    }  

    private HSSFFont transform(HSSFWorkbook workbookNew, XSSFFont fontOld) {  
        HSSFFont fontNew = workbookNew.createFont();  
        fontNew.setBoldweight(fontOld.getBoldweight());  
        fontNew.setCharSet(fontOld.getCharSet());  
        fontNew.setColor(fontOld.getColor());  
        fontNew.setFontName(fontOld.getFontName());  
        fontNew.setFontHeight(fontOld.getFontHeight());  
        fontNew.setItalic(fontOld.getItalic());  
        fontNew.setStrikeout(fontOld.getStrikeout());  
        fontNew.setTypeOffset(fontOld.getTypeOffset());  
        fontNew.setUnderline(fontOld.getUnderline());  
        return fontNew;  
    }   
}  

图片 2图片 3    public static void main(String[] args) 图片 4{
图片 5图片 6        try 图片 7{
图片 8            HSSFWorkbook wb = new HSSFWorkbook();
图片 9            HSSFSheet sheet = wb.createSheet();
图片 10            HSSFRow row = sheet.createRow(0);
图片 11            row.setHeight((short) 250);
图片 12            sheet.setColumnWidth((short) 0, (short) 250);
图片 13            FileOutputStream fileOut = new FileOutputStream(“c:\\a.xls”);
图片 14            wb.write(fileOut);
图片 15            fileOut.close();
图片 16        }
图片 17图片 18        catch (Exception e) 图片 19{
图片 20            e.printStackTrace();
图片 21        }
图片 22    }

Demo结构及援的Jar包

       
前数天写了同样首用POI导出Excel遇到的一个新奇的题目,今天发出使用了POI但是这次未相符用模板,所有的布局都是用程序来完成的。所以来相逢了数意外的问题。其实就算有限只问题,设置行高和安列宽。
         首先自己查看了POI3.0的API发现HSSFRow对象来setHeight(short
height)方法,我就算形容了接触测试代码

源代码(TestDemo.java)

收受来说说sheet.setColumnWidth((short) 0, (short) 250);
率先独参数表示一旦为第几排设置,第二个参数表示列的肥瘦,看看上面的代码按说第一执行第一排的唯有元格形状应该是独正方形,因为宽和高都是250,但是打开导出后底Excel发现步长没有惊人充分,是独增长方形,查看该列的小幅只有为7个像素,看来行高和列宽的单位凡勿同等的,同样换一终sheet.setColumnWidth((short)
0, (short)
(35.7));表示高度也一个像素,同样设置列宽的像素为sheet.setColumnWidth((short)
0, (short) (35.7*n));//n为列高之如素数。
但时排宽自己还没找到其他的比是还简约的函数,如果发朋友于这个更简短的梦想大家多多交流。

 

 

网盘链接:https://pan.baidu.com/s/1I7ZH4gXrTMPR-\_zIjCpGCg 密码:z3gj

图片 23图片 24    public static void main(String[] args) 图片 25{
图片 26图片 27        try 图片 28{
图片 29            HSSFWorkbook wb = new HSSFWorkbook();
图片 30            HSSFSheet sheet = wb.createSheet();
图片 31            HSSFRow row = sheet.createRow(0);
图片 32            row.setHeight((short) 25);//目的凡想把行高设置成25px
图片 33            FileOutputStream fileOut = new FileOutputStream(“c:\\a.xls”);
图片 34            wb.write(fileOut);
图片 35            fileOut.close();
图片 36        }
图片 37图片 38        catch (Exception e) 图片 39{
图片 40            e.printStackTrace();
图片 41        }
图片 42    }

源代码(Transform.java) 将xlsx文件转换成xls文件。(可以拍卖统一单元格,边框等格式问题!!!)

POI中将Excel转换为HTML方法才能换HSSFWorkBook类型(即03版xls),故可以预先用读取的xlsx文件转换成xls文件再度调用该办法统一处理

 

package test;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.hssf.converter.ExcelToHtmlConverter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;

public class TestDemo {

 final static String path = "D:\\EclipseWorkspace\\ExcelToHtmlDemo\\ExcelToHtml\\";
 final static String file = "TestExcel.xlsx";
        private static final String EXCEL_XLS = "xls";  
        private static final String EXCEL_XLSX = "xlsx";  

 public static void main(String[] args)
 {
  try{
         InputStream input = new FileInputStream(path +"/"+ file);  
         HSSFWorkbook excelBook = new HSSFWorkbook();
         //判断Excel文件将07+版本转换为03版本
         if(file.endsWith(EXCEL_XLS)){  //Excel 2003  
          excelBook = new HSSFWorkbook(input);  
         }
         else if(file.endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
          Transform xls = new Transform();    
          XSSFWorkbook workbookOld = new XSSFWorkbook(input); 
                xls.transformXSSF(workbookOld, excelBook);
         }  

         ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());          
         //去掉Excel头行  
         excelToHtmlConverter.setOutputColumnHeaders(false);  
         //去掉Excel行号  
         excelToHtmlConverter.setOutputRowNumbers(false);  

         excelToHtmlConverter.processWorkbook(excelBook); 

         Document htmlDocument = excelToHtmlConverter.getDocument();  

         ByteArrayOutputStream outStream = new ByteArrayOutputStream();  
         DOMSource domSource = new DOMSource(htmlDocument);  
         StreamResult streamResult = new StreamResult(outStream);  
         TransformerFactory tf = TransformerFactory.newInstance();  
         Transformer serializer = tf.newTransformer();  

         serializer.setOutputProperty(OutputKeys.ENCODING, "gb2312");  
         serializer.setOutputProperty(OutputKeys.INDENT, "yes");  
         serializer.setOutputProperty(OutputKeys.METHOD, "html");  

         serializer.transform(domSource, streamResult);  
         outStream.close();  

         //Excel转换成Html
         String content = new String(outStream.toByteArray());  
                System.out.println(content);
  }
  catch(Exception e) {
   e.printStackTrace();   
  }
 }
}            

  

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图