// removes a column and all the contents
public boolean removeColumn(String sheetName, int colNum) {
try {
if (!isSheetExist(sheetName)) return false;
fis = new FileInputStream(path);
workBook = new XSSFWorkbook(fis);
sheet = workBook.getSheet(sheetName);
XSSFCellStyle style = workBook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
XSSFCreationHelper createHelper = workBook.getCreationHelper();
style.setFillPattern(HSSFCellStyle.NO_FILL);
for (int i = 0; i < getRowCount(sheetName); i++) {
row = sheet.getRow(i);
if (row != null) {
cell = row.getCell(colNum);
if (cell != null) {
cell.setCellStyle(style);
row.removeCell(cell);
}
}
}
fos = new FileOutputStream(path);
workBook.write(fos);
fos.close();
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
private int createMonth(
XSSFSheet sheet, LocalDate dt, int dayRow, int monthRow, int row, CellCopyPolicy policy) {
int r = row;
final int nbDays = dt.dayOfMonth().getMaximumValue();
final int month = dt.monthOfYear().get();
/* Insert days for the month */
for (int day = 1; day <= nbDays; day++) {
XSSFRow inserted = copyRow(sheet, dayRow, r, policy);
inserted.getCell(COLUMN_MONTH).setCellValue(month);
inserted.getCell(COLUMN_DAY).setCellValue(day);
r++;
}
/* Insert month summary */
XSSFRow inserted = copyRow(sheet, monthRow, r, policy);
inserted.getCell(COLUMN_MONTH).setCellValue(month);
r++;
/* Group month rows */
int groupRow = r - 2;
sheet.groupRow(row, groupRow);
sheet.setRowGroupCollapsed(groupRow, true);
return r;
}
/**
* @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;
}
// 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";
}
}
public ArrayList<String> working_Section(
int SOOWDLocation, int EOOWDLocation, XSSFWorkbook workbook) {
ArrayList<String> workingSection = new ArrayList<String>();
XSSFRow row;
XSSFCell cell;
XSSFSheet Sheet = workbook.getSheet("Operation_Standard");
String genrateFormula;
for (int start = (SOOWDLocation - 1); start < EOOWDLocation; start++) {
try {
row = Sheet.getRow(start);
cell = row.getCell(2);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
genrateFormula = "Operation_Standard!C" + (row.getRowNum() + 1);
workingSection.add(genrateFormula);
break;
case Cell.CELL_TYPE_BLANK:
break;
default:
System.out.println("Error");
break;
}
} catch (Exception e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
return workingSection;
}
/**
* 指定した列番号の<b>XSSFCell</b>を取得する。有効範囲外のセルの場合は新規作成する。
*
* @param row <b>XSSFRow</b>
* @param nColumn 取得したいセルの列番号
* @return <b>XSSFCell</b>
*/
public static XSSFCell getCellAnyway(XSSFRow row, int nColumn) {
assert row != null;
XSSFCell cell = row.getCell(nColumn);
if (cell == null) {
cell = row.createCell(nColumn);
}
return cell;
}
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();
}
}
/**
* 指定行の有効列数を取得する
*
* @param row 行データ
* @return 有効列数
*/
public static int getColumnCount(XSSFRow row) {
if (row != null) {
for (int nColumn = 0; ; nColumn++) {
XSSFCell cell = row.getCell(nColumn);
if (cell == null) {
return nColumn;
}
}
}
return 0;
}
public static void setCellStyleX(int row, int col, XSSFSheet sheet, XSSFCellStyle style) {
XSSFRow r = sheet.getRow(row);
if (null == r) {
r = sheet.createRow(row);
}
XSSFCell cell = r.getCell(col);
if (null == cell) {
cell = sheet.getRow(row).createCell(col);
}
cell.setCellStyle(style);
}
/**
* 合并单元格后给合并后的单元格加边框
*
* @param region
* @param cs
*/
public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {
int toprowNum = region.getFirstRow();
for (int i = toprowNum; i <= region.getLastRow(); i++) {
XSSFRow row = sheet.getRow(i);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
XSSFCell cell = row.getCell(j); // XSSFCellUtil.getCell(row,
// (short) j);
cell.setCellStyle(cs);
}
}
}
public static void setCellsX(int row, int col, Object colValue, XSSFFont font, XSSFSheet sheet) {
XSSFRow r = sheet.getRow(row);
if (null == r) {
r = sheet.createRow(row);
}
XSSFCell cell = r.getCell(col);
if (null == cell) {
cell = sheet.getRow(row).createCell(col);
}
setCellValueOfCnX(cell, colValue);
cell.getCellStyle().setFont(font);
}
public List<String> getAttributes(MultipartFile attributeReport, String classId) {
List<String> attributes = new ArrayList<String>();
try {
XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(attributeReport.getInputStream());
XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
for (int i = 2; i < worksheetIn.getLastRowNum(); i++) {
XSSFRow rowIn = worksheetIn.getRow(i);
if (XSSFCell.CELL_TYPE_NUMERIC == rowIn.getCell(1).getCellType()) {
if (rowIn.getCell(1).getNumericCellValue() == Integer.parseInt(classId)) {
attributes.add(
rowIn.getCell(2).getStringCellValue()
+ "###"
+ rowIn.getCell(7).getStringCellValue());
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return attributes;
}
private static void extractGeoJSONFiles(FileInputStream in, String outPath) throws IOException {
outPath += "geojson/";
XSSFWorkbook workbook = new XSSFWorkbook(in);
XSSFSheet sheet = workbook.getSheet("scenarios");
int numRows = sheet.getLastRowNum() + 1;
for (int i = 0; i < numRows; i++) {
XSSFRow row = sheet.getRow(i);
String userId = ((int) row.getCell(2).getNumericCellValue()) + "";
String scenarioId = row.getCell(5).getStringCellValue();
String geoJSON = row.getCell(6).getStringCellValue();
String crsString =
"\"crs\": { \"type\": \"name\", \"properties\": { \"name\": \"urn:ogc:def:crs:EPSG::3395\" } },";
geoJSON = geoJSON.replaceFirst(",", ",\n" + crsString + "\n");
LOG.info(geoJSON);
String filename = userId + "_" + scenarioId + ".geojson";
String subdir = scenarioId.contains("-1") ? "/1/" : "/2/";
writeToFile(geoJSON, filename, outPath + subdir);
LOG.info("written geoJSON to " + outPath + filename);
}
workbook.close();
}
/**
* 列方向のセルの値を合算する
*
* @param sheet 編集対象シート
* @param nColumn 行番号
* @param nStartRow 開始列番号
* @param nEndRow 終了列番号
* @return 合算値
*/
public static int sumColumn(XSSFSheet sheet, int nColumn, int nStartRow, int nEndRow) {
int sum = 0;
for (int nIndex = nStartRow; nIndex <= nEndRow; nIndex++) {
XSSFRow row = sheet.getRow(nIndex);
assert row != null;
XSSFCell cell = row.getCell(nColumn);
assert cell != null;
if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
sum += cell.getNumericCellValue();
}
}
return sum;
}
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();
}
}
/**
* セルの値を取得する
*
* @param row 行データ
* @param nColumn 列番号
* @return セルの値
*/
public static Object getData(XSSFRow row, int nColumn) {
if (row != null) {
XSSFCell cell = row.getCell(nColumn);
if (cell != null) {
if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
return cell.getNumericCellValue();
} else if (XSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
return cell.getStringCellValue();
}
}
}
return null;
}
public List<XSSFCell> writeStyle(MultipartFile fileIn, String classId) {
List<XSSFCell> attributeCell = new ArrayList<XSSFCell>();
try {
XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(fileIn.getInputStream());
XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
for (int i = 1; i < worksheetIn.getLastRowNum(); i++) {
XSSFRow rowIn = worksheetIn.getRow(i);
if (XSSFCell.CELL_TYPE_NUMERIC == rowIn.getCell(7).getCellType()) {
if (rowIn.getCell(7).getNumericCellValue() == Integer.parseInt(classId)) {
attributeCell.add(rowIn.getCell(14));
int j = 20;
for (; j < 44; ) {
attributeCell.add(rowIn.getCell(j));
j += 2;
}
break;
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return attributeCell;
}
/**
* 指定セルの削除
*
* @param sheet シート
* @param startRow 開始行番号
* @param endRow 終了行番号
* @param startColumn 開始列番号
* @param endColumn 終了列番号
*/
public static void removeCell(
XSSFSheet sheet, int startRow, int endRow, int startColumn, int endColumn) {
assert sheet != null;
for (int nRow = startRow; nRow <= endRow; nRow++) {
XSSFRow row = sheet.getRow(nRow);
if (row != null) {
for (int nColumn = startColumn; nColumn <= endColumn; nColumn++) {
XSSFCell cell = row.getCell(nColumn);
if (cell != null) {
row.removeCell(cell);
}
}
}
}
}
/**
* 上線は太線のセル行を探す
*
* @param nRow 行データ
* @return 有効列数
*/
public static int getRowForBold(XSSFSheet sheet, int nRow, int pageRowNum) {
int nRowIndex = nRow;
for (nRowIndex = nRow; nRowIndex > (nRow - pageRowNum); nRow--) {
XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow);
if (row != null) {
XSSFCell cell = row.getCell(0);
XSSFCellStyle styletmp = cell.getCellStyle();
short borderTopnum = styletmp.getBorderTop();
short borderBold = XSSFCellStyle.BORDER_MEDIUM;
if (styletmp.getBorderTop() == (XSSFCellStyle.BORDER_MEDIUM)) {
break;
}
}
}
return nRowIndex;
}
/**
* セルに設定された計算式を評価して値を取得する
*
* @param nRow 行番号
* @param nColumn 列番号
* @return セルの値
*/
public static Object getDataByEvaluateFormula(XSSFSheet sheet, int nRow, int nColumn) {
assert sheet != null;
XSSFRow row = getRowAnyway(sheet, nRow);
if (row != null) {
XSSFCell cell = row.getCell(nColumn);
if (cell != null) {
FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
if (eval != null) {
CellValue value = eval.evaluate(cell);
if (value != null) {
return value.getNumberValue();
}
}
}
}
return null;
}
public static void main(String[] args) throws IOException {
File file = new File("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx");
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row = sheet.getRow(0);
XSSFCell cell = row.getCell(0);
System.out.println(cell);
XSSFSheet write = workbook.createSheet();
XSSFRow rowwrite = write.createRow(0);
XSSFCell cell1 = rowwrite.createCell(1);
cell1.setCellValue("Yamini");
FileOutputStream file1 =
new FileOutputStream("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx");
workbook.write(file1);
file1.close();
}
@SuppressWarnings("static-access")
public static void setCellData(String Result, int RowNum, int ColNum) throws Exception {
try {
Row = ExcelWSheet.getRow(RowNum);
Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);
if (Cell == null) {
Cell = Row.createCell(ColNum);
Cell.setCellValue(Result);
} else {
Cell.setCellValue(Result);
}
// Constant variables Test Data path and Test Data file name
FileOutputStream fileOut =
new FileOutputStream(Constant.Path_TestData + Constant.File_TestData);
ExcelWBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
throw (e);
}
}
public String processFiles(
MultipartFile contentTemplateGenerator,
MultipartFile attributeReport,
MultipartFile staplesMasterStyleGuide)
throws IOException {
fileNameList = new ArrayList<>();
String status = "Processing Failed.";
System.out.println(contentTemplateGenerator);
System.out.println(attributeReport);
System.out.println(staplesMasterStyleGuide);
XSSFWorkbook cTGWorkbook = new XSSFWorkbook(contentTemplateGenerator.getInputStream());
XSSFSheet worksheet = cTGWorkbook.getSheetAt(0);
Set<String> uniqueClassId = new HashSet<String>();
DateFormat dateFormat = new SimpleDateFormat("MMddyyyy");
Date date = new Date();
String dateFinal = dateFormat.format(date);
System.out.println(dateFinal);
String vendor = "";
for (int i = 1; i < worksheet.getLastRowNum(); i++) {
XSSFRow row = worksheet.getRow(i);
uniqueClassId.add(row.getCell(1).getRawValue());
}
File file = null;
String fileName = null;
for (String s : uniqueClassId) {
for (int i = 1; i < worksheet.getLastRowNum(); i++) {
XSSFRow row = worksheet.getRow(i);
String vendortemp = row.getCell(6).getStringCellValue();
String classId = row.getCell(1).getRawValue();
if (s.equals(classId)) {
if (!vendor.equalsIgnoreCase(vendortemp)) {
fileName = SOURCE_FOLDER + "/" + s + "_" + vendortemp + "_" + dateFinal + ".xlsx";
file = new File(fileName);
if (file.createNewFile()) {
System.out.println("File is created:" + file.getName());
vendor = vendortemp;
}
}
}
}
vendor = "";
InputStream fin =
new FileInputStream(
"src/main/java/com/staples/product/template/Content_Smartsheet_Template- CTG 7 15 15.xlsx");
XSSFWorkbook cTGWorkbook_temp = new XSSFWorkbook(fin);
FileOutputStream fout = new FileOutputStream(file);
// cTGWorkbook_temp.write(fout);
XSSFWorkbook cTGWorkbookWithCTG =
writeCTGI(
cTGWorkbook_temp,
contentTemplateGenerator,
s,
staplesMasterStyleGuide,
attributeReport);
// XSSFWorkbook cTGWorkbookWithAttr=writeAttribute(cTGWorkbookWithCTG,
// staplesMasterStyleGuide, s);
// fout.close();
// fin.close();
// fout=new FileOutputStream(file);
cTGWorkbookWithCTG.write(fout);
fout.close();
System.out.println("Output File Format Created!!");
fileNameList.add(fileName);
}
if (fileNameList != null && !fileNameList.isEmpty()) {
zipIt(OUTPUT_ZIP_FILE);
}
status = OUTPUT_ZIP_FILE.split("/")[5];
System.out.println(status);
return status;
}
/** 导入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";
}
/**
* 读取2007-2013格式
*
* @param filePath 文件路径
* @return
* @throws java.io.IOException
*/
@SuppressWarnings("rawtypes")
public static List<Map> readExcel2007(String filePath) throws IOException {
List<Map> valueList = new ArrayList<Map>();
FileInputStream fis = null;
try {
fis = new FileInputStream(filePath);
XSSFWorkbook xwb = new XSSFWorkbook(fis); // 构造 XSSFWorkbook 对象，strPath 传入文件路径
XSSFSheet sheet = xwb.getSheetAt(0); // 读取第一章表格内容
// 定义 row、cell
XSSFRow row;
// 循环输出表格中的第一行内容 表头
Map<Integer, String> keys = new HashMap<Integer, String>();
row = sheet.getRow(0);
if (row != null) {
// System.out.println("j = row.getFirstCellNum()::"+row.getFirstCellNum());
// System.out.println("row.getPhysicalNumberOfCells()::"+row.getPhysicalNumberOfCells());
for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
// 通过 row.getCell(j).toString() 获取单元格内容，
if (row.getCell(j) != null) {
if (!row.getCell(j).toString().isEmpty()) {
keys.put(j, row.getCell(j).toString());
}
} else {
keys.put(j, "K-R1C" + j + "E");
}
}
}
// 循环输出表格中的从第二行开始内容
for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row != null) {
boolean isValidRow = false;
Map<String, Object> val = new HashMap<String, Object>();
for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
XSSFCell cell = row.getCell(j);
if (cell != null) {
String cellValue = null;
if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
cellValue =
new DataFormatter()
.formatRawCellContents(
cell.getNumericCellValue(), 0, "yyyy-MM-dd HH:mm:ss");
} else {
cellValue = String.valueOf(cell.getNumericCellValue());
}
} else {
cellValue = cell.toString();
}
if (cellValue != null && cellValue.trim().length() <= 0) {
cellValue = null;
}
val.put(keys.get(j), cellValue);
if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) {
isValidRow = true;
}
}
}
// 第I行所有的列数据读取完毕，放入valuelist
if (isValidRow) {
valueList.add(val);
}
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
fis.close();
}
return valueList;
}
public XSSFWorkbook writeCTGI(
XSSFWorkbook cTGWorkbook,
MultipartFile fileIn,
String classId,
MultipartFile staplesMasterStyleGuide,
MultipartFile attributeReport) {
try {
List<XSSFCell> attributeCell = writeStyle(staplesMasterStyleGuide, classId);
List<String> attributes = getAttributes(attributeReport, classId);
XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(fileIn.getInputStream());
XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
XSSFSheet worksheetOut = cTGWorkbook.getSheetAt(0);
int i = 1;
int j = 4;
XSSFCell cell = null;
for (i = 1; i < worksheetIn.getLastRowNum(); i++) {
XSSFRow rowIn = worksheetIn.getRow(i);
XSSFRow rowOut = worksheetOut.createRow(j);
if (rowIn.getCell(1).getNumericCellValue() == Integer.parseInt(classId)) {
XSSFCell cell1 = rowOut.createCell(13);
if (rowIn.getCell(2) != null) cell1.setCellValue(rowIn.getCell(2).getStringCellValue());
XSSFCell cell2 = rowOut.createCell(15);
cell2.setCellValue(classId);
XSSFCell cell3 = rowOut.createCell(19);
if (rowIn.getCell(3) != null) cell3.setCellValue(rowIn.getCell(3).getStringCellValue());
XSSFCell cell4 = rowOut.createCell(20);
if (rowIn.getCell(0) != null) cell4.setCellValue(rowIn.getCell(0).getStringCellValue());
XSSFCell cell5 = rowOut.createCell(24);
if (rowIn.getCell(3) != null) cell5.setCellValue(rowIn.getCell(3).getStringCellValue());
XSSFCell cell6 = rowOut.createCell(179);
if (rowIn.getCell(5) != null) cell6.setCellValue(rowIn.getCell(5).getNumericCellValue());
XSSFCell cell7 = rowOut.createCell(26);
cell7 = attributeCell.get(0);
int z = 1;
for (int k = 36; k < 48; k++) {
cell = rowOut.createCell(k);
cell.setCellValue((attributeCell.get(z)).getStringCellValue());
z++;
}
int counter = 1;
int cellCount = 63;
for (String attribute : attributes) {
if (counter <= 50) {
String lable = attribute.split("###")[0];
String value = attribute.split("###")[1];
cell = rowOut.createCell(cellCount);
cell.setCellValue(lable);
cell = rowOut.createCell(cellCount + 1);
cell.setCellValue(value);
counter++;
cellCount += 2;
}
}
}
j++;
}
} catch (IOException e) {
e.printStackTrace();
}
return cTGWorkbook;
}
public void readSNPs() {
boolean done = false;
int rownum = 1;
while (!done) {
XSSFRow row = sheet.getRow(rownum);
if (row == null) {
done = true;
} else {
String gene = row.getCell(0).getRichStringCellValue().getString();
String strongestallele = row.getCell(1).getRichStringCellValue().getString();
String snp = row.getCell(2).getRichStringCellValue().getString();
String riskfrequency = null;
XSSFCell risk = row.getCell(3);
switch (risk.getCellType()) {
case Cell.CELL_TYPE_STRING:
riskfrequency = risk.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
riskfrequency = Double.toString(risk.getNumericCellValue());
break;
}
int pvalue_mantissa = (int) row.getCell(4).getNumericCellValue();
int pvalue_exponent = (int) row.getCell(5).getNumericCellValue();
float pval_float = (float) (pvalue_mantissa * Math.pow(10, pvalue_exponent));
String pval_num = setpvalnum(pvalue_mantissa, pvalue_exponent);
String pvaluetxt;
if (row.getCell(6, row.RETURN_BLANK_AS_NULL) != null) {
pvaluetxt = row.getCell(6).getRichStringCellValue().getString();
} else {
pvaluetxt = null;
}
Double orpercopynum;
if (row.getCell(7, row.RETURN_BLANK_AS_NULL) != null) {
orpercopynum = row.getCell(7).getNumericCellValue();
} else {
orpercopynum = null;
}
Double orpercopyrecip;
if (row.getCell(8, row.RETURN_BLANK_AS_NULL) != null) {
orpercopyrecip = row.getCell(8).getNumericCellValue();
if (orpercopyrecip == 0) {
orpercopyrecip = null;
}
} else {
orpercopyrecip = null;
}
if (orpercopyrecip != null) {
orpercopynum = ((100 / orpercopyrecip) / 100);
}
char ortype = row.getCell(9).getRichStringCellValue().getString().charAt(0);
String orpercopyrange;
if (row.getCell(10, row.RETURN_BLANK_AS_NULL) != null) {
orpercopyrange = row.getCell(10).getRichStringCellValue().getString();
} else {
orpercopyrange = null;
}
String orpercopyunitdescr;
if (row.getCell(11, row.RETURN_BLANK_AS_NULL) != null) {
orpercopyunitdescr = row.getCell(11).getRichStringCellValue().getString();
} else {
orpercopyunitdescr = null;
}
Double orpercopystderror;
if (row.getCell(12, row.RETURN_BLANK_AS_NULL) != null) {
orpercopystderror = row.getCell(12).getNumericCellValue();
} else {
orpercopystderror = null;
}
if ((orpercopyrange == null) && (orpercopystderror != null)) {
orpercopyrange = setRange(orpercopystderror, orpercopynum);
}
if ((orpercopyrecip != null) && (orpercopyrange != null) && (orpercopystderror == null)) {
orpercopyrange = reverseCI(orpercopyrange);
}
String snptype = row.getCell(13).getRichStringCellValue().getString();
SNPentry thisSNP =
new SNPentry(
gene,
strongestallele,
snp,
riskfrequency,
pvalue_mantissa,
pvalue_exponent,
pval_float,
pval_num,
pvaluetxt,
orpercopynum,
orpercopyrecip,
ortype,
orpercopyrange,
orpercopyunitdescr,
orpercopystderror,
snptype);
allSNPs.add(thisSNP);
}
rownum++;
}
}
