List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:Servelt.ExcelWriter.java
public ExcelWriter(String pjname, String id) { logger = Logger.getLogger(ExcelWriter.class.getName()); String cate = null;/*from ww w . jav a2 s. c o m*/ Map<String, List<String>> map = new TreeMap<String, List<String>>(); List<String> typ = new ArrayList<String>(); List<String> des = new ArrayList<String>(); Map<String, List<String[]>> emap = new TreeMap<String, List<String[]>>(); String[] stra; try (Connection conn = DriverManager.getConnection(DB2url, DB2user, DB2password)) { Statement stm = conn.createStatement(); ResultSet result = MySQLSyntax.getResultSet(stm, db2.project_Table.TABLE_NAME, db2.project_Table.ProjectName, pjname); if (result.next()) { cate = result.getString(db2.project_Table.Category); } result = MySQLSyntax.getResultSet(stm, db2.description_Table.TABLE_NAME, db2.description_Table.ProjectName, pjname); while (result.next()) { stra = new String[2]; stra[0] = result.getString(db2.description_Table.Type); stra[1] = result.getString(db2.description_Table.Description); if (!emap.containsKey("Others")) { emap.put("Others", new ArrayList<String[]>()); } emap.get("Others").add(stra); } ProjectResultMap(stm, map, pjname, Antennas.TableName, db2.project_antennas_Table.TABLE_NAME, Antennas.MainKey); ProjectResultMap(stm, map, pjname, Battery.TableName, db2.project_battery_Table.TABLE_NAME, Battery.MainKey); ProjectResultMap(stm, map, pjname, Button.TableName, db2.project_button_Table.TABLE_NAME, Button.MainKey); ProjectResultMap(stm, map, pjname, Camera.TableName, db2.project_camera_Table.TABLE_NAME, Camera.MainKey); ProjectResultMap(stm, map, pjname, CardReader.TableName, db2.project_card_reader_Table.TABLE_NAME, CardReader.MainKey); ProjectResultMap(stm, map, pjname, Charger.TableName, db2.project_charger_Table.TABLE_NAME, Charger.MainKey); ProjectResultMap(stm, map, pjname, ClickPad.TableName, db2.project_click_pad_Table.TABLE_NAME, ClickPad.MainKey); ProjectResultMap(stm, map, pjname, CPU.TableName, db2.project_cpu_Table.TABLE_NAME, CPU.MainKey); ProjectResultMap(stm, map, pjname, Ethernet.TableName, db2.project_ethernet_Table.TABLE_NAME, Ethernet.MainKey); ProjectResultMap(stm, map, pjname, KBCEBC.TableName, db2.project_kbc_ebc_Table.TABLE_NAME, KBCEBC.MainKey); ProjectResultMap(stm, map, pjname, Keyboard.TableName, db2.project_keyboard_Table.TABLE_NAME, Keyboard.MainKey); ProjectResultMap(stm, map, pjname, LcdPanel.TableName, db2.project_lcd_panel_Table.TABLE_NAME, LcdPanel.MainKey); ProjectResultMap(stm, map, pjname, Measurement.TableName, db2.project_measurement_Table.TABLE_NAME, Measurement.MainKey); ProjectResultMap(stm, map, pjname, Memory.TableName, db2.project_memory_Table.TABLE_NAME, Memory.MainKey); ProjectResultMap(stm, map, pjname, Sensor.TableName, db2.project_sensor_Table.TABLE_NAME, Sensor.MainKey); ProjectResultMap(stm, map, pjname, Storage.TableName, db2.project_storage_Table.TABLE_NAME, Storage.MainKey); ProjectResultMap(stm, map, pjname, TouchPanel.TableName, db2.project_touch_panel_Table.TABLE_NAME, TouchPanel.MainKey); ProjectResultMap(stm, map, pjname, WLAN.TableName, db2.project_wlan_Table.TABLE_NAME, WLAN.MainKey); ProjectResultMap(stm, map, pjname, WWAN.TableName, db2.project_wwan_Table.TABLE_NAME, WWAN.MainKey); ProjectResultMap(stm, map, pjname, Graphic.TableName, db2.project_graphic_Table.TABLE_NAME, Graphic.MainKey); ProjectResultMap(stm, map, pjname, AudioCodec.TableName, db2.project_audio_codec_Table.TABLE_NAME, AudioCodec.MainKey); ProjectResultMap(stm, map, pjname, PanelInterfaceBridge.TableName, db2.project_panel_interface_bridge_Table.TABLE_NAME, PanelInterfaceBridge.MainKey); ProjectResultMap(stm, map, pjname, ExternalStorageCard.TableName, db2.project_external_storage_card_Table.TABLE_NAME, ExternalStorageCard.MainKey); ProjectResultMap(stm, map, pjname, ODD.TableName, db2.project_odd_Table.TABLE_NAME, ODD.MainKey); ProjectResultMap(stm, map, pjname, Speaker.TableName, db2.project_speaker_Table.TABLE_NAME, Speaker.MainKey); ProjectResultMap(stm, map, pjname, Mic.TableName, db2.project_mic_Table.TABLE_NAME, Mic.MainKey); ProjectResultMap(stm, map, pjname, IoPort.TableName, db2.project_io_port_Table.TABLE_NAME, IoPort.MainKey); ProjectResultMap(stm, map, pjname, OS.TableName, db2.project_os_Table.TABLE_NAME, OS.MainKey); } catch (SQLException ex) { logger.log(Level.WARNING, "In SQLException (getSearchProjectResultPage) : " + ex.toString()); ex.printStackTrace(); } List<String[]> list; String str; try (Connection conn = DriverManager.getConnection(DBurl, DBuser, DBpassword)) { Statement stm = conn.createStatement(); ResultSet result = null; for (Entry<String, List<String>> entry : map.entrySet()) { switch (entry.getKey()) { case db.antennas_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.antennas_Table.TABLE_NAME, Antennas.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.antennas_Table.Type); stra[1] = result.getString(db.antennas_Table.Description); if (!emap.containsKey("Antenna")) emap.put("Antenna", new ArrayList<String[]>()); emap.get("Antenna").add(stra); } } break; } case db.audio_codec_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.audio_codec_Table.TABLE_NAME, AudioCodec.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.audio_codec_Table.Vendor); stra[1] = result.getString(db.audio_codec_Table.Model) + "\n" + result.getString(db.audio_codec_Table.Description); if (!emap.containsKey("Audio Codec")) emap.put("Audio Codec", new ArrayList<String[]>()); emap.get("Audio Codec").add(stra); } } break; } case db.battery_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.battery_Table.TABLE_NAME, Battery.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.battery_Table.Vendor); stra[1] = result.getString(db.battery_Table.ModelName) + " " + result.getString(db.battery_Table.Capacity) + " " + result.getString(db.battery_Table.Cell) + "\n" + result.getString(db.battery_Table.Description); if (!emap.containsKey("Battery Pack")) emap.put("Battery Pack", new ArrayList<String[]>()); emap.get("Battery Pack").add(stra); } } break; } case db.button_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.button_Table.TABLE_NAME, Button.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.button_Table.Type); stra[0] = result.getString(db.button_Table.Description); if (!emap.containsKey("Button")) emap.put("Button", new ArrayList<String[]>()); emap.get("Button").add(stra); } } break; } case db.camera_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.camera_Table.TABLE_NAME, Camera.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.camera_Table.Vendor); stra[1] = result.getString(db.camera_Table.Model) + " " + result.getString(db.camera_Table.Sensor_IC) + " " + result.getString(db.camera_Table.Pixel_Mega) + " " + result.getString(db.camera_Table.AF_FF) + " " + result.getString(db.camera_Table.Lens) + "\n" + result.getString(db.camera_Table.Description); if (!emap.containsKey("Web CAM")) emap.put("Web CAM", new ArrayList<String[]>()); emap.get("Web CAM").add(stra); } } break; } case db.card_reader_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.card_reader_Table.TABLE_NAME, CardReader.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.card_reader_Table.Vendor); stra[1] = result.getString(db.card_reader_Table.Model) + "\n" + result.getString(db.card_reader_Table.Description); if (!emap.containsKey("Card reader")) emap.put("Card reader", new ArrayList<String[]>()); emap.get("Card reader").add(stra); } } break; } case db.charger_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.charger_Table.TABLE_NAME, Charger.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.charger_Table.Vendor); stra[1] = result.getString(db.charger_Table.ModelName) + " " + result.getString(db.charger_Table.Type) + " " + result.getString(db.charger_Table.OUTPUT) + " " + result.getString(db.charger_Table.INPUT) + "\n" + result.getString(db.charger_Table.Description); if (!emap.containsKey("Adaptor")) emap.put("Adaptor", new ArrayList<String[]>()); emap.get("Adaptor").add(stra); } } break; } case db.click_pad_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.click_pad_Table.TABLE_NAME, ClickPad.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.click_pad_Table.Vendor); stra[1] = result.getString(db.click_pad_Table.ModelName) + "\n" + result.getString(db.click_pad_Table.Description); if (!emap.containsKey("Pointing Device")) emap.put("Pointing Device", new ArrayList<String[]>()); emap.get("Pointing Device").add(stra); } } break; } case db.cpu_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.cpu_Table.TABLE_NAME, CPU.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.cpu_Table.Vendor) + "/" + result.getString(db.cpu_Table.CodeName); stra[1] = result.getString(db.cpu_Table.ModelName) + " " + result.getString(db.cpu_Table.MaxCoreFreq) + " " + result.getString(db.cpu_Table.CoreThreats) + " " + result.getString(db.cpu_Table.Gfx) + " " + result.getString(db.cpu_Table.TDP); if (!emap.containsKey("CPU/SoC")) emap.put("CPU/SoC", new ArrayList<String[]>()); emap.get("CPU/SoC").add(stra); } } break; } case db.ethernet_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.ethernet_Table.TABLE_NAME, Ethernet.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.ethernet_Table.Vendor); stra[1] = result.getString(db.ethernet_Table.ModelName) + "\n" + result.getString(db.ethernet_Table.Description); if (!emap.containsKey("LAN Controller")) emap.put("LAN Controller", new ArrayList<String[]>()); emap.get("LAN Controller").add(stra); } } break; } case db.external_storage_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.external_storage_Table.TABLE_NAME, ExternalStorageCard.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.external_storage_Table.Vendor); stra[1] = result.getString(db.external_storage_Table.Type) + " " + result.getString(db.external_storage_Table.Model) + " " + result.getString(db.external_storage_Table.Dimension) + " " + result.getString(db.external_storage_Table.Interface) + " " + result.getString(db.external_storage_Table.Speed) + " " + result.getString(db.external_storage_Table.Capacity); if (!emap.containsKey("Extra storage card")) emap.put("Extra storage card", new ArrayList<String[]>()); emap.get("Extra storage card").add(stra); } } break; } case db.graphic_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.graphic_Table.TABLE_NAME, Graphic.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.graphic_Table.Vendor); stra[1] = result.getString(db.graphic_Table.ModelName) + " " + result.getString(db.graphic_Table.MemoryType) + " " + result.getString(db.graphic_Table.vRAM_size) + "\n" + result.getString(db.graphic_Table.Description); if (!emap.containsKey("Graphic")) emap.put("Graphic", new ArrayList<String[]>()); emap.get("Graphic").add(stra); } } break; } case db.io_port_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.io_port_Table.TABLE_NAME, IoPort.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = ""; stra[1] = result.getString(db.io_port_Table.Standard) + " " + result.getString(db.io_port_Table.ConnectorType) + "\n" + result.getString(db.io_port_Table.Description); if (!emap.containsKey("I/O Port")) emap.put("I/O Port", new ArrayList<String[]>()); emap.get("I/O Port").add(stra); } } break; } case db.kbc_ebc_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.kbc_ebc_Table.TABLE_NAME, KBCEBC.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.kbc_ebc_Table.Vendor); stra[1] = result.getString(db.kbc_ebc_Table.Model) + "\n" + result.getString(db.kbc_ebc_Table.Description); if (!emap.containsKey("KBC/EBC")) emap.put("KBC/EBC", new ArrayList<String[]>()); emap.get("KBC/EBC").add(stra); } } break; } case db.keyboard_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.keyboard_Table.TABLE_NAME, Keyboard.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.keyboard_Table.Vendor); stra[1] = result.getString(db.keyboard_Table.ModelName) + " " + result.getString(db.keyboard_Table.Type) + " " + result.getString(db.keyboard_Table.OS) + " " + result.getString(db.keyboard_Table.Layout) + "\n" + result.getString(db.keyboard_Table.Description); if (!emap.containsKey("Keyboard")) emap.put("Keyboard", new ArrayList<String[]>()); emap.get("Keyboard").add(stra); } } break; } case db.lcd_panel_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.lcd_panel_Table.TABLE_NAME, LcdPanel.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.lcd_panel_Table.Vendor); stra[1] = result.getString(db.lcd_panel_Table.ModelName) + " " + result.getString(db.lcd_panel_Table.Size) + " " + result.getString(db.lcd_panel_Table.Resolution) + " " + result.getString(db.lcd_panel_Table.Type) + " " + result.getString(db.lcd_panel_Table.Nits) + " " + result.getString(db.lcd_panel_Table.Touch) + " " + result.getString(db.lcd_panel_Table.Interface); if (!emap.containsKey("LCD")) emap.put("LCD", new ArrayList<String[]>()); emap.get("LCD").add(stra); } } break; } case db.measurement_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.measurement_Table.TABLE_NAME, Measurement.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.measurement_Table.Type); stra[1] = result.getString(db.measurement_Table.Dimension) + " " + result.getString(db.measurement_Table.Weight); if (!emap.containsKey("Physical Outline")) emap.put("Physical Outline", new ArrayList<String[]>()); emap.get("Physical Outline").add(stra); } } break; } case db.memory_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.memory_Table.TABLE_NAME, Memory.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.memory_Table.Vendor); stra[1] = result.getString(db.memory_Table.Type) + " " + result.getString(db.memory_Table.Frequency) + " " + result.getString(db.memory_Table.Capacity) + "\n" + result.getString(db.memory_Table.Description); if (!emap.containsKey("Memory")) emap.put("Memory", new ArrayList<String[]>()); emap.get("Memory").add(stra); } } break; } case db.mic_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.mic_Table.TABLE_NAME, Mic.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = "Mic"; stra[1] = result.getString(db.mic_Table.ModelName) + "\n" + result.getString(db.mic_Table.Description); if (!emap.containsKey("Audio")) emap.put("Audio", new ArrayList<String[]>()); emap.get("Audio").add(stra); } } break; } case db.speaker_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.speaker_Table.TABLE_NAME, Speaker.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = "Speaker"; stra[1] = result.getString(db.speaker_Table.ModelName) + "\n" + result.getString(db.mic_Table.Description); if (!emap.containsKey("Audio")) emap.put("Audio", new ArrayList<String[]>()); emap.get("Audio").add(stra); } } break; } case db.odd_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.odd_Table.TABLE_NAME, ODD.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.odd_Table.Vendor); stra[1] = result.getString(db.odd_Table.Model) + " " + result.getString(db.odd_Table.Interface) + " " + result.getString(db.odd_Table.zHigh) + "\n" + result.getString(db.odd_Table.Description); if (!emap.containsKey("ODD")) emap.put("ODD", new ArrayList<String[]>()); emap.get("ODD").add(stra); } } break; } case db.os_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.os_Table.TABLE_NAME, OS.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.os_Table.Type); stra[1] = result.getString(db.os_Table.Description); if (!emap.containsKey("OS")) emap.put("OS", new ArrayList<String[]>()); emap.get("OS").add(stra); } } break; } case db.panel_interface_bridge_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.panel_interface_bridge_Table.TABLE_NAME, PanelInterfaceBridge.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.panel_interface_bridge_Table.Vendor); stra[1] = result.getString(db.panel_interface_bridge_Table.ModelName) + "\n" + result.getString(db.panel_interface_bridge_Table.Description); if (!emap.containsKey("Bridge IC")) emap.put("Bridge IC", new ArrayList<String[]>()); emap.get("Bridge IC").add(stra); } } break; } case db.sensor_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.sensor_Table.TABLE_NAME, Sensor.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.sensor_Table.Type); stra[1] = result.getString(db.sensor_Table.Vendor) + " " + result.getString(db.sensor_Table.ModelName) + "\n" + result.getString(db.sensor_Table.Description); if (!emap.containsKey("Sensor")) emap.put("Sensor", new ArrayList<String[]>()); emap.get("Sensor").add(stra); } } break; } case db.storage_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.storage_Table.TABLE_NAME, Storage.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.storage_Table.Vendor); stra[1] = result.getString(db.storage_Table.Type) + " " + result.getString(db.storage_Table.Model) + " " + result.getString(db.storage_Table.Capacity) + " " + result.getString(db.storage_Table.Interface) + " " + result.getString(db.storage_Table.zHight) + " " + result.getString(db.storage_Table.Rpm) + " " + result.getString(db.storage_Table.Speed); if (!emap.containsKey("Storage")) emap.put("Storage", new ArrayList<String[]>()); emap.get("Storage").add(stra); } } break; } case db.touch_panel_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.touch_panel_Table.TABLE_NAME, TouchPanel.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.touch_panel_Table.Vendor); stra[1] = result.getString(db.touch_panel_Table.ModelName) + " " + result.getString(db.touch_panel_Table.IC) + " " + result.getString(db.touch_panel_Table.Type) + " " + result.getString(db.touch_panel_Table.Multi_Touch) + " " + result.getString(db.touch_panel_Table.Stylus); if (!emap.containsKey("Touch solution")) emap.put("Touch solution", new ArrayList<String[]>()); emap.get("Touch solution").add(stra); } } break; } case db.wlan_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.wlan_Table.TABLE_NAME, WLAN.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.wlan_Table.Vendor); stra[1] = result.getString(db.wlan_Table.ModelName) + " " + result.getString(db.wlan_Table.Protocal) + " " + result.getString(db.wlan_Table.nTnR) + " " + result.getString(db.wlan_Table.Band) + " " + result.getString(db.wlan_Table.Bluetooth) + " " + result.getString(db.wlan_Table.Max_Speed); if (!emap.containsKey("Wireless LAN and Bluetooth Combo")) emap.put("Wireless LAN and Bluetooth Combo", new ArrayList<String[]>()); emap.get("Wireless LAN and Bluetooth Combo").add(stra); } } break; } case db.wwan_Table.TABLE_NAME: { for (String s : entry.getValue()) { result = MySQLSyntax.getResultSet(stm, db.wwan_Table.TABLE_NAME, WWAN.MainKey, s); if (result.next()) { stra = new String[2]; stra[0] = result.getString(db.wwan_Table.Vendor); stra[1] = result.getString(db.wwan_Table.ModelName) + " " + result.getString(db.wwan_Table.Type) + "\n" + result.getString(db.wwan_Table.Description); if (!emap.containsKey("WWAN")) emap.put("WWAN", new ArrayList<String[]>()); emap.get("WWAN").add(stra); } } break; } } } } catch (SQLException ex) { logger.log(Level.WARNING, "In SQLException (ExcelWriter) : " + ex.toString()); ex.printStackTrace(); } try { workbook = new XSSFWorkbook(new File(filepath + template)); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row; XSSFCell cell; int y, k; y = 45; List<Entry<String, List<String[]>>> elist = new ArrayList<Entry<String, List<String[]>>>(); if (emap.containsKey("CPU/SoC")) elist.add(new SimpleEntry<String, List<String[]>>("CPU/SoC", emap.get("CPU/SoC"))); if (emap.containsKey("Graphic")) elist.add(new SimpleEntry<String, List<String[]>>("Graphic", emap.get("Graphic"))); if (emap.containsKey("Memory")) elist.add(new SimpleEntry<String, List<String[]>>("Memory", emap.get("Memory"))); if (emap.containsKey("Audio Codec")) elist.add(new SimpleEntry<String, List<String[]>>("Audio Codec", emap.get("Audio Codec"))); if (emap.containsKey("KBC/EBC")) elist.add(new SimpleEntry<String, List<String[]>>("KBC/EBC", emap.get("KBC/EBC"))); if (emap.containsKey("LAN Controller")) elist.add(new SimpleEntry<String, List<String[]>>("LAN Controller", emap.get("LAN Controller"))); if (emap.containsKey("Wireless LAN and Bluetooth Combo")) elist.add(new SimpleEntry<String, List<String[]>>("Wireless LAN and Bluetooth Combo", emap.get("Wireless LAN and Bluetooth Combo"))); if (emap.containsKey("WWAN")) elist.add(new SimpleEntry<String, List<String[]>>("WWAN", emap.get("WWAN"))); if (emap.containsKey("Antenna")) elist.add(new SimpleEntry<String, List<String[]>>("Antenna", emap.get("Antenna"))); if (emap.containsKey("Pointing Device")) elist.add(new SimpleEntry<String, List<String[]>>("Pointing Device", emap.get("Pointing Device"))); if (emap.containsKey("LCD")) elist.add(new SimpleEntry<String, List<String[]>>("LCD", emap.get("LCD"))); if (emap.containsKey("Touch solution")) elist.add(new SimpleEntry<String, List<String[]>>("Touch solution", emap.get("Touch solution"))); if (emap.containsKey("Bridge IC")) elist.add(new SimpleEntry<String, List<String[]>>("Bridge IC", emap.get("Bridge IC"))); if (emap.containsKey("Storage")) elist.add(new SimpleEntry<String, List<String[]>>("Storage", emap.get("Storage"))); if (emap.containsKey("Extra storage card")) elist.add(new SimpleEntry<String, List<String[]>>("Extra storage card", emap.get("Extra storage card"))); if (emap.containsKey("ODD")) elist.add(new SimpleEntry<String, List<String[]>>("ODD", emap.get("ODD"))); if (emap.containsKey("Card reader")) elist.add(new SimpleEntry<String, List<String[]>>("Card reader", emap.get("Card reader"))); if (emap.containsKey("Audio")) elist.add(new SimpleEntry<String, List<String[]>>("Audio", emap.get("Audio"))); if (emap.containsKey("Web CAM")) elist.add(new SimpleEntry<String, List<String[]>>("Web CAM", emap.get("Web CAM"))); if (emap.containsKey("Sensor")) elist.add(new SimpleEntry<String, List<String[]>>("Sensor", emap.get("Sensor"))); if (emap.containsKey("Battery Pack")) elist.add(new SimpleEntry<String, List<String[]>>("Battery Pack", emap.get("Battery Pack"))); if (emap.containsKey("Adaptor")) elist.add(new SimpleEntry<String, List<String[]>>("Adaptor", emap.get("Adaptor"))); if (emap.containsKey("I/O Port")) elist.add(new SimpleEntry<String, List<String[]>>("I/O Port", emap.get("I/O Port"))); if (emap.containsKey("Keyboard")) elist.add(new SimpleEntry<String, List<String[]>>("Keyboard", emap.get("Keyboard"))); if (emap.containsKey("Button")) elist.add(new SimpleEntry<String, List<String[]>>("Button", emap.get("Button"))); if (emap.containsKey("OS")) elist.add(new SimpleEntry<String, List<String[]>>("OS", emap.get("OS"))); if (emap.containsKey("Physical Outline")) elist.add( new SimpleEntry<String, List<String[]>>("Physical Outline", emap.get("Physical Outline"))); if (emap.containsKey("Others")) elist.add(new SimpleEntry<String, List<String[]>>("Others", emap.get("Others"))); for (Entry<String, List<String[]>> entry : elist) { for (int i = 0; i < entry.getValue().size(); i++) { sheet.createRow(y + i); } //System.out.println(entry.getValue().size()); row = sheet.getRow(y); cell = row.createCell(0); cell.setCellValue(entry.getKey()); sheet.addMergedRegion(new CellRangeAddress(y, y + entry.getValue().size() - 1, 0, 1)); k = 0; for (String[] s : entry.getValue()) { //System.out.println(s[0] + s[1]); row = sheet.getRow(y + k); cell = row.createCell(2); cell.setCellValue(s[0]); sheet.addMergedRegion(new CellRangeAddress(y + k, y + k, 2, 3)); cell = row.createCell(4); cell.setCellValue(s[1]); sheet.addMergedRegion(new CellRangeAddress(y + k, y + k, 4, 10)); k++; } y = y + entry.getValue().size(); } filename = pjname + ".xlsx"; try (FileOutputStream out = new FileOutputStream(filepath + filename)) { workbook.write(out); } } catch (Exception ex) { logger.log(Level.WARNING, "In Exception (ExcelWriter) : " + ex.toString()); ex.printStackTrace(); } }
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public StreamedContent getFilePlantilla() { filePlantilla = null;//from w w w. ja v a 2s . c o m String ruthPath = null; try { if (this.getEvaluacionSubir() != null && this.getEvaluacionSubir().getIdEvaluacion() != null) { // Se crea el libro XSSFWorkbook libro = new XSSFWorkbook(); // Se crea una hoja dentro del libro XSSFSheet sheetD = libro.createSheet(); //Obtener lista de alumnos del curso List<SieniAlumno> alumnosEval = sieniAlumnoFacadeRemote .findAlumnosInscritos(this.getEvaluacionSubir().getIdCurso().getIdCurso()); //Leer datos y colocarlos en la hoja int f = 0; //Guardar datos en celda for (SieniAlumno alumno : alumnosEval) { // Se crea una fila dentro de la hoja XSSFRow fila = sheetD.createRow(f); f++; // Se crea las celdas dentro de la fila XSSFCell celdaCarnet = fila.createCell((short) 0); XSSFCell celdaAlumno = fila.createCell((short) 1); XSSFCell celdaNota = fila.createCell((short) 2); //Colocar valor en celda celdaCarnet.setCellValue(alumno.getAlCarnet()); celdaAlumno.setCellValue(alumno.getNombreCompleto()); celdaNota.setCellValue((double) 0.00); } //Encabezados desde plantilla InputStream stream = ((ServletContext) FacesContext.getCurrentInstance().getExternalContext() .getContext()).getResourceAsStream("/resources/templates/PlantillaAlumnosEval.xlsx"); StreamedContent plantillaXLS = new DefaultStreamedContent(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Plantilla.xlsx"); XSSFWorkbook plantilla = new XSSFWorkbook(plantillaXLS.getStream()); XSSFSheet sheetP = plantilla.getSheetAt(0); //Filas que ocupa el encabezado de plantilla int encabezado = 3; //Quitar encabezado y desplazar Datos sheetD.shiftRows(0, sheetD.getLastRowNum(), encabezado); //Copiar contenido de plantilla a la hoja del reporte int inicio = 0; for (int row = 0; row < encabezado; row++) { copyRow(sheetP, sheetD, row, inicio); inicio++; } //Combinar las columnas al igual que la plantilla for (int m = 0; m < sheetP.getNumMergedRegions(); m++) { CellRangeAddress cellRangeAddress = sheetP.getMergedRegion(m).copy(); sheetD.addMergedRegion(cellRangeAddress); } //Evaluacion XSSFCell celdaEval = sheetD.getRow(0).getCell(1); celdaEval.setCellValue(this.getEvaluacionSubir().getEvNombre()); // Se salva el libro. FileOutputStream elFichero = new FileOutputStream("ListaAlumnos.xlsx"); libro.write(elFichero); elFichero.close(); //Leer libro para descarga FileInputStream file = new FileInputStream(new File("ListaAlumnos.xlsx")); filePlantilla = new DefaultStreamedContent(file, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "AlumnosEval.xlsx"); } else { new ValidationPojo().printMsj("Seleccione una evaluacion", FacesMessage.SEVERITY_ERROR); } } catch (Exception exc) { new ValidationPojo().printMsj( "Ocurrio un error al descargar plantilla ... consulte con el administrador" + ruthPath, FacesMessage.SEVERITY_ERROR); } return filePlantilla; }
From source file:tan.jam.jsf.Shifting.java
public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); XSSFRow newRow = worksheet.getRow(destinationRowNum); XSSFRow sourceRow = worksheet.getRow(sourceRowNum); if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {//from w w w . j a va 2 s .c o m newRow = worksheet.createRow(destinationRowNum); } for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); if (oldCell == null) { newCell = null; continue; } XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } newCell.setCellType(oldCell.getCellType()); switch (oldCell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: //newCell.setCellValue(oldCell.getNumericCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: newCell.setCellValue(""); break; } } for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } int inc = destinationRowNum + 1; worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc); }
From source file:tan.jam.jsf.Shifting.java
public static void MergeCells(XSSFWorkbook workbook, XSSFSheet worksheet) { int Mov = FindShift(workbook, worksheet); worksheet.addMergedRegion(new CellRangeAddress(1, 1, 12 + Mov, 13 + Mov)); worksheet.addMergedRegion(new CellRangeAddress(1, 1, 18 + Mov, 19 + Mov)); worksheet.addMergedRegion(new CellRangeAddress(1, 1, 24 + Mov, 25 + Mov)); worksheet.addMergedRegion(new CellRangeAddress(1, 1, 30 + Mov, 31 + Mov)); worksheet.addMergedRegion(new CellRangeAddress(1, 1, 36 + Mov, 37 + Mov)); worksheet.addMergedRegion(new CellRangeAddress(1, 1, 42 + Mov, 43 + Mov)); worksheet.addMergedRegion(new CellRangeAddress(1, 1, 48 + Mov, 49 + Mov)); worksheet.addMergedRegion(new CellRangeAddress(1, 1, 54 + Mov, 55 + Mov)); }
From source file:vd10_workbook.AbilityManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("kha_nang"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Kh nng lm vic"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 2 //last column (0-based) );/*from ww w .j av a 2 s . c o m*/ sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border this.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); this.setThickBorder(cell, workbook); this.setBackGroundColor(cell, workbook); //ID_NHAN_VIEN sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("ID Nhn vin"); this.setThickBorder(cell, workbook); this.setBackGroundColor(cell, workbook); //ID_NGOAI_NGU sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("ID Ngoi ng"); this.setThickBorder(cell, workbook); this.setBackGroundColor(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); this.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); this.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getEmployeeID()); cell = (XSSFCell) row.createCell((short) 2); this.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getLanguageID()); } }
From source file:vd10_workbook.AssignedVoteManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("phieu_phan_cong"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Phiu phn cng"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 4 //last column (0-based) );/*from w w w.ja v a2 s . co m*/ sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //Ngy bt u sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Ngy bt u"); AbilityManagement.setThickBorder(cell, workbook); //S ngy sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("S ngy"); AbilityManagement.setThickBorder(cell, workbook); //ID nhn vin sheet.setColumnWidth(3, 5000); cell = (XSSFCell) row.createCell((short) 3); cell.setCellValue("ID nhn vin"); AbilityManagement.setThickBorder(cell, workbook); //ID loi cng vic sheet.setColumnWidth(4, 5000); cell = (XSSFCell) row.createCell((short) 4); cell.setCellValue("ID loi cng vic"); AbilityManagement.setThickBorder(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getStartDate().toString()); cell = (XSSFCell) row.createCell((short) 2); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getNumberOfdate()); cell = (XSSFCell) row.createCell((short) 3); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getEmployeeID()); cell = (XSSFCell) row.createCell((short) 4); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getWorkTypeID()); } //== calculate sum of days ==// row = sheet.createRow((short) startRow + 2 + this.list.size()); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Tng"); //merge cells at column 1 & 2 cellRangeAddress = new CellRangeAddress(startRow + 2 + this.list.size(), //first row (0-based) startRow + 2 + this.list.size(), //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) ); sheet.addMergedRegion(cellRangeAddress); //set border RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderRight(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderTop(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); cell = row.createCell((short) 2); cell.setCellFormula("SUM(C" + (startRow + 1 + 2) + ":C" + (startRow + 2 + this.list.size()) + ")"); AbilityManagement.setThickBorder(cell, workbook); }
From source file:vd10_workbook.Company.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("cong_ty"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Thng tin cng ty"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 5 //last column (0-based) );/* w ww . j a va 2s .c om*/ sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border // AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //Tn cng ty sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Tn"); AbilityManagement.setThickBorder(cell, workbook); //?in thoi sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("?in thoi"); AbilityManagement.setThickBorder(cell, workbook); //?a ch sheet.setColumnWidth(3, 10000); cell = (XSSFCell) row.createCell((short) 3); cell.setCellValue("?a ch"); AbilityManagement.setThickBorder(cell, workbook); //Tui ti thiu sheet.setColumnWidth(4, 5000); cell = (XSSFCell) row.createCell((short) 4); cell.setCellValue("Tui ti thiu"); AbilityManagement.setThickBorder(cell, workbook); //Tui ti a sheet.setColumnWidth(5, 5000); cell = (XSSFCell) row.createCell((short) 5); cell.setCellValue("Tui ti a"); AbilityManagement.setThickBorder(cell, workbook); //== fill out the information ==// row = sheet.createRow((short) startRow + 2); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(1); //== content ==// //Tn cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.name); //?in thoi cell = (XSSFCell) row.createCell((short) 2); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.phoneNumber); //?a ch cell = (XSSFCell) row.createCell((short) 3); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.address); //Tui ti thiu cell = (XSSFCell) row.createCell((short) 4); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.minAge); //Tui ti a cell = (XSSFCell) row.createCell((short) 5); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.maxAge); }
From source file:vd10_workbook.DepartmentManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("don_vi"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("?n v"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) );//ww w . j av a2s . com sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //Tn n v sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Tn n v"); AbilityManagement.setThickBorder(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getName().toString()); } }
From source file:vd10_workbook.EmployeeManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("ds_nhan_vien"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Danh sch nhn vin"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 7 //last column (0-based) );/*w w w . j a va 2 s. c o m*/ sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //H? tn sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("H? tn"); AbilityManagement.setThickBorder(cell, workbook); //Gii tnh sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("Gii tnh"); AbilityManagement.setThickBorder(cell, workbook); //Ngy sinh sheet.setColumnWidth(3, 5000); cell = (XSSFCell) row.createCell((short) 3); cell.setCellValue("Ngy sinh"); AbilityManagement.setThickBorder(cell, workbook); //CMND sheet.setColumnWidth(4, 5000); cell = (XSSFCell) row.createCell((short) 4); cell.setCellValue("CMND"); AbilityManagement.setThickBorder(cell, workbook); //Mc lng sheet.setColumnWidth(5, 5000); cell = (XSSFCell) row.createCell((short) 5); cell.setCellValue("Mc lng"); AbilityManagement.setThickBorder(cell, workbook); //?a ch sheet.setColumnWidth(6, 10000); cell = (XSSFCell) row.createCell((short) 6); cell.setCellValue("?a ch"); AbilityManagement.setThickBorder(cell, workbook); //?n v sheet.setColumnWidth(7, 2000); cell = (XSSFCell) row.createCell((short) 7); cell.setCellValue("?n v"); AbilityManagement.setThickBorder(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getName().toString()); cell = (XSSFCell) row.createCell((short) 2); AbilityManagement.setThinBorder(cell, workbook); if (this.list.get(i).getSex() == 1) { cell.setCellValue("Nam"); } else { cell.setCellValue("N"); } cell = (XSSFCell) row.createCell((short) 3); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getDateOfbirth().toString()); cell = (XSSFCell) row.createCell((short) 4); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getIdentityNumber().toString()); cell = (XSSFCell) row.createCell((short) 5); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getSalary()); cell = (XSSFCell) row.createCell((short) 6); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getAddress()); cell = (XSSFCell) row.createCell((short) 7); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getDepartmentID()); } //== calculate sum of days ==// row = sheet.createRow((short) startRow + 2 + this.list.size()); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("S nhn vin c lng >= 6tr "); //merge cells at column 1 & 2 cellRangeAddress = new CellRangeAddress(startRow + 2 + this.list.size(), //first row (0-based) startRow + 2 + this.list.size(), //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) ); sheet.addMergedRegion(cellRangeAddress); //set border RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderRight(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderTop(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); cell = row.createCell((short) 2); cell.setCellFormula( "COUNTIF(F" + (startRow + 1 + 2) + ":F" + (startRow + 2 + this.list.size()) + ",\">= 6000000\")"); AbilityManagement.setThickBorder(cell, workbook); }
From source file:vd10_workbook.ForeignLanguageManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("ngoai_ngu"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Ngoi ng"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) );// w w w .ja v a2s .com sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //Tn ngn ng sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Ngn ng"); AbilityManagement.setThickBorder(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getName().toString()); } }