/**
* @param srcSheet the sheet to copy.
* @param destSheet the sheet to create.
* @param srcRow the row to copy.
* @param destRow the row to create.
* @param styleMap -
*/
public static void copyRow(
XSSFSheet srcSheet,
XSSFSheet destSheet,
XSSFRow srcRow,
XSSFRow destRow,
Map<Integer, XSSFCellStyle> styleMap) {
Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
XSSFCell oldCell = srcRow.getCell(j);
XSSFCell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(oldCell, newCell, styleMap);
CellRangeAddress mergedRegion =
getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
if (mergedRegion != null) {
CellRangeAddress newMergedRegion =
new CellRangeAddress(
mergedRegion.getFirstRow(),
mergedRegion.getFirstColumn(),
mergedRegion.getLastRow(),
mergedRegion.getLastColumn());
CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
if (isNewMergedRegion(wrapper, mergedRegions)) {
mergedRegions.add(wrapper);
destSheet.addMergedRegion(wrapper.range);
}
}
}
}
}
// returns true if data is set successfully else false
public boolean setCellData(
String sheetName, String colName, int rowNum, String data, String url) {
// System.out.println("setCellData setCellData******************");
try {
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
if (rowNum <= 0) return false;
int index = workBook.getSheetIndex(sheetName);
int colNum = -1;
if (index == -1) return false;
sheet = workBook.getSheetAt(index);
// System.out.println("A");
row = sheet.getRow(0);
for (int i = 0; i < row.getLastCellNum(); i++) {
// System.out.println(row.getCell(i).getStringCellValue().trim());
if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i;
}
if (colNum == -1) return false;
sheet.autoSizeColumn(colNum); // ashish
row = sheet.getRow(rowNum - 1);
if (row == null) row = sheet.createRow(rowNum - 1);
cell = row.getCell(colNum);
if (cell == null) cell = row.createCell(colNum);
cell.setCellValue(data);
XSSFCreationHelper createHelper = workBook.getCreationHelper();
// cell style for hyperlinks
// by default hypelrinks are blue and underlined
CellStyle hlink_style = workBook.createCellStyle();
XSSFFont hlink_font = workBook.createFont();
hlink_font.setUnderline(XSSFFont.U_SINGLE);
hlink_font.setColor(IndexedColors.BLUE.getIndex());
hlink_style.setFont(hlink_font);
// hlink_style.setWrapText(true);
XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
link.setAddress(url);
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* @param newSheet the sheet to create from the copy.
* @param sheet the sheet to copy.
* @param copyStyle true copy the style.
*/
public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
int maxColumnNum = 0;
Map<Integer, XSSFCellStyle> styleMap =
(copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
XSSFRow srcRow = sheet.getRow(i);
XSSFRow destRow = newSheet.createRow(i);
if (srcRow != null) {
XSSFCopySheet.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
// returns number of columns in a sheet
public int getColumnCount(String sheetName) {
// check if sheet exists
if (!isSheetExist(sheetName)) return -1;
sheet = workBook.getSheet(sheetName);
row = sheet.getRow(0);
if (row == null) return -1;
return row.getLastCellNum();
}
// returns the data from a cell
public String getCellData(String sheetName, String colName, int rowNum) {
try {
if (rowNum <= 0) return "";
int index = workBook.getSheetIndex(sheetName);
int col_Num = -1;
if (index == -1) return "";
sheet = workBook.getSheetAt(index);
row = sheet.getRow(0);
for (int i = 0; i < row.getLastCellNum(); i++) {
// System.out.println(row.getCell(i).getStringCellValue().trim());
if (row.getCell(i).getStringCellValue().trim().equals(colName.trim())) col_Num = i;
}
if (col_Num == -1) return "";
sheet = workBook.getSheetAt(index);
row = sheet.getRow(rowNum - 1);
if (row == null) return "";
cell = row.getCell(col_Num);
if (cell == null) return "";
// System.out.println(cell.getCellType());
if (cell.getCellType() == Cell.CELL_TYPE_STRING) return cell.getStringCellValue();
else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
|| cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
String cellText = String.valueOf(cell.getNumericCellValue());
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// format in form of M/D/YY
double d = cell.getNumericCellValue();
Calendar cal = Calendar.getInstance();
cal.setTime(HSSFDateUtil.getJavaDate(d));
cellText = (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
cellText =
cal.get(Calendar.DAY_OF_MONTH) + "/" + cal.get(Calendar.MONTH) + 1 + "/" + cellText;
// System.out.println(cellText);
}
return cellText;
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return "";
else return String.valueOf(cell.getBooleanCellValue());
} catch (Exception e) {
e.printStackTrace();
return "row " + rowNum + " or column " + colName + " does not exist in xls";
}
}
// returns true if column is created successfully
public boolean addColumn(String sheetName, String colName) {
// System.out.println("**************addColumn*********************");
try {
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
int index = workBook.getSheetIndex(sheetName);
if (index == -1) return false;
XSSFCellStyle style = workBook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
sheet = workBook.getSheetAt(index);
row = sheet.getRow(0);
if (row == null) row = sheet.createRow(0);
// cell = row.getCell();
// if (cell == null)
// System.out.println(row.getLastCellNum());
if (row.getLastCellNum() == -1) cell = row.createCell(0);
else cell = row.createCell(row.getLastCellNum());
cell.setCellValue(colName);
cell.setCellStyle(style);
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
private int getColumnCountFromSheet() {
int lastRow = xssfSheet.getLastRowNum();
short lastCol = 0;
short lastCellInRow;
XSSFRow row = null;
for (int i = 0; i < lastRow; i++) {
row = xssfSheet.getRow(i);
lastCellInRow = row.getLastCellNum();
if (lastCellInRow > lastCol) {
lastCol = lastCellInRow;
}
}
return lastCol;
}
public boolean setCellData(String sheetName, String colName, int rowNum, String data) {
try {
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
if (rowNum <= 0) return false;
int index = workBook.getSheetIndex(sheetName);
int colNum = -1;
if (index == -1) return false;
sheet = workBook.getSheetAt(index);
row = sheet.getRow(0);
for (int i = 0; i < row.getLastCellNum(); i++) {
// System.out.println(row.getCell(i).getStringCellValue().trim());
if (row.getCell(i).getStringCellValue().trim().equals(colName)) colNum = i;
}
if (colNum == -1) return false;
sheet.autoSizeColumn(colNum);
row = sheet.getRow(rowNum - 1);
if (row == null) row = sheet.createRow(rowNum - 1);
cell = row.getCell(colNum);
if (cell == null) cell = row.createCell(colNum);
// cell style
// CellStyle cs = workbook.createCellStyle();
// cs.setWrapText(true);
// cell.setCellStyle(cs);
cell.setCellValue(data);
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
public static void addErrorRow(XSSFRow srcRow) {
try {
XSSFRow destRow = sheetWrite.createRow(rowCount);
// XSSFRow destRow =null;
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
XSSFCell oldCell = srcRow.getCell(j); // ancienne cell
XSSFCell newCell = destRow.getCell(j); // new cell
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(oldCell, newCell);
}
}
} catch (Exception ex) {
System.out.println("Error in writing Row in reject excel file :: " + ex.getMessage());
ex.printStackTrace();
}
}
public static void main(String[] args) {
File f = new File("D:\\data.xlsx");
try {
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(f));
if (workbook != null) {
XSSFSheet sheet = workbook.getSheetAt(0);
for (int i = 0; i < sheet.getLastRowNum(); i++) {
XSSFRow row = sheet.getRow(i);
String bigPic = row.getCell(0).toString();
System.out.println(bigPic);
for (int j = 0; j < row.getLastCellNum(); j++) {
System.out.print("," + row.getCell(j));
}
System.out.println("\n");
}
System.out.println("ok");
}
} catch (Exception e) {
e.printStackTrace();
}
}
/** 导入Excel表格 */
@SuppressWarnings("unused")
public String intoDB() throws IOException {
String uploadPath = ServletActionContext.getServletContext().getRealPath("/upload");
isImpSuccess = false; // 判断数据导入是否成功
errCount = 0; // 判断导入数据模板是否对应
// 基于myFile创建一个文件输入流
InputStream is = new FileInputStream(myFile);
// 设置目标文件
File toFile = new File(uploadPath, this.getMyFileFileName());
String caseName = null; // 活动名称
String caseDesc = null; // 活动描述
Timestamp caseSt = null; // 活动开始时间
Timestamp caseEt = null; // 活动结束时间
String sysUserId = null; // 创建/修改用户ID
Timestamp sysDt = null; // 修改时间
Integer status = null; // 活动状态
String caseCode = null; // 活动编码
Double ratioNew = null; // 新款占比
Integer num = null; // 参与款数
Date date = new Date(); // 创建一个时间对象，获取到当前的时间
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 设置时间显示格式
String str = sdf.format(date); // 将当前时间格式化为需要的类型
sysDt = Timestamp.valueOf(str);
sysUserId = ParaCasePAction.getCurrentUserName();
/** 2007版的读取方法 */
int k = 0;
int flag = 0; // 指示指针所访问的位置
if (myFile != null) {
try {
Workbook workbook = WorkbookFactory.create(toFile);
intolist.clear();
// Workbook workbook = new XSSFWorkbook(is);//初始化workbook对象
for (int numSheets = 0;
numSheets < workbook.getNumberOfSheets();
numSheets++) { // 读取每一个sheet
if (null != workbook.getSheetAt(numSheets)) {
XSSFSheet aSheet = (XSSFSheet) workbook.getSheetAt(numSheets); // 定义Sheet对象
for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {
// 进入当前sheet的行的循环
if (null != aSheet.getRow(rowNumOfSheet)) {
XSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 定义行，并赋值
for (int cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {
// 读取rowNumOfSheet值所对应行的数据
XSSFCell xCell = aRow.getCell(cellNumOfRow); // 获得行的列数
// //获得列值
// System.out.println("type="+xCell.getCellType());
if (null != aRow.getCell(cellNumOfRow)) {
// 如果rowNumOfSheet的值为0，则读取表头，判断excel的格式和预定格式是否相符
if (rowNumOfSheet == 1) {
if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
/**
* 一下根据从Excel的各列命名是否符合要求：如下面匹配： 活动名称，活动描述，活动开始时间， 活动结束时间,操作用户,
* 修改时间,活动状态,活动编码,新款占比 ,参与款数,备注开始时间,备注结束时间
*/
if (cellNumOfRow == 0) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动名称")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 1) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动描述")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 2) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动开始时间")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 3) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动结束时间")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 4) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动状态")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 5) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("活动编码")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 6) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("新款占比")) {
flag++;
} else {
errCount++;
}
} else if (cellNumOfRow == 7) {
if (xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim()
.equals("参与款数")) {
flag++;
} else {
errCount++;
}
}
}
} else {
// rowNumOfSheet != 0 即开始打印内容
// 获取excel中每列的值，并赋予相应的变量，如下的赋值的ID，name,sex,
// Dormitory,sept;
if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { // 为数值型
System.out.println(
"===============进入XSSFCell .CELL_TYPE_NUMERIC模块============");
switch (cellNumOfRow) {
case 2:
Date St = (Date) xCell.getDateCellValue(); // 对日期处理
caseSt = new Timestamp(St.getTime());
break;
case 3:
Date Et = (Date) xCell.getDateCellValue(); // 对日期处理
caseEt = new Timestamp(Et.getTime());
break;
case 4:
status = (int) xCell.getNumericCellValue();
break;
case 6:
ratioNew = xCell.getNumericCellValue();
break;
case 7:
num = (int) xCell.getNumericCellValue();
break;
}
} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { // 为字符串型
System.out.println(
"===============进入XSSFCell .CELL_TYPE_STRING模块============");
switch (cellNumOfRow) {
case 0:
caseName =
xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim();
break;
case 1:
caseDesc =
xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim();
break;
case 5:
caseCode =
xCell
.getStringCellValue()
.replace('\t', ' ')
.replace('\n', ' ')
.replace('\r', ' ')
.trim();
break;
}
} else if (xCell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
System.out.println(
"提示：在Sheet"
+ (numSheets + 1)
+ "中的第"
+ (rowNumOfSheet + 1)
+ "行的第"
+ (cellNumOfRow + 1)
+ "列的值为空，请查看核对是否符合约定要求");
switch (cellNumOfRow) {
case 0:
caseName = "";
break;
case 1:
caseDesc = "";
break;
case 2:
caseSt = null;
break;
case 3:
caseEt = null;
break;
case 4:
status = null;
break;
case 5:
caseCode = "";
break;
}
}
}
}
}
// 判断各个元素被赋值,如果放入数据库，就直接使用数据的插入的函数就可以了。
if (aRow.getRowNum() > 1) {
ParaDt pd = new ParaDt();
pd.setCaseName(caseName);
pd.setCaseDesc(caseDesc);
pd.setCaseEt(caseEt);
pd.setCaseSt(caseSt);
pd.setSysUserId(sysUserId);
pd.setSysDt(sysDt);
pd.setStatus(status);
pd.setCaseCode(caseCode);
pd.setRatioNew(ratioNew);
pd.setNum(num);
pd.setCaseCode(caseCode);
intolist.add(pd);
}
} // 获得一行，即读取每一行
}
// 读取每一个sheet
}
}
refreshList = "paraCaseDtgetParaDtAll";
titleName = "营销活动实例";
if (errCount > 0) {
msg = "导入数据与模板不符，导入失败！";
} else {
// 调用sever方法
isImpSuccess = paraDtService.addOneBoat(intolist, 500);
if (isImpSuccess) {
msg = "营销活动实例导入成功！";
} else {
msg = "营销活动实例导入失败！";
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
return "importExcel";
}
