Poi complex excel implementation, poi complex excel implementation

Poi complex excel implementation, poi complex excel implementation

I. Preface

Recently, I helped a friend export an excel file. It was a headache for me. I read the table style and it was a headache for me, I want to do my first job when I first got out of the society. The first job was to convert word into html. In this process, I tried too many ways, however, there is no guarantee that all the data can be converted successfully. complicated word conversion still causes confusion. I have not exported the excel file yet. I designed to merge the cells. I went to the official documents and did not see any names. Then I searched for it online, next, let's record the pitfall we step on. In fact, it didn't take long for us to start from in the evening and finish it in three hours. But we did a lot of experiments between them.

Ii. Content

Let's talk about the online search results. I searched "hssfrow merge cells" and found the combination of the two articles.

(1): http://dacoolbaby.iteye.com/blog/1630957

(2): http://blog.csdn.net/hehexiaoyou/article/details/37873131

First, let's talk about my test. I got the code in (1) to run locally, but always reported an error, mainly because

　　

// Merge B2 and B3 sheet. addMergedRegion (new Region (1, // first row (0-based) (short) 1, // first column (0-based) 2, // last row (0-based) (short) 1 // last column (0-based )));

This class is mainly red. I checked the document below, modified it with (2), and pasted the code.

Package com. mouse. moon. frepoi; import java. io. fileOutputStream; import java. io. IOException; 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. hssf. util. HSSFColor; import org. apache. poi. ss. util. cellRangeAddress; // test complex cell merging public class TestFreMergedCells {public static void main (String [] args) throws IOException {HSSFWorkbook wb = new HSSFWorkbook (); HSSFSheet sheet = wb. createSheet ("new sheet"); HSSFRow row = sheet. createRow (0); HSSFCell cell = row. createCell (0); // HSSFRow row = sheet. createRow (0); // HSSFCell cell = row. createCell (1); cell. setCellValue ("value"); this indicates that the value is set to (1st) the second column of row 1st // The value is (1st) indicates that the second column of row, if you think that since I have merged the first and second rows of the first column, can I set (, // you can test whether this is obviously not a cell. setCellValue ("merge the second row of the first row, first column"); sheet. addMergedRegion (new CellRangeAddress (0, 1, 0, 0); // merge the first row of the second column and the second row of sheet. addMergedRegion (new CellRangeAddress (0, 1, 1, 1); // 1. generate the font object HSSFFont font = wb. createFont (); font. setFontHeightInPoints (short) 10); font. setFontName ("" "); font. setColor (HSSFColor. BLUE. index); font. setBoldweight (short) 0.8); // 2. generate the style object HSSFCellStyle style = wb. createCellStyle (); style. setAlignment (HSSFCellStyle. ALIGN_CENTER); style. setverticalignment (HSSFCellStyle. VERTICAL_CENTER); style. setFont (font); // call the font style object style. setWrapText (true); // Add the style example style of the table border. setBorderTop (HSSFCellStyle. BORDER_DOUBLE); style. setBorderLeft (HSSFCellStyle. BORDER_DOUBLE); style. setTopBorderColor (HSSFColor. GOLD. index); style. setLeftBorderColor (HSSFColor. PLUM. index); // 3. cell Application style cell. setCellStyle (style); // row: Merge the first row, the second row, and the third row. // column description: The column also starts from 0, and 0 indicates the first column. // column: merge the fifth, sixth, seventh, eighth, and ninth columns with HSSFRow rowOther = sheet. createRow (0); HSSFCell cellOther = rowOther. createCell (4); cellOther. setCellValue ("multi-row, multi-column merge"); sheet. addMergedRegion (new CellRangeAddress (0, 2, 4, 9); // the following code inserts a string HSSFRow row2 = sheet in D4 cell. createRow (3); HSSFCell cell2 = row2.createCell (3); cell2.setCellValue ("only one data "); // Write the output to a file FileOutputStream fileOut = new FileOutputStream ("/Mouse/poi/test.xls"); wb. write (fileOut); fileOut. close ();}}

Make a simple description. In fact, you can also find out in your own test. This is the problem of setting data for merging cells.

HSSFRow rowOther = sheet. createRow (0); HSSFCell cellOther = rowOther. createCell (4); cellOther. setCellValue ("multi-row, multi-column merge"); sheet. addMergedRegion (new CellRangeAddress (0, 2, 4, 9 ));

For example, if you create 1st rows (0 represents 1st rows) and 5th columns (4 represents 5th columns), the first to third rows and 5th columns to 19th columns are merged below, when setting the value, you can only set the coordinates to (0, 4). If you change to the following:

HSSFRow rowOther = sheet. createRow (1); HSSFCell cellOther = rowOther. createCell (4); cellOther. setCellValue ("multi-row, multi-column merge"); sheet. addMergedRegion (new CellRangeAddress (0, 2, 4, 9 ));

At this time, the value is 2nd rows, and the Fifth Column is actually in the merged cell, but the final table that actually exists does not have data.

The header I created is dynamically generated, and the rows are also dynamic. The example exported is as follows:

Iii. Summary

In fact, to solve a problem, you only need to find a way, and then try and find documents calmly. Many problems are not as difficult as you think. I haven't written a blog for a long time. In fact, some of them have been in the draft box. I should have insisted on writing them recently. Hard work, this year's effort, read more books, plan to proceed as usual. GO.