Spring

[JXLS] ์—‘์…€ ๋‹ค์šด๋กœ๋“œ

natrue 2021. 5. 6. 15:12
728x90

JXLS =  JXLS์€ ํ…œํ”Œ๋ฆฟ์„ ๊ธฐ๋ฐ˜์œผ๋กœ ์ตœ์ข… ์—‘์…€ํŒŒ์ผ์„ ์ƒ์„ฑ

๋ฐ์ดํ„ฐ (java ๊ฐ์ฒด) + ์—‘์…€ ํ…œํ”Œ๋ฆฟ = ์—‘์…€๊ฒฐ๊ณผ ์ถœ๋ ฅ

์—‘์…€์„ ๊ทธ๋Œ€๋กœ ์‚ฌ์šฉํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์—‘์…€์˜ ์„œ์‹, ์ฐจํŠธ, ์ˆ˜์‹๋“ฑ์„ ๋„ฃ์„ ์ˆ˜ ์žˆ๋‹ค.

pom.xml

<!-- Create Excel -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.13</version>
</dependency>

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.13</version>
</dependency>

<dependency> 
	<groupId>org.apache.poi</groupId> 
	<artifactId>poi-ooxml-schemas</artifactId> 
	<version>3.13</version> 
</dependency> 

<dependency> 
	<groupId>org.apache.poi</groupId> 
	<artifactId>poi-contrib</artifactId> 
	<version>3.7-beta3</version> 
</dependency> 

<!-- apache poi -->
<dependency> 
	<groupId>commons-beanutils</groupId> 
	<artifactId>commons-beanutils</artifactId> 
	<version>1.9.2</version> 
</dependency>

<dependency> 
	<groupId>commons-digester</groupId> 
	<artifactId>commons-digester</artifactId> 
	<version>2.0</version> 
</dependency>

<dependency> 
	<groupId>org.apache.commons</groupId> 
	<artifactId>commons-jexl</artifactId> 
	<version>2.1.1</version> 
</dependency>

<dependency> 
	<groupId>net.sf.jxls</groupId> 
	<artifactId>jxls-core</artifactId> 
	<version>1.0.5</version> 
</dependency>

 

ExcelView.java

import java.io.OutputStream;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.servlet.view.document.AbstractXlsxStreamingView;

import net.sf.jxls.transformer.XLSTransformer;

public class ExcelView extends AbstractXlsxStreamingView {

	private static final String sample = "/excel/sample.xlsx";// ํด๋ž˜์ŠคํŒจ์Šค์— ์žˆ๋Š” Resource ๊ฒฝ๋กœ 
	

	@Override
	protected void buildExcelDocument(final Map<String, Object> model, final Workbook workbook, final HttpServletRequest request,
			final HttpServletResponse response) throws Exception {

		OutputStream os = null;
		InputStream is = null;

		try {
			String fileName = "excelTest";
		
			is = new ClassPathResource(sample).getInputStream();
			
			response.setHeader("Content-Type", "application/octet-stream");
			response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");

			os = response.getOutputStream();

			XLSTransformer transformer = new XLSTransformer();

			Workbook excel = transformer.transformXLS(is, model);
			excel.write(os);
			os.flush();

		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			if (os != null) {
				try {
					os.close();
				} catch (IOException e) {
				}
			}
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
				}
			}
		}
	}
}

 

ExcelController.java (๋ฐ์ดํ„ฐ๋ฅผ ์ƒ์„ฑํ•˜์—ฌ ExcelView๋ฅผ ๋ฆฌํ„ด)

@Controller
public class ExcelController {
	@RequestMapping(value = "/api/excel")
	public View reportExcelDownload2(final HttpServletResponse response,
			@RequestParam(value = "reqData", required = false, defaultValue = "") final String reqData, final Model model) throws Exception {

		try {
			//๋ฐ์ดํ„ฐ ๋งŒ๋“ค๊ธฐ
			List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
			
			HashMap<String, Object> obj = null;
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");

			obj = new HashMap<String, Object>();
			obj.put("name", "๊ฐ€ํŠธ๋ฃจ");
			obj.put("age", "20");
			obj.put("address", "๋Œ€์ „");
			list.add(obj);
				
			obj = new HashMap<String, Object>();
			obj.put("name", "๋‚˜ํŠธ๋ฃจ");
			obj.put("age", "27");
			obj.put("address", "์„œ์šธ");
			list.add(obj);
				
			obj = new HashMap<String, Object>();
			obj.put("name", "๋‹คํŠธ๋ฃจ");
			obj.put("age", "50");
			obj.put("address", "๋ถ€์‚ฐ");
			list.add(obj);
				
			obj = new HashMap<String, Object>();
			obj.put("name", "๋ผํŠธ๋ฃจ");
			obj.put("age", "100");
			obj.put("address", "์ œ์ฃผ");
			list.add(obj);
			
			model.addAttribute("list", list);
			model.addAttribute("count", list.size());
			model.addAttribute("DownloadDate", sdf.format(new Date()));
	

		} catch (Exception e) {
		e.printStackTrace();
		}
		return new ExcelView();
	}
}

 

sample.xlsx ํŒŒ์ผ 

์…€์— ์น˜ํ™˜ํ•  ๋ฐ์ดํ„ฐ๋ฅผ ๋„ฃ๊ธฐ ์œ„ํ•ด์„œ๋Š” ${} ํ˜•์‹ ์‚ฌ์šฉ. (JSTL๊ณผ ๋งค์šฐ ํก์‚ฌ) 

<jx:forEach>ํƒœ๊ทธ๋‚ด์˜ ๋‚ด์šฉ์„ ์•„์ดํ…œ ๊ฐœ์ˆ˜ ๋งŒํผ ๋ฐ˜๋ณตํ•˜๋Š” ๋ฐฉ์‹. 

<jx:forEach items="${list}" var="info">
	${info.age}
</jx:forEach>

 

sample.xlsx

 

 class path Resource ๊ฒฝ๋กœ๋Œ€๋กœ ์œ„์—์„œ ์ƒ์„ฑํ•œ .xlsx ํŒŒ์ผ์„ ๋„ฃ์–ด์ค€๋‹ค.

 

๊ฒฐ๊ณผ ํ™”๋ฉด 

 

 

 

 

 

์ฐธ๊ณ  :

 

JXLS -

Getting Started Guide Let’s assume we have a Java collection of employee objects that we want to output into Excel. The Employee class may look like this public class Employee { private String name; private Date birthDate; private BigDecimal payment; pri

jxls.sourceforge.net

 

Java๋กœ ์—‘์…€ ๋‹ค์šด๋กœ๊ทธ ๊ตฌํ˜„ํ•˜๊ธฐ

ํ”„๋กœ์ ํŠธ๋ฅผ ํ•˜๋‹ค๋ณด๋ฉด ๋ฐ์ดํ„ฐ๋ฅผ ์—‘์…€๋กœ ์ถœ๋ ฅํ•ด ๋‹ฌ๋ผ๋Š” ์š”๊ตฌ๊ฐ€ ๋นˆ๋ฒˆํžˆ ๋ฐœ์ƒํ•ฉ๋‹ˆ๋‹ค. ๊ฐ„๋‹จํ•˜๊ฒŒ CSV ํŒŒ์ผ๋กœ ์ถœ๋ ฅํ•  ์ˆ˜๋„ ์žˆ์œผ๋ฉฐ Excel์ด HTML ํ˜•ํƒœ์˜ ๋ฌธ์„œ์–‘์‹๋„ ์ง€์›ํ•˜๊ธฐ์— HTML๋กœ ์ž‘์„ฑํ•˜๊ณ  mine type๋งŒ ์‚ด

greatkim91.tistory.com