基于apache poi根据模板导出excel的实现方法

需要预先新建编辑好一个excel文件，设置好样式。

编辑好输出的数据，根据excel坐标一一对应。

支持列表数据输出，列表中列合并。

代码如下：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

package com.icourt.util;

import org.apache.commons.collections4.CollectionUtils;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddress;

import java.io.*;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Map.Entry;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

/**

* 描述:poi根据模板导出excel,根据excel坐标赋值,如(B1)

*/

public class ExcelExportUtil {

//模板map

private Map<String, Workbook> tempWorkbook = new HashMap<String, Workbook>();

//模板输入流map

private Map<String, InputStream> tempStream = new HashMap<String, InputStream>();

/**

* 功能:按模板向Excel中相应地方填充数据

*/

public void writeData(String templateFilePath, Map<String, Object> dataMap, int sheetNo) throws IOException, InvalidFormatException {

if (dataMap == null || dataMap.isEmpty()) {

return;

}

//读取模板

Workbook wbModule = getTempWorkbook(templateFilePath);

//数据填充的sheet

Sheet wsheet = wbModule.getSheetAt(sheetNo);

for (Entry<String, Object> entry : dataMap.entrySet()) {

String point = entry.getKey();

Object data = entry.getValue();

TempCell cell = getCell(point, data, wsheet);

//指定坐标赋值

setCell(cell, wsheet);

}

//设置生成excel中公式自动计算

wsheet.setForceFormulaRecalculation(true);

}

/**

* 功能:按模板向Excel中列表填充数据.只支持列合并

*/

public void writeDateList(String templateFilePath, String[] heads, List<Map<Integer, Object>> datalist, int sheetNo) throws IOException, InvalidFormatException {

if (heads == null || heads.length <= 0 || CollectionUtils.isEmpty(datalist)) {

return;

}

//读取模板

Workbook wbModule = getTempWorkbook(templateFilePath);

//数据填充的sheet

Sheet wsheet = wbModule.getSheetAt(sheetNo);

//列表数据模板cell

List<TempCell> tempCells = new ArrayList<TempCell>(heads.length);

for (String point : heads) {

TempCell tempCell = getCell(point, null, wsheet);

//取得合并单元格位置 -1:表示不是合并单元格

int pos = isMergedRegion(wsheet, tempCell.getRow(), tempCell.getColumn());

if (pos > -1) {

CellRangeAddress range = wsheet.getMergedRegion(pos);

tempCell.setColumnSize(range.getLastColumn() - range.getFirstColumn());

}

tempCells.add(tempCell);

}

//赋值

for (int i = 0; i < datalist.size(); i++) {//数据行

Map<Integer, Object> dataMap = datalist.get(i);

for (int j = 0; j < tempCells.size(); j++) {//列

TempCell tempCell = tempCells.get(j);

tempCell.setData(dataMap.get(j + 1));

setCell(tempCell, wsheet);

tempCell.setRow(tempCell.getRow() + 1);

}

/**

* 功能:获取输入工作区

*/

private Workbook getTempWorkbook(String templateFilePath) throws IOException, InvalidFormatException {

if (!tempWorkbook.containsKey(templateFilePath)) {

InputStream inputStream = getInputStream(templateFilePath);

tempWorkbook.put(templateFilePath, WorkbookFactory.create(inputStream));

}

return tempWorkbook.get(templateFilePath);

}

/**

* 功能:获得模板输入流

*/

private InputStream getInputStream(String templateFilePath) throws FileNotFoundException {

if (!tempStream.containsKey(templateFilePath)) {

tempStream.put(templateFilePath, new FileInputStream((templateFilePath)));

}

return tempStream.get(templateFilePath);

}

/**

* 功能:获取单元格数据,样式(根据坐标:B3)

*/

private TempCell getCell(String point, Object data, Sheet sheet) {

TempCell tempCell = new TempCell();

//得到列字母

String lineStr = "";

String reg = "[A-Z]+";

Pattern p = Pattern.compile(reg);

Matcher m = p.matcher(point);

while (m.find()) {

lineStr = m.group();

}

//将列字母转成列号根据ascii转换

char[] ch = lineStr.toCharArray();

int column = 0;

for (int i = 0; i < ch.length; i++) {

char c = ch[i];

int post = ch.length - i - 1;

int r = (int) Math.pow(10, post);

column = column + r * ((int) c - 65);

}

tempCell.setColumn(column);

//得到行号

reg = "[1-9]+";

p = Pattern.compile(reg);

m = p.matcher(point);

while (m.find()) {

tempCell.setRow((Integer.parseInt(m.group()) - 1));

}

//获取模板指定单元格样式,设置到tempCell(写列表数据的时候用)

Row rowIn = sheet.getRow(tempCell.getRow());

if (rowIn == null) {

rowIn = sheet.createRow(tempCell.getRow());

}

Cell cellIn = rowIn.getCell(tempCell.getColumn());

if (cellIn == null) {

cellIn = rowIn.createCell(tempCell.getColumn());

}

tempCell.setCellStyle(cellIn.getCellStyle());

tempCell.setData(data);

return tempCell;

}

/**

* 功能:给指定坐标单元格赋值

*/

private void setCell(TempCell tempCell, Sheet sheet) {

if (tempCell.getColumnSize() > -1) {

CellRangeAddress rangeAddress = mergeRegion(sheet, tempCell.getRow(), tempCell.getRow(), tempCell.getColumn(), tempCell.getColumn() + tempCell.getColumnSize());

setRegionStyle(tempCell.getCellStyle(), rangeAddress, sheet);

}

Row rowIn = sheet.getRow(tempCell.getRow());

if (rowIn == null) {

copyRows(tempCell.getRow() - 1, tempCell.getRow() - 1, tempCell.getRow(), sheet);//复制上一行

rowIn = sheet.getRow(tempCell.getRow());

}

Cell cellIn = rowIn.getCell(tempCell.getColumn());

if (cellIn == null) {

cellIn = rowIn.createCell(tempCell.getColumn());

}

//根据data类型给cell赋值

if (tempCell.getData() instanceof String) {

cellIn.setCellValue((String) tempCell.getData());

} else if (tempCell.getData() instanceof Integer) {

cellIn.setCellValue((int) tempCell.getData());

} else if (tempCell.getData() instanceof Double) {

cellIn.setCellValue((double) tempCell.getData());

} else {

cellIn.setCellValue((String) tempCell.getData());

}

//样式

if (tempCell.getCellStyle() != null && tempCell.getColumnSize() == -1) {

cellIn.setCellStyle(tempCell.getCellStyle());

}

/**

* 功能:写到输出流并移除资源

*/

public void writeAndClose(String templateFilePath, OutputStream os) throws IOException, InvalidFormatException {

if (getTempWorkbook(templateFilePath) != null) {

getTempWorkbook(templateFilePath).write(os);

tempWorkbook.remove(templateFilePath);

}

if (getInputStream(templateFilePath) != null) {

getInputStream(templateFilePath).close();

tempStream.remove(templateFilePath);

}

/**

* 功能:判断指定的单元格是否是合并单元格

*/

private Integer isMergedRegion(Sheet sheet, int row, int column) {

for (int i = 0; i < sheet.getNumMergedRegions(); i++) {

CellRangeAddress range = sheet.getMergedRegion(i);

int firstColumn = range.getFirstColumn();

int lastColumn = range.getLastColumn();

int firstRow = range.getFirstRow();

int lastRow = range.getLastRow();

if (row >= firstRow && row <= lastRow) {

if (column >= firstColumn && column <= lastColumn) {

return i;

}

return -1;

}

/**

* 功能:合并单元格

*/

private CellRangeAddress mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {

CellRangeAddress rang = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);

sheet.addMergedRegion(rang);

return rang;

}

/**

* 功能:设置合并单元格样式

*/

private void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet) {

for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {

Row row = sheet.getRow(i);

if (row == null) row = sheet.createRow(i);

for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {

Cell cell = row.getCell(j);

if (cell == null) {

cell = row.createCell(j);

cell.setCellValue("");

}

cell.setCellStyle(cs);

}

/**

* 功能:copy rows

*/

private void copyRows(int startRow, int endRow, int pPosition, Sheet sheet) {

int pStartRow = startRow - 1;

int pEndRow = endRow - 1;

int targetRowFrom;

int targetRowTo;

int columnCount;

CellRangeAddress region = null;

int i;

int j;

if (pStartRow == -1 || pEndRow == -1) {

return;

}

// 拷贝合并的单元格

for (i = 0; i < sheet.getNumMergedRegions(); i++) {

region = sheet.getMergedRegion(i);

if ((region.getFirstRow() >= pStartRow)

&& (region.getLastRow() <= pEndRow)) {

targetRowFrom = region.getFirstRow() - pStartRow + pPosition;

targetRowTo = region.getLastRow() - pStartRow + pPosition;

CellRangeAddress newRegion = region.copy();

newRegion.setFirstRow(targetRowFrom);

newRegion.setFirstColumn(region.getFirstColumn());

newRegion.setLastRow(targetRowTo);

newRegion.setLastColumn(region.getLastColumn());

sheet.addMergedRegion(newRegion);

}

// 设置列宽

for (i = pStartRow; i <= pEndRow; i++) {

Row sourceRow = sheet.getRow(i);

columnCount = sourceRow.getLastCellNum();

if (sourceRow != null) {

Row newRow = sheet.createRow(pPosition - pStartRow + i);

newRow.setHeight(sourceRow.getHeight());

for (j = 0; j < columnCount; j++) {

Cell templateCell = sourceRow.getCell(j);

if (templateCell != null) {

Cell newCell = newRow.createCell(j);

copyCell(templateCell, newCell);

}

/**

* 功能:copy cell,不copy值

*/

private void copyCell(Cell srcCell, Cell distCell) {

distCell.setCellStyle(srcCell.getCellStyle());

if (srcCell.getCellComment() != null) {

distCell.setCellComment(srcCell.getCellComment());

}

int srcCellType = srcCell.getCellType();

distCell.setCellType(srcCellType);

}

/**

* 描述:临时单元格数据

*/

class TempCell {

private int row;

private int column;

private CellStyle cellStyle;

private Object data;

//用于列表合并,表示几列合并

private int columnSize = -1;

public int getColumn() {

return column;

}

public void setColumn(int column) {

this.column = column;

}

public int getRow() {

return row;

}

public void setRow(int row) {

this.row = row;

}

public CellStyle getCellStyle() {

return cellStyle;

}

public void setCellStyle(CellStyle cellStyle) {

this.cellStyle = cellStyle;

}

public Object getData() {

return data;

}

public void setData(Object data) {

this.data = data;

}

public int getColumnSize() {

return columnSize;

}

public void setColumnSize(int columnSize) {

this.columnSize = columnSize;

}

public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {

String templateFilePath = ExcelExportUtil.class.getClassLoader().getResource("plugin/ProTiming.xlsx").getPath();

File file = new File("/Users/sql/Downloads/test/data.xlsx");

OutputStream os = new FileOutputStream(file);

ExcelExportUtil excel = new ExcelExportUtil();

Map<String, Object> dataMap = new HashMap<String, Object>();

dataMap.put("B1", "03_Alpha_项目工作时间统计表");

dataMap.put("B2", "统计时间：2017/01/01 - 2017/03/31");

excel.writeData(templateFilePath, dataMap, 0);

List<Map<Integer, Object>> datalist = new ArrayList<Map<Integer, Object>>();

Map<Integer, Object> data = new HashMap<Integer, Object>();

data.put(1, "3/10/17");

data.put(2, "18:50");

data.put(3, "19:00");

data.put(4, "李子鹏");

data.put(5, "新增项目键值对接口,供任务计时调用");

data.put(6, "代码开发");

data.put(7, "3.17");

datalist.add(data);

data = new HashMap<Integer, Object>();

data.put(1, "3/10/17");

data.put(2, "18:50");

data.put(3, "19:00");

data.put(4, "李子鹏");

data.put(5, "新增项目键值对接口,供任务计时调用");

data.put(6, "代码开发");

data.put(7, "3.17");

datalist.add(data);

data = new HashMap<Integer, Object>();

大体思路：

最主要是制作好模版

代码根据模版，读取设置好的列的格式，在循环数据行，读取模版中的对应的行，存在该行就取得，不存在看是否需要copy某一行，不需要就手动创建无制定格式的行，后面在为该行的每一列对应的给个单元格制定格式和数据。

以上这篇基于apache poi根据模板导出excel的实现方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持快网idc。

基于apache poi根据模板导出excel的实现方法

相关文章

快网idc优惠网

相关文章

微信

快网idc优惠网

QQ交流群