Java读写Excel实例分享 - 快网idc优惠网

话不多说，请看代码：

ExcelUtil.java

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

package pers.kangxu.datautils.utils;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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

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

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

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

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

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

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

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

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

/**

*

* <b>

* excel 工具

* </b>

* @author kangxu

*

*/

public class ExcelUtil {

/**

* 导出 excel

* @param filePath 文件全路径

* @param sheetName sheet页名称

* @param sheetIndex 当前sheet下表从0开始

* @param fileHeader 头部

* @param datas 内容

*/

public static void writeExcel(String filePath,String sheetName,

int sheetIndex,

String[] fileHeader,

List<String[]> datas){

// 创建工作簿

Workbook wb = new HSSFWorkbook();

// 创建工作表 sheet

Sheet s = wb.createSheet();

wb.setSheetName(sheetIndex, sheetName);

Row r = s.createRow(0);

Cell c = null;

Font font = null;

CellStyle styleHeader = null;

CellStyle styleContent = null;

//粗体

font = wb.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// 设置头样式

styleHeader = wb.createCellStyle();

styleHeader.setFont(font);

styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

// 设置内容样式

styleContent = wb.createCellStyle();

styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框

styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框

styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

//设置头

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

c = r.createCell(i);

c.setCellStyle(styleHeader);

c.setCellValue(fileHeader[i]);

i++;

}

//设置内容

for(int rownum=0;rownum<datas.size();){ // 行 row datas.size()

r = s.createRow(rownum+1); //创建行

for(int cellnum=0;cellnum<fileHeader.length;){

c = r.createCell(cellnum);

c.setCellValue(datas.get(rownum)[cellnum]);

c.setCellStyle(styleContent);

cellnum++;

}

rownum++;

}

FileOutputStream out = null;

try {

// 创建文件或者文件夹,将内容写进去

if(FileUtil.createFile(new File(filePath))){

out = new FileOutputStream(filePath);

wb.write(out);

}

} catch (Exception e) {

e.printStackTrace();

}finally {

try {

// 关闭流

if(out != null){

out.flush();

out.close();

}

} catch (IOException e) {

e.printStackTrace();

}

/**

* 读取 excel 文件内容

* @param filePath

* @param sheetIndex

*/

public static List<Map<String,String>> readExcel(String filePath,int sheetIndex){

List<Map<String,String>> mapList = new ArrayList<Map<String,String>>();

// 头

List<String> list = new ArrayList<String>();

//

int cnt = 0;

int idx = 0;

try {

InputStream input = new FileInputStream(filePath); //建立输入流

Workbook wb = null;

wb = new HSSFWorkbook(input);

// 获取sheet页

Sheet sheet = wb.getSheetAt(sheetIndex);

Iterator<Row> rows = sheet.rowIterator();

while (rows.hasNext()) {

Row row = rows.next();

Iterator<Cell> cells = row.cellIterator();

Map<String,String> map = new HashMap<String,String>();

if(cnt == 0){ // 将头放进list中

while (cells.hasNext()) {

Cell cell = cells.next();

if(isContainMergeCell(sheet)){

cancelMergeCell(sheet);

}

list.add(getStringCellValue(cell));

}

cnt ++;

continue;

}else {

while (cells.hasNext()) {

Cell cell = cells.next();

if(isContainMergeCell(sheet)){

cancelMergeCell(sheet);

}

// 区别相同的头

list = ListUtil.changeSameVal(list);

map.put(list.get(idx++), getStringCellValue(cell));

}

idx = 0;

mapList.add(map);

}

return mapList;

} catch (IOException ex) {

ex.printStackTrace();

}

return null;

}

/**

* 合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

*/

public static int mergeCell(Sheet sheet,int firstRow,int lastRow,int firstCol,int lastCol){

if(sheet == null){

return -1;

}

return sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

}

/**

* 取消合并单元格

* @param sheet

* @param idx

*/

public static void cancelMergeCell(Sheet sheet){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int idx = 0; idx < sheetMergeCount;){

CellRangeAddress range = sheet.getMergedRegion(idx);

String val = getMergeCellValue(sheet,range.getFirstRow(),range.getLastRow());

// 取消合并单元格

sheet.removeMergedRegion(idx);

for(int rownum=range.getFirstRow();rownum<range.getLastRow()+1;){

for(int cellnum=range.getFirstColumn();cellnum<range.getLastColumn()+1;){

sheet.getRow(rownum).getCell(cellnum).setCellValue(val);

cellnum ++;

}

rownum ++;

}

idx++;

}

/**

* 判断指定单元格是否是合并单元格

* @param sheet 当前sheet页

* @param firstRow 开始行

* @param lastRow 结束行

* @param firstCol 开始列

* @param lastCol 结束列

* @return

*/

public static boolean isMergeCell(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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 true;

}

i++;

}

return false;

}

/**

* 判断sheet页中是否含有合并单元格

* @param sheet

* @return

*/

public static boolean isContainMergeCell(Sheet sheet){

if(sheet == null){

return false;

}

return sheet.getNumMergedRegions()>0 ? true : false;

}

/**

* 获取指定合并单元的值

* @param sheet

* @param row

* @param column

* @return

*/

public static String getMergeCellValue(Sheet sheet,

int row ,int column){

int sheetMergeCount = sheet.getNumMergedRegions();

for(int i = 0; i < sheetMergeCount;){

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){

Row fRow = sheet.getRow(firstRow);

Cell fCell = fRow.getCell(firstColumn);

return getStringCellValue(fCell) ;

}

i++;

}

return null;

}

/**

* 获取单元格的值

* @param cell

* @return

*/

public static String getStringCellValue(Cell cell) {

String strCell = "";

if(cell==null) return strCell;

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

strCell = cell.getRichStringCellValue().getString().trim();

break;

case Cell.CELL_TYPE_NUMERIC:

strCell = String.valueOf(cell.getNumericCellValue());

break;

case Cell.CELL_TYPE_BOOLEAN:

strCell = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();

evaluator.evaluateFormulaCell(cell);

CellValue cellValue = evaluator.evaluate(cell);

strCell = String.valueOf(cellValue.getNumberValue()) ;

break;

default:

strCell = "";

}

return strCell;

}

调用方式如下

ExcelUtilTester.java

相关文章

微信

快网idc优惠网

QQ交流群