[JXLS] JAVA SPRING µ¥ÀÌÅÍ ¿¢¼¿Ãâ·Â ¹× ¼¿º´ÇÕ, merge ÇÏ´Â ¹æ¹ý ¿¡·¯ ÇØ°á¹æ¹ý
[JXLS] JAVA SPRING µ¥ÀÌÅÍ ¿¢¼¿Ãâ·Â ¹× ¼¿º´ÇÕ, merge ÇÏ´Â ¹æ¹ý
À¥ÇÁ·ÎÁ§Æ®¸¦ °³¹ßÇÏ´Ùº¸¸é
Å×À̺íÀ̳ª ¾î¶² µ¥ÀÌÅ͵éÀ» ¿¢¼¿·Î ´Ù¿î¹Þ´Â ±â´ÉÀ» ¸¸µé¾î¾ß ÇÏ´Â °æ¿ì°¡ ÀÖ´Ù.
ÀÏ´Ü ¿¢¼¿·Î ¸¸µå´Â°Ô POI¸¸ ¾²¸é ÁøÂ¥ ´õ·´°Ô ±ÍÂú¾ÆÁø´Ù.
±×·¸´Ù°í Á¦ÀÌÄõ¸® excel export¸¦ ¾²ÀÚ´Ï
ÆäÀÌ¡ó¸®µÈ Å×À̺íÀÇ µ¥ÀÌÅ͵éÀ» »Ì±â°¡ ¾Ö¸ÅÇÏ°í
1¸¸·Î¿ìÂë µÇ¾úÀ»¶§ ±×°É ´Ù Å×ÀÌºí¿¡ append ½ÃÅ°±âµµ ¿À¹Ù°°´Ù
±×·¡¼ ã´Ùã´Ù ã¾Æ³½°ÍÀÌ
JXLS
ÀÏ´Ü jxls´Â poi ±â¹ÝÀ¸·Î ¸¸µé¾îÁ³´Ù.
»ç¿ë¹ýÀº
Á¤¸» °£´ÜÇÏ´Ù.
±×³É ¸¶À̹ÙƼ½º¿¡¼ dbµ¥ÀÌÅÍ »Ì¾Æ¼
ÆäÀÌÁö·Î ³¯·ÁÁÖ´Â ±× ¸ðµ¨
Çؽ¬¸Êµ¥ÀÌÅÍ Çü½ÄÀ¸·Î»ÌÀº°É ±×´ë·Î »ç¿ëÇÏ¸é µÈ´Ù
¿¢¼¿¿¡ À§Ä¡ ÁöÁ¤À̳ª ¹Ýº¹µÇ´Â°ÍÀº
¹Ì¸® ¿¢¼¿ ÅÛÇø´À» ¸¸µé¾î ³õÀ¸¸é
±×´ë·Î µé¾î°£´Ù.
ÀÏ´Ü ¼ÂÆà ¹æ¹ýºÎÅÍ ¾Ë¾Æº¸µµ·Ï ÇÏÀÚ.
ÀüÀÚÁ¤ºÎ ±âÁØÀÌ·Î ¼³¸í ÇÑ´Ù.
1. pom.xml
pom.xmlÀÇ dependency ºÎºÐ¿¡ ¾Æ·¡ Äڵ带 ³Ö¾î ÁÖµµ·Ï ÇÏÀÚ.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | <!-- ¿¢¼¿ ´Ù¿î·Îµå --> <dependency> <groupId>net.sf.jxls</groupId> <artifactId>jxls-core</artifactId> <version>1.0.6</version> </dependency> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-poi</artifactId> <version>1.0.13</version> </dependency> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls</artifactId> <version>2.4.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-jexcel</artifactId> <version>1.0.6</version> </dependency> | cs |
À§¿¡¼ ¸»Çßµí jxls´Â poi¸¦ »ç¿ëÇϱ⠶§¹®¿¡ poiµµ ¹Þ¾ÆÁÖ¾î¾ß ÇÑ´Ù.
2. ¿¢¼¿¸¸µé°í ´Ù¿î·Îµå ¹Þ´Â Ŭ·¡½º
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | package ÆÐÅ°Áö °æ·Î; import java.io.BufferedInputStream; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import net.sf.jxls.exception.ParsePropertyException; import net.sf.jxls.transformer.XLSTransformer; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Workbook; // MakeExcelÀ̶ó´Â Ŭ·¡½º¸¦ ¸¸µé°í ±× ¾È¿¡ downliad¶ó´Â ¸Þ¼Òµå¸¦ »ý¼ºÇÑ´Ù. public class MakeExcel { public void download(HttpServletRequest request, HttpServletResponse response, Map<String, Object> bean, String fileName, String templateFile, String string) throws ParsePropertyException, InvalidFormatException { // ¹Þ¾Æ¿À´Â ¸Å°³º¯¼ö bean´Â µðºñ¿¡¼ »Ì¾Æ¿Â µ¥ÀÌÅÍ // fileName Àº ´Ù¿î·Îµå ¹ÞÀ»¶§ ÁöÁ¤µÇ´Â ÆÄÀϸí // templateFile ´Â ÅÛÇø´ ¿¢¼¿ ÆÄÀϸíÀÌ´Ù. // tempPath´Â ÅÛÇø´ ¿¢¼¿ÆÄÀÏÀÌ µé¾î°¡´Â °æ·Î¸¦ ³Ö¾î ÁØ´Ù. String tempPath = request.getSession().getServletContext().getRealPath("/WEB-INF/excel"); // º°µµ·Î ´Ù¿î·Îµå ¸¸µé±â ±ÍÂúÀ¸±î ÀÌ·±½ÄÀ¸·Î ¸¸µé¾î¼ ¹Ù·Î ¿¢¼¿ »ý¼ºÈÄ ´Ù¿î ¹Þ°Ô try { InputStream is = new BufferedInputStream(new FileInputStream(tempPath + "\\" + templateFile)); XLSTransformer xls = new XLSTransformer(); Workbook workbook = xls.transformXLS(is, bean); response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + ".xlsx\""); OutputStream os = response.getOutputStream(); workbook.write(os); } catch (IOException e) { e.printStackTrace(); } } } | cs |
3. ¿¢¼¿ ´Ù¿î·Îµå ¿äû ¹× µ¥ÀÌÅÍ°¡Á®¿À´Â ¸Þ¼Òµå
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | @RequestMapping(value = "/downExcel.do") public void listExcel(HttpServletRequest request, HttpServletResponse response, VO vo, ModelMap modelMap) throws Exception, Exception { // ±×³É Æò¼Ò¿¡ ¸¶À̹ÙƼ½º¿¡¼ µ¥ÀÌÅÍ »Ì´Â ¹æ¹ýÀ¸·Î µ¥ÀÌÅ͸¦ °¡Á®¿Â´Ù. List<VO> dataList = trs01Service.selectGroupList(groupVO); // ¹ÞÀº µ¥ÀÌÅ͸¦ ¸Ê¿¡ ´ã´Â´Ù. Map<String, Object> beans = new HashMap<String, Object>(); beans.put("dataList", dataList); // ¿¢¼¿ ´Ù¿î·Îµå ¸Þ¼Òµå°¡ ´ã°Ü ÀÖ´Â °´Ã¼ MakeExcel me = new MakeExcel(); me.download(request, response, beans, "´Ù¿î¹ÞÀ»¶§ÁöÁ¤µÉ ¿¢¼¿ÆÄÀϸí", "¿¢¼¿ÅÛÇø´ ÆÄÀÏ ¸í.xlsx", "¹«½ÃÇصµµÊ"); } | cs |
ÀÌ·¸°Ô Çϸé ÀÚ¹Ù¿¡¼ ÇÒ °Ç ³¡³µ´Ù.
¸· ¿¹Àü¿¡ POI¿¡¼ ¼¿À§Ä¡ ÁöÁ¤ÇÏ°í Çß´ø°ÍµéÀº ÇÏÁö ¾Ê¾Æµµ µÈ´Ù!!
Àú ÅÛÇø´À§Ä¡¿¡ ¾Æ·¡ 4¹ø¿¡¼ ¸¸µç ÅÛÇø´ ¿¢¼¿ÆÄÀÏÀ» ³Ö¾î³õ°í
3¹ø¿¡¼ ¸¸µç ¸Þ¼Òµå¸¦ È£ÃâÇϸé ÇØ´ç µ¥ÀÌÅ͸¦ ´ãÀº ¿¢¼¿À» ´Ù¿î·Îµå ¹Þ°Ô µÈ´Ù.
Á¤¸» ³Ê¹«³Ê¹« °£´ÜÇÏ´Ù.
4. ÅÛÇø´ ¿¢¼¿ ¿¹Á¦
À§ ±×¸²°ú °°ÀÌ JSTL ¾²´ø°Í ó·³ Àû¾îÁÖ¸é µÈ´Ù.
Àú·¸°Ô ÀûÀ¸¸é ÄÝ·º¼ÇÀÏ °æ¿ì ÀÚµ¿À¸·Î ¹Ýº¹±îÁö ÇØÁØ´Ù.
±×³É Àú·¸°Ô¸¸ ¸¸µé¾î µÎ¸é Àú À§Ä¡ºÎÅÍ ¾Æ·¡·Î ÂßÂß ¿¢¼¿µ¥ÀÌÅÍ°¡ ÀÔ·Â µÇ´Â °ÍÀÌ´Ù.
¾öû³ª´Ù.
5. ¼¿º´ÇÕ, merge
ÀÌ·±½ÄÀ¸·Î ¼¿º´ÇÕÀ» ÇÏ°í À̸§¿·¿¡ 3ÁÙ·Î ´Ù¸¥ Á¤º¸¸¦ ¹Ýº¹½ÃÄÑ¾ß ÇÏ´Â °æ¿ì°¡ ÀÖ´Ù.
±×³É 4¹ø¿¡ Àִ½ÄÀ¸·Î ÅÛÇø´¸¸µé¾î¼ ÀûÀ¸¸é ¿¡·¯³´Ù.
java.lang.NullPointerException
at net.sf.jxls.util.Util.shiftColumnUp(Util.java:335) ~[jxls-core-1.0.6.jar:?]
at net.sf.jxls.util.Util.shiftUncoupledCellsUp(Util.java:315) ~[jxls-core-1.0.6.jar:?]
at net.sf.jxls.util.Util.duplicateRow(Util.java:246) ~[jxls-core-1.0.6.jar:?]
at net.sf.jxls.controller.SheetTransformationControllerImpl.duplicateRow(SheetTransformationControllerImpl.java:140) ~[jxls-core-1.0.6.jar:?]
at net.sf.jxls.transformer.CollectionRowTransformer.processRowCollections(CollectionRowTransformer.java:106) ~[jxls-core-1.0.6.jar:?]
at net.sf.jxls.transformer.CollectionRowTransformer.transform(CollectionRowTransformer.java:66) ~[jxls-core-1.0.6.jar:?]
ÀÌ·± ¿¡·¯°¡ ¸· »Õ¾îÁ® ³ª¿Ã °ÍÀÌ´Ù.
¿Ö ¿¡·¯°¡ ³ª´À³Ä..
ÅÛÇø´À» À߸ø ¸¸µé¾ú±â ¶§¹®ÀÌ´Ù.
ÀÌ°É º¸´Â ¿©·¯ºÐµéµµ ÅÛÇø´À¸·Î ÀÌ°ÍÀú°Í ¿¡·¯»Õ¾î°¡¸é¼ Å×½ºÆ® ÇÏ´Ùº¸¸é
¾Ë¼ö ÀÖÀ» ÀÌÀ¯ÀÌ´Ù
±×³É ÇØ°á¹æ¹ý¸¸ °£´ÜÇÏ°Ô ¸»Çϸé
ÀÌ·±½ÄÀ¸·Î ¼¿º´ÇÕÇÑ À§Ä¡¿¡ µé¾î°¡´Â µ¥ÀÌÅÍ ¿·¿¡ //:¼ýÀÚ ¸¦ ³Ö¾î ÁÖ¸é µÈ´Ù.
¸¸¾à µ¥ÀÌÅÍ°¡ 3ÄÀ» º´ÇÕÇؼ ¼¼ÁÙ´ÜÀ§·Î ³Ñ¾î°¡¾ß ÇÏ´Â °æ¿ì
0 , 1, 2 ·Î ¼¼¼ ¼ýÀÚ 2¸¦ ³Ö¾îÁÖ¸é µÈ´Ù.
¸ÓÁö¸¦ ÇÏÁö ¾Ê´õ¶óµµ
2ÁÙ´ÜÀ§·Î ¹Ýº¹Çϰųª 3ÁÙ´ÜÀ§·Î ¹Ýº¹ÇÏ°Ô ÇÏ´Â °æ¿ì¿¡µµ µ¿ÀÏÇÏ°Ô Àû¿ëÇÏ¸é µÈ´Ù.
JXLSÀÇ °¡Àå ÁÁÀº ÀåÁ¡Àº µ¥ÀÌÅÍ ³Ö´Â°÷ ÅÛÇø´¿¡ ½ºÅ¸ÀÏ ³Ö¾îÁÖ¸é ±× ½ºÅ¸Àϵµ °°ÀÌ ¹Ýº¹ÀÌ µÈ´Ù.
¾ÆÁÖ ÈǸ¢ÇÏ´Ù