在项目开发过程中，我们经常都会遇到对excel文件的相关操作，本文主要讲解的是对多个excel文件进行合并的操作。
在讲解之前，有必要说明这两种工具的使用场景和区别。jxl目前仅支持后缀格式为xls的excel文件，像Excel2007版本后缀为xlsx的文件暂时还不支持。poi目前可以支持excel后缀格式为xls与xlsx的文件。其他的区别有兴趣可以自己去网络上搜索下。
一. 使用jxl工具合并多个后缀为xls的excel文件，直接贴上代码。
1. package com.bacs.buz.util;
2. import java.io.File;
3. import java.io.IOException;
4. import java.util.ArrayList;
5. import java.util.List;
6. import com.xingyi.bacs.util.LogUtil;
7. import jxl.Cell;
8. import jxl.Sheet;
9. import jxl.Workbook;
10. import jxl.write.Label;
11. import jxl.write.WritableCellFormat;
12. import jxl.write.WritableFont;
13. import jxl.write.WritableSheet;
14. import jxl.write.WritableWorkbook;
15. import jxl.write.WriteException;
16. import jxl.write.biff.RowsExceededException;
17. public class JXLMergerWriteExcelUtil {
18.
19. private WritableCellFormat times;
20. private String createFilePath;//合成文件存放路径
21. private int beginMergerColumnIndex=0;//开始合并的列
22. private int endMerGerColumnIndex;//结束的合并的列
23. //private long headRowIndex;//列头所在位置
24. private int beginMergerRow=1;//开始合并的行标
25. private List<String> columns=null;
26. private String dirPath;
27. private int mergerRowBeginIndex=0;
28. public JXLMergerWriteExcelUtil(String createFilePath,String path){
29. this.createFilePath = createFilePath;
30. this.dirPath=path;
31. }
32. public JXLMergerWriteExcelUtil(String createFilePath,String path,int beginMergerRow){
33. this.createFilePath = createFilePath;
34. this.dirPath=path;
35. this.beginMergerRow=beginMergerRow;
36. }
37. public JXLMergerWriteExcelUtil(String createFilePath,String path,int beginMergerColumnIndex,int endMerGerColumnIndex,int beginMergerRow){
38. this.createFilePath = createFilePath;
39. this.dirPath=path;
40. this.beginMergerColumnIndex=beginMergerColumnIndex;
41. this.endMerGerColumnIndex=endMerGerColumnIndex;
42. this.beginMergerRow=beginMergerRow;
43. }
44.
45.
46. /*******
47. * 写文件
48. * @throws IOException
49. * @throws WriteException
50. * @throws Exception
51. */
52. public String mergerExcel() {
53. LogUtil.info(JXLMergerWriteExcelUtil.class, "开始合并文件");
54. WritableWorkbook workbook=null;
55. Workbook book=null;
56. try{
57. if(createFilePath==null){
58. return "请输入创建文件路径";
59. }
60. if(dirPath==null){
61. return "请输入被合并文件夹的路径";
62. }
63. File file = new File(createFilePath);
64. LogUtil.info(JXLMergerWriteExcelUtil.class, "主文件名路径为："+createFilePath);
65. book=Workbook.getWorkbook(file);
66. workbook=Workbook.createWorkbook(file, book);
67. WritableSheet excelSheet = workbook.getSheet(0);
68. this.beginMergerColumn(excelSheet);
69. workbook.write();
70. LogUtil.info(JXLMergerWriteExcelUtil.class, "结束合并文件");
71. return "0";
72. }catch(Exception e){
73. e.printStackTrace();
74. return "1";
75. }finally{
76. if(workbook!=null){
77. try {
78. workbook.close();
79. } catch (WriteException e) {
80. e.printStackTrace();
81. } catch (IOException e) {
82. e.printStackTrace();
83. }
84. }
85. if(book!=null){
86. book.close();
87. }
88. }
89. }
90.
91. /*****
92. * 开始合并文件
93. * @param excelSheet
94. * @param dirPath
95. */
96. private void beginMergerColumn(WritableSheet excelSheet ){
97. File srcFile = new File(dirPath);
98. LogUtil.info(JXLMergerWriteExcelUtil.class, "需要合并的文件名路径为："+dirPath);
99. String path= srcFile.getAbsolutePath();
100. LogUtil.info(JXLMergerWriteExcelUtil.class, "====合并excel文件----请稍等====");
101. try {
102. this.readSingleExcel(excelSheet,path);
103. } catch (Exception e) {
104. e.printStackTrace();
105. }
106. }
107.
108. /*****
109. * 读取单个文件
110. * @param excelSheet 主excel
111. * @param singFilePath 需要合并的文件路径
112. */
113. public void readSingleExcel(WritableSheet excelSheet,String singFilePath){
114. File inputWorkbook = new File(singFilePath);
115. Workbook w = null;
116. try {
117. w = Workbook.getWorkbook(inputWorkbook);
118. Sheet sheet = w.getSheet(0);
119. mergerRowBeginIndex=excelSheet.getRows();
120. LogUtil.info(JXLMergerWriteExcelUtil.class, "主文件名开始合并开始值为："+mergerRowBeginIndex);
121. endMerGerColumnIndex=excelSheet.getColumns();
122. LogUtil.info(JXLMergerWriteExcelUtil.class, "需要合并的数量为："+(sheet.getRows()-1));
123. for (int i = beginMergerRow; i < sheet.getRows(); i++) {
124. for (int j = beginMergerColumnIndex; j < endMerGerColumnIndex; j++) {
125. Cell cell = sheet.getCell(j, i);
126. String cell_value=cell.getContents();
127. /*if(j==0){
128. if(cell_value!=""){
129. cell_value=String.valueOf(Integer.parseInt(cell_value)+mergerRowBeginIndex);
130. }
131. }*/
132. this.addLabel(excelSheet, j, mergerRowBeginIndex, cell_value);
133. }
134. mergerRowBeginIndex++;
135. }
136. } catch (Exception e) {
137. e.printStackTrace();
138. }finally{
139. if(w!=null){
140. w.close();
141. }
142. }
143. }
144. /*****
145. * 创建表头
146. * @param excelSheet
147. * @param firstFilePath
148. */
149. private void createHeader(WritableSheet excelSheet,String firstFilePath){
150. int column=0;
151. try{
152. this.readFirstFileGetHeaders(firstFilePath);
153. for(int i=beginMergerColumnIndex;i<endMerGerColumnIndex;i++){
154. this.addLabel(excelSheet, column++, 0, columns.get(i));
155. }
156. this.addLabel(excelSheet, excelSheet.getColumns()-1, 0, "来源文件名称");
157. }catch(Exception e){
158. e.printStackTrace();
159. }
160. }
161. /*****
162. * 读取单个文件获取文件的表头信息
163. * @param filePath
164. */
165. private void readFirstFileGetHeaders(String filePath){
166. File inputWorkbook = new File(filePath);
167. Workbook w = null;
168. try {
169. w = Workbook.getWorkbook(inputWorkbook);
170. // Get the first sheet
171. Sheet sheet = w.getSheet(0);
172. // Loop over first 10 column and lines
173. columns=new ArrayList<String>();
174. for (int i = 0; i < sheet.getRows(); i++) {
175. for (int j = 0; j < sheet.getColumns(); j++) {
176. Cell cell = sheet.getCell(j, i);
177. String cell_value=cell.getContents();
178. columns.add(cell_value);
179. }
180.
181. endMerGerColumnIndex=sheet.getColumns();
182. break;
183. }
184. } catch (Exception e) {
185. // TODO Auto-generated catch block
186. e.printStackTrace();
187. }finally{
188. if(w!=null){
189. w.close();
190. }
191. }
192. }
193. /*****
194. * 添加信息到excel中
195. * @param sheet
196. * @param column
197. * @param row
198. * @param s
199. * @throws WriteException
200. * @throws RowsExceededException
201. */
202. private void addLabel(WritableSheet sheet, int column, int row, String s)
203. throws WriteException, RowsExceededException {
204. Label label;
205. // Lets create a times font
206. WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
207. // Define the cell format
208. times = new WritableCellFormat(times10pt);
209. label = new Label(column, row, s, times);
210. sheet.addCell(label);
211. }
212. public static void main(String[] args) throws Exception {
213. // for(int i=17;i<29;i++){
214. // JXLWriteExcel mergerExcel = new JXLWriteExcel("d:/合并文件/merger_"+i+".xls","D:/scopus20111124/"+i,2);
215. // String inf=mergerExcel.mergerExcel();
216. // System.out.println("第"+i+"批次文件"+inf);
217. // }
218. File source=new File("D://cs//MPOSJLTF_T1_20170103_01.xls");
219. File srcFile=new File("D://cs//MPOSJLTF_T1_20170103.xls");
220.
221. JXLMergerWriteExcelUtil mergerExcel = new JXLMergerWriteExcelUtil("D://cs//MPOSJLTF_T1_20170103.xls","D://cs//MPOSJLTF_T1_20170103_02.xls",1);
222. String inf=mergerExcel.mergerExcel();
223. // mergerExcel.fileChannelCopy(source, srcFile);
224. }
225. }二. 使用POI工具合并多个后缀为xls与xlsx的excel文件，直接贴上代码。
1. package com.bacs.buz.util;
2. import java.io.File;
3. import java.io.FileInputStream;
4. import java.io.FileNotFoundException;
5. import java.io.FileOutputStream;
6. import java.io.IOException;
7. import java.util.ArrayList;
8. import java.util.List;
9. import org.apache.poi.hssf.usermodel.HSSFFont;
10. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
11. import org.apache.poi.hssf.util.HSSFColor;
12. import org.apache.poi.ss.usermodel.Cell;
13. import org.apache.poi.ss.usermodel.CellStyle;
14. import org.apache.poi.ss.usermodel.Font;
15. import org.apache.poi.ss.usermodel.Row;
16. import org.apache.poi.ss.usermodel.Sheet;
17. import org.apache.poi.ss.usermodel.Workbook;
18. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
19. import com.xingyi.bacs.util.LogUtil;
20. /**
21. * POI合并excel工具类
22. * @author hhb
23. *
24. */
25. public class POIMergeExcelUtil {
26.
27. private List<String> columns=null;//列名集合
28.
29. /**
30. * 创建xls或者xlsx文件
31. * @param filePath
32. * @param sourceSheet 第一份文件的sheet
33. * @throws Exception
34. */
35. public void createExcelFile(String filePath,Sheet sourceSheet) throws Exception{
36. LogUtil.info(POIMergeExcelUtil.class, "开始创建主文件，文件路径为："+filePath);
37. Workbook workbook = null;
38. File file=new File(filePath);
39. String status=filePath.substring(filePath.lastIndexOf("."));
40. //HSSFWorkbook针对xls
41. if(status.equals(".xls")){
42. workbook = new HSSFWorkbook();
43. }
44. //XSSFWorkbook针对xlsx
45. if(status.equals(".xlsx")){
46. workbook = new XSSFWorkbook();
47. }
48. //创建一个工作簿
49. Sheet sheet=workbook.createSheet();
50. //创建列头
51. Row firstRow=sheet.createRow(0);
52. for(int i=0;i<columns.size();i++){
53. Cell cell=firstRow.createCell(i, Cell.CELL_TYPE_STRING);
54. cell.setCellStyle(getStyle(workbook));
55. cell.setCellValue(columns.get(i));
56. }
57. LogUtil.info(POIMergeExcelUtil.class, "正在将第一份文件内容合并到主文件中……");
58. //将第一份目标文件内容填充到新的文件中
59. for(int i=1;i<sourceSheet.getLastRowNum()+1;i++){
60. Row createRow=sheet.createRow(i);
61. //获取第一份文件的一行
62. Row sourceRow=sourceSheet.getRow(i);
63. for(int j=0;j<columns.size();j++){
64. Cell cell=sourceRow.getCell(j);
65. String cellValue=getCellValue(cell);
66. Cell createCell=createRow.createCell(j);
67. createCell.setCellValue(cellValue);
68. }
69. }
70. FileOutputStream fos=new FileOutputStream(file);
71. workbook.write(fos);
72. fos.flush();
73. fos.close();
74. LogUtil.info(POIMergeExcelUtil.class, "主文件创建成功，文件路径为："+file.getAbsolutePath());
75. }
76.
77. /**
78. * 获取单元格里面的值
79. * @param cell
80. * @return
81. */
82. public String getCellValue(Cell cell){
83. String cellValue = "";
84. if(cell == null){
85. return cellValue;
86. }
87. //把数字当成String来读，避免出现1读成1.0的情况
88. if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
89. cell.setCellType(Cell.CELL_TYPE_STRING);
90. }
91. //判断数据的类型
92. switch (cell.getCellType()){
93. case Cell.CELL_TYPE_NUMERIC: //数字
94. cellValue = String.valueOf(cell.getNumericCellValue());
95. break;
96. case Cell.CELL_TYPE_STRING: //字符串
97. cellValue = String.valueOf(cell.getStringCellValue());
98. break;
99. case Cell.CELL_TYPE_BOOLEAN: //Boolean
100. cellValue = String.valueOf(cell.getBooleanCellValue());
101. break;
102. case Cell.CELL_TYPE_FORMULA: //公式
103. cellValue = String.valueOf(cell.getCellFormula());
104. break;
105. case Cell.CELL_TYPE_BLANK: //空值
106. cellValue = "";
107. break;
108. case Cell.CELL_TYPE_ERROR: //故障
109. cellValue = "非法字符";
110. break;
111. default:
112. cellValue = "未知类型";
113. break;
114. }
115. return cellValue;
116. }
117.
118. /**
119. * 表格格式
120. * @param workbook
121. * @return
122. */
123. private CellStyle getStyle(Workbook workbook){
124. CellStyle style = workbook.createCellStyle();
125. //style.setAlignment(CellStyle.ALIGN_CENTER);
126. // style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
127. // 设置单元格字体
128. Font headerFont = workbook.createFont(); // 字体
129. headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
130. headerFont.setColor(HSSFColor.BLACK.index);
131. //headerFont.setFontName("宋体");
132. style.setFont(headerFont);
133. //tyle.setWrapText(true);*/
134.
135. return style;
136. }
137. /**
138. * 合并Excel
139. * @param srcPath 目标文件路径
140. * @param sourcePath 需要合并的文件路径
141. * @throws IOException
142. * @throws FileNotFoundException
143. */
144. public void mergeExcel(String srcPath,String sourcePath) throws Exception{
145. File inputWorkbook = new File(srcPath);
146. Workbook w = null;
147. String status=srcPath.substring(srcPath.lastIndexOf("."));
148. //HSSFWorkbook针对xls
149. if(status.equals(".xls")){
150. w = new HSSFWorkbook(new FileInputStream(inputWorkbook));
151. }
152. //XSSFWorkbook针对xlsx
153. if(status.equals(".xlsx")){
154. w = new XSSFWorkbook(new FileInputStream(inputWorkbook));
155. }
156. Sheet sheet = w.getSheetAt(0);
157. this.beginMergerColumn(sheet, sourcePath);
158. FileOutputStream fos=new FileOutputStream(inputWorkbook);
159. w.write(fos);
160. fos.flush();
161. fos.close();
162. LogUtil.info(POIMergeExcelUtil.class, "======Excel文件合并成功=====");
163. }
164.
165. /*****
166. * 开始合并文件
167. * @param excelSheet
168. * @param dirPath
169. * @throws IOException
170. * @throws FileNotFoundException
171. */
172. private void beginMergerColumn(Sheet excelSheet,String dirPath ) throws Exception{
173. File inputWorkbook = new File(dirPath);
174. LogUtil.info(POIMergeExcelUtil.class, "开始合并Excel文件："+dirPath);
175. Workbook w = null;
176. String status=dirPath.substring(dirPath.lastIndexOf("."));
177. //HSSFWorkbook针对xls
178. if(status.equals(".xls")){
179. w = new HSSFWorkbook(new FileInputStream(inputWorkbook));
180. }
181. //XSSFWorkbook针对xlsx
182. if(status.equals(".xlsx")){
183. w = new XSSFWorkbook(new FileInputStream(inputWorkbook));
184. }
185. //需要合并文件的sheet
186. Sheet sheet = w.getSheetAt(0);
187. //主文件从第几行开始追加内容
188. int mergerRowBeginIndex=excelSheet.getLastRowNum()+1;
189. LogUtil.info(POIMergeExcelUtil.class, "主文件开始合并的行号值为："+mergerRowBeginIndex);
190. LogUtil.info(POIMergeExcelUtil.class, "需要合并的数量为："+(sheet.getLastRowNum()));
191. for(int i=1;i<sheet.getLastRowNum()+1;i++){
192. Row createRow=excelSheet.createRow(mergerRowBeginIndex);
193. //获取第一份文件的一行
194. Row sourceRow=sheet.getRow(i);
195. for(int j=0;j<columns.size();j++){
196. Cell cell=sourceRow.getCell(j);
197. String cellValue=getCellValue(cell);
198. Cell createCell=createRow.createCell(j);
199. createCell.setCellValue(cellValue);
200. }
201. mergerRowBeginIndex++;
202. }
203. }
204.
205. /**
206. * 获取列头名称,返回第一份文件的sheet
207. * @param filePath
208. * @throws IOException
209. * @throws FileNotFoundException
210. */
211. public Sheet readFirstFileGetHeaders(String filePath) throws Exception{
212. LogUtil.info(POIMergeExcelUtil.class, "开始获取目标文件的列头,文件路径为："+filePath);
213. File inputWorkbook = new File(filePath);
214. Workbook w = null;
215. Sheet sheet =null;
216. String status=filePath.substring(filePath.lastIndexOf("."));
217. //HSSFWorkbook针对xls
218. if(status.equals(".xls")){
219. w = new HSSFWorkbook(new FileInputStream(inputWorkbook));
220. }
221. //XSSFWorkbook针对xlsx
222. if(status.equals(".xlsx")){
223. w = new XSSFWorkbook(new FileInputStream(inputWorkbook));
224. }
225. sheet = w.getSheetAt(0);
226. columns=new ArrayList<String>();
227. Row row=sheet.getRow(0);
228. for(int i=0;i<row.getPhysicalNumberOfCells();i++){
229. columns.add(row.getCell(i).toString());
230. }
231. LogUtil.info(POIMergeExcelUtil.class, "结束获取目标文件的列头，列头信息为："+columns.toString());
232. return sheet;
233. }
234.
235. public static void main(String[] args) throws IOException {
236. POIMergeExcelUtil u=new POIMergeExcelUtil();
237. String srcPath="D://cs//test.xlsx";
238. try {
239. Sheet sheet=u.readFirstFileGetHeaders("D://cs//MPOSJLTF_T1_20170103_01.xls");
240. u.createExcelFile(srcPath,sheet);
241. u.mergeExcel(srcPath, "D://cs//需要合并的文件.xls");
242. } catch (Exception e) {
243. e.printStackTrace();
244. File file=new File(srcPath);
245. if(file.exists()){
246. file.delete();
247. }
248. System.out.println("异常，文件删除");
249. }
250.
251. /* //File file = new File("D://cs//MPOSJLTF_T1_20170104.xlsx");
252. File file = new File("D://cs//test.xlsx");
253. ReadExcel reader = null;
254. //读EXCEL的第一页
255. try {
256. reader = new ReadExcel("D://cs//test.xlsx");
257. reader.processByRow(1);
258. reader.stop();
259. } catch (Exception e) {
260. e.printStackTrace();
261. }
262. //获取读取到的行对象集合
263. List<XRow> xRows = reader.getListXRow();
264. System.out.println(xRows.size());*/
265. }
266. }
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
