Excel formatting with Java Apache POI -
i have excel file in following format:
i restructure format , have outputted in following format:
the sections repeating , can appear in order have either format. 'check' column merged across rows shown in image.
how can achieve this?
i used guava's table data structure save reinventing wheel
a few caveats;
- there may mathematical trick matrices or pivot tables or functional programming may same job less code.
- i'm not sure if i'm using latest , greatest poi classes this
- i have gone brute force , many data iterations should easy follow.
- i've assumed practical identify ranges of cells define data (abc, xyz, etc.)
- i guessed first row , column of merged area contain actual value (i don't know if case)
- the code optimized final variables etc.
import com.google.common.collect.hashbasedtable; import com.google.common.collect.table; import java.io.file; import java.io.fileinputstream; import java.io.ioexception; 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.row; import org.apache.poi.ss.usermodel.sheet; import org.apache.poi.ss.util.cellrangeaddress; public class excelformatting { public static void main(string[] args) throws ioexception { fileinputstream file = new fileinputstream(new file("data.xls")); hssfworkbook workbook = new hssfworkbook(file); hssfsheet sheet = workbook.getsheetat(0); // abc - range a3:f7 in data.xls table<integer, integer, string> abc = getvalues(sheet, 2, 0, 6, 5); //displaytable(abc); // xyz - range a11:e14 in data.xls table<integer, integer, string> xyz = getvalues(sheet, 10, 0, 13, 4); //displaytable(xyz); table<integer, integer, string> results = hashbasedtable.create(); // headers results.put(0, 0, "name"); results.put(0, 1, "section"); results.put(0, 2, "check"); results.put(0, 3, "cat"); results.put(0, 4, "value"); // process abc table results.putall(transform(abc, "abc", results.rowmap().size())); // process xyz table results.putall(transform(xyz, "xyz", results.rowmap().size())); displaytable(results); } private static table<integer, integer, string> transform(table<integer, integer, string> table, string section, int offset) { table<integer, integer, string> results = hashbasedtable.create(); int maxrows = table.rowmap().size(); int maxcols = table.columnmap().size(); // first col name string name; // second col check string check; string cata, catb, catc, catd = null; // offset added table putall doesn't overlap data rather append int outputrow = 0 + offset; (int r = 0; r < maxrows; r++) { name = table.get(r, 0); check = table.get(r, 1); if (check == null) { check = ""; } cata = table.get(r, 2); catb = table.get(r, 3); catc = table.get(r, 4); if (maxcols == 6) { catd = table.get(r, 5); } if (cata != null) { results = addrow(results, outputrow, name, section, check, "a", cata); outputrow++; } if (catb != null) { results = addrow(results, outputrow, name, section, check, "b", catb); outputrow++; } if (catc != null) { results = addrow(results, outputrow, name, section, check, "c", catc); outputrow++; } if (catd != null) { results = addrow(results, outputrow, name, section, check, "d", catd); outputrow++; } } return results; } private static table<integer, integer, string> addrow(table<integer, integer, string> table, int row, string name, string section, string check, string category, string value) { table.put(row, 0, name); table.put(row, 1, section); table.put(row, 2, check); table.put(row, 3, category); table.put(row, 4, value); return table; } // convert "merged" data normal populated cells private static table<integer, integer, string> getvalues(sheet sheet, int minrow, int mincol, int maxrow, int maxcol) { table<integer, integer, string> table = hashbasedtable.create(); string result; row row; cell col; int r = minrow; int c; while (r <= maxrow) { row = sheet.getrow(r); c = mincol; while (c <= maxcol) { col = row.getcell(c); result = getvalue(sheet, row, col); if (result != null) { table.put(r - minrow, c - mincol, result); } c++; } r++; } return table; } private static string getvalue(sheet sheet, row row, cell cell) { string result = getcellvalue(cell); if (result == null) { result = getmergedareavalue(sheet, row.getrownum(), cell.getcolumnindex()); } return result; } private static string getcellvalue(cell cell) { string result = null; if (cell.cell_type_string == cell.getcelltype()) { result = cell.getstringcellvalue(); } if (cell.cell_type_numeric == cell.getcelltype()) { result = cell.getnumericcellvalue() + ""; } return result; } private static string getmergedareavalue(sheet sheet, int row, int col) { int mergedareas = sheet.getnummergedregions(); cellrangeaddress mergearea; (int = 0; < mergedareas; i++) { mergearea = sheet.getmergedregion(i); if (mergearea.isinrange(row, col)) { return getcellvalue( sheet.getrow(mergearea.getfirstrow()) .getcell(mergearea.getfirstcolumn())); } } return null; } private static void displaytable(table<integer, integer, string> table) { int maxrows = table.rowmap().size(); int maxcols = table.columnmap().size(); stringbuilder sb = new stringbuilder(); string val; (int r = 0; r < maxrows; r++) { (int c = 0; c < maxcols; c++) { val = table.get(r, c); if (val != null) { sb.append(val); } sb.append("\t\t"); } sb.append('\n'); } system.out.println(sb.tostring()); } } this should result in following output:
name sect check cat value qqq abc 1.0 qqq abc d 5.0 www abc x b 2.0 www abc x d 5.0 eee abc x c 3.0 eee abc x c 4.0 rrr abc d 6.0 qqq xyz 1.0 qqq xyz c 4.0 www xyz x b 2.0 www xyz x c 4.0 www xyz x b 3.0 www xyz x c 4.0 eee xyz x c 4.0
Comments
Post a Comment