List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellValue
@Override public void setCellValue(boolean value)
From source file:Servelt.ExcelWriter.java
private void setDesCell(XSSFCell cell, String name) { XSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.GREEN.index); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); XSSFFont font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font);/*from w w w . j a v a 2 s . c o m*/ cell.setCellValue(name); cell.setCellStyle(style); }
From source file:Servelt.ExcelWriter.java
public ExcelWriter(String pjname, String id) { logger = Logger.getLogger(ExcelWriter.class.getName()); String cate = null;/* w ww . j a v 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:Sheets.GlobalSheet.java
@Override protected void createDateRows() throws SQLException { int currentColN = 11; Row row1 = sheet.getRow(21); //row com os "X" dos dias letivos Row row2 = sheet.getRow(23); //row com os dias da semana Row row3 = sheet.getRow(24); //row com as datas XSSFCellStyle style;// w w w . j a va 2 s.co m for (CustomDate cDate : dates) { XSSFCell cell1 = (XSSFCell) row1.getCell(currentColN); XSSFCell cell2 = (XSSFCell) row2.getCell(currentColN); XSSFCell cell3 = (XSSFCell) row3.getCell(currentColN); boolean classRegistered = false; for (StudentClassDiscipline studentClassDiscipline : studentClassDisciplines) { ArrayList<Class> classes = ClassDAO .getClassesByStudentClassDisciplineAndDate(studentClassDiscipline, cDate.getDate()); if (!classes.isEmpty()) //se pelo menos uma aula ocorreu { classRegistered = true; studentClassDiscipline.getWeekDays().addDate(cDate); //essa turma/disciplina tem aula no dia da semana de cDate for (Class classs : classes) { this.totalHours += classs.getHoursPerDay(); } } } if (classRegistered) { this.nClassDays++; cell1.setCellValue("X"); } cell2.setCellValue(" " + cDate.getWeekDay()); cell3.setCellValue(new java.util.Date(cDate.getDate().getTime())); style = (XSSFCellStyle) cell1.getCellStyle().clone(); XSSFColor fillBackgroundColorColor = style.getFillBackgroundColorColor(); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillForegroundColor(fillBackgroundColorColor); cell1.setCellStyle(style); style = (XSSFCellStyle) cell2.getCellStyle().clone(); style.setFillPattern(FillPatternType.NO_FILL); cell2.setCellStyle(style); style = (XSSFCellStyle) cell3.getCellStyle().clone(); style.setFillPattern(FillPatternType.NO_FILL); cell3.setCellStyle(style); currentColN++;//vai pra prxima coluna } }
From source file:Sheets.GlobalSheet.java
private void createStudentClassHeader() { // Rows setup Row courseRow = sheet.getRow(3);// w ww .j a va 2 s.c o m Row info1Row = sheet.getRow(5); Row info2Row = sheet.getRow(6); Row info3Row = sheet.getRow(7); // Info cells setups XSSFCell CourseCell = (XSSFCell) courseRow.getCell(0); XSSFCell PoloCell = (XSSFCell) info1Row.getCell(5); XSSFCell SupervisorCell = (XSSFCell) info1Row.getCell(20); XSSFCell ReferenceTimeCell = (XSSFCell) info1Row.getCell(36); XSSFCell HoursPerDayCell = (XSSFCell) info2Row.getCell(8); XSSFCell ShiftCell = (XSSFCell) info2Row.getCell(20); XSSFCell CodeCell = (XSSFCell) info2Row.getCell(36); XSSFCell BeginEndCell = (XSSFCell) info3Row.getCell(20); XSSFCell NDaysCell = (XSSFCell) info3Row.getCell(39); // Info labels CourseCell.setCellValue(this.studentClass.getCourse().toUpperCase()); PoloCell.setCellValue(this.studentClass.getPolo().getName().toUpperCase()); SupervisorCell.setCellValue("LEANDRO MAIA SILVA"); ReferenceTimeCell.setCellValue(dateFormat.format(startDate) + " - " + dateFormat.format(endDate)); HoursPerDayCell.setCellValue(this.totalHours / this.nClassDays + " horas / dia"); ShiftCell.setCellValue(this.studentClass.getShift().toUpperCase() + " / " + this.studentClass.getStartTime().toLocalTime().toString() + " - " + this.studentClass.getEndTime().toLocalTime().toString()); CodeCell.setCellValue(this.studentClass.getCode()); BeginEndCell.setCellValue(dateFormat.format(studentClassDisciplines.get(0).getStartDate()) + " - " + dateFormat.format(studentClassDisciplines.get(0).getEndDate().getTime())); NDaysCell.setCellValue(this.nDays); }
From source file:Sheets.PartialSheet.java
/** * Cria as linhas das datas/* w w w . j a va 2 s . c om*/ * @throws java.sql.SQLException */ @Override protected void createDateRows() throws SQLException { int currentColN = 11; Row row1 = sheet.getRow(10); //row com os "X" dos dias letivos Row row2 = sheet.getRow(12); //row com os dias da semana Row row3 = sheet.getRow(13); //row com as datas XSSFCellStyle style; for (CustomDate cDate : dates) { ArrayList<Class> classes = ClassDAO.getClassesByStudentClassDisciplineAndDate(studentClassDiscipline, cDate.getDate()); XSSFCell cell1 = (XSSFCell) row1.getCell(currentColN); XSSFCell cell2 = (XSSFCell) row2.getCell(currentColN); XSSFCell cell3 = (XSSFCell) row3.getCell(currentColN); if (!classes.isEmpty()) { Class classs = classes.get(0);//pega somente a primeira aula (no deveria existir mais de uma aula de uma displiplina em um mesmo dia) this.totalHours += classs.getHoursPerDay(); this.nClassDays++; cell1.setCellValue("X"); } cell2.setCellValue(" " + cDate.getWeekDay()); cell3.setCellValue(new java.util.Date(cDate.getDate().getTime())); style = (XSSFCellStyle) cell1.getCellStyle().clone(); XSSFColor fillBackgroundColorColor = style.getFillBackgroundColorColor(); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillForegroundColor(fillBackgroundColorColor); cell1.setCellStyle(style); style = (XSSFCellStyle) cell2.getCellStyle().clone(); style.setFillPattern(FillPatternType.NO_FILL); cell2.setCellStyle(style); style = (XSSFCellStyle) cell3.getCellStyle().clone(); style.setFillPattern(FillPatternType.NO_FILL); cell3.setCellStyle(style); currentColN++;//vai pra prxima coluna } }
From source file:Sheets.PartialSheet.java
@Override protected void createInfoRows() { // Rows setup Row courseRow = sheet.getRow(3);/*from w w w .j a v a 2 s .co m*/ Row info1Row = sheet.getRow(5); Row info2Row = sheet.getRow(6); Row info3Row = sheet.getRow(7); Row info4Row = sheet.getRow(8); // Info cells setups XSSFCell CourseCell = (XSSFCell) courseRow.getCell(0); XSSFCell DisciplineCell = (XSSFCell) info1Row.getCell(5); XSSFCell TeacherCell = (XSSFCell) info1Row.getCell(20); XSSFCell ReferenceTimeCell = (XSSFCell) info1Row.getCell(36); XSSFCell PoloCell = (XSSFCell) info2Row.getCell(5); XSSFCell SupervisorCell = (XSSFCell) info2Row.getCell(20); XSSFCell CodeCell = (XSSFCell) info2Row.getCell(36); XSSFCell HoursPerDayCell = (XSSFCell) info3Row.getCell(8); XSSFCell ShiftCell = (XSSFCell) info3Row.getCell(20); XSSFCell StudentClassCell = (XSSFCell) info3Row.getCell(36); XSSFCell TotalHoursCell = (XSSFCell) info4Row.getCell(8); XSSFCell BeginEndCell = (XSSFCell) info4Row.getCell(20); XSSFCell NDaysCell = (XSSFCell) info4Row.getCell(39); // Info labels CourseCell.setCellValue(this.studentClassDiscipline.getStudentClass().getCourse().toUpperCase()); DisciplineCell.setCellValue(this.studentClassDiscipline.getDiscipline().getName().toUpperCase()); TeacherCell.setCellValue(this.studentClassDiscipline.getTeacher().getName().toUpperCase()); ReferenceTimeCell.setCellValue(dateFormat.format(startDate) + "-" + dateFormat.format(endDate)); PoloCell.setCellValue(this.studentClassDiscipline.getStudentClass().getPolo().getName().toUpperCase()); SupervisorCell.setCellValue("LEANDRO MAIA SILVA"); CodeCell.setCellValue(this.studentClassDiscipline.getStudentClass().getCode()); HoursPerDayCell.setCellValue(this.totalHours / this.nClassDays + " horas / dia"); ShiftCell.setCellValue(this.studentClassDiscipline.getStudentClass().getShift().toUpperCase()); StudentClassCell .setCellValue(this.studentClassDiscipline.getStudentClass().getPolo().getName().toUpperCase()); TotalHoursCell.setCellValue(this.totalHours + " horas"); BeginEndCell.setCellValue(dateFormat.format(this.studentClassDiscipline.getStartDate()) + " - " + dateFormat.format(this.studentClassDiscipline.getEndDate().getTime())); NDaysCell.setCellValue(this.nDays); }
From source file:sistemas.Utils.java
public static void writeXLSXFile(JTable table, String path) throws IOException { TableModel model = table.getModel(); //Table model String excelFileName = path;//name of excel file String sheetName = "Sheet1";//name of sheet XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(sheetName); XSSFRow rowH = sheet.createRow(0);//w w w .j a v a 2s . c om for (int headings = 0; headings < model.getColumnCount(); headings++) { //For each column rowH.createCell(headings).setCellValue(model.getColumnName(headings));//Write column name } //iterating r number of rows for (int r = 1; r <= model.getRowCount(); r++) { XSSFRow row = sheet.createRow(r); //iterating c number of columns for (int c = 0; c < model.getColumnCount(); c++) { XSSFCell cell = row.createCell(c); cell.setCellValue("" + model.getValueAt(r - 1, c)); } } FileOutputStream fileOut = new FileOutputStream(excelFileName); //write this workbook to an Outputstream. wb.write(fileOut); fileOut.flush(); fileOut.close(); }
From source file:si_piket_smkn3.frm_tampil_piket.java
private void exportexcel(String alamat) { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet();//from w w w . ja v a2s . c o m //load data dari tabel ke treemap TreeMap<Integer, Object[]> data = new TreeMap<>(); data.put(0, new Object[] { tableModel.getColumnName(0), tableModel.getColumnName(1), tableModel.getColumnName(2), tableModel.getColumnName(3), tableModel.getColumnName(4), tableModel.getColumnName(5), tableModel.getColumnName(6), tableModel.getColumnName(7), tableModel.getColumnName(8) }); for (int i = 1; i < tableModel.getRowCount() + 1; i++) { data.put(i, new Object[] { getCellValue(i - 1, 0), getCellValue(i - 1, 1), getCellValue(i - 1, 2), getCellValue(i - 1, 3), getCellValue(i - 1, 4), getCellValue(i - 1, 5), getCellValue(i - 1, 6), getCellValue(i - 1, 7), getCellValue(i - 1, 8), }); } //menulis ke kertas Set<Integer> ids = data.keySet(); XSSFRow row; int rowID = 0; for (Integer key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object o : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(o.toString()); } } //write excel to file system try { FileOutputStream fos = new FileOutputStream(new File(alamat)); wb.write(fos); fos.close(); } catch (Exception ex) { System.err.println(ex.getMessage()); JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", JOptionPane.INFORMATION_MESSAGE); } }
From source file:sv.com.mined.sieni.controller.GestionNotasController.java
public StreamedContent getFilePlantilla() { filePlantilla = null;/* w w w.ja v a2 s . c om*/ 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:sv.com.mined.sieni.controller.GestionNotasController.java
public static void copyRow(XSSFSheet worksheetSource, XSSFSheet worksheetDestination, int sourceRowNum, int destinationRowNum) { // Get the source / new row XSSFRow origen = worksheetSource.getRow(sourceRowNum); XSSFRow destino = worksheetDestination.createRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < origen.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = origen.getCell(i); XSSFCell newCell = destino.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;//w ww. ja v a 2 s.co m continue; } //Ajustar tamaos columnas worksheetDestination.setColumnWidth(i, worksheetSource.getColumnWidth(i)); // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } }