/**
* @param src 源文件
* @param dst 目标位置
*/
private static void copy(File src, File dst) {
InputStream in = null;
OutputStream out = null;
try {
in = new BufferedInputStream(new FileInputStream(src), BUFFER_SIZE);
out = new BufferedOutputStream(new FileOutputStream(dst), BUFFER_SIZE);
byte[] buffer = new byte[BUFFER_SIZE];
int len = 0;
while ((len = in.read(buffer)) > 0) {
out.write(buffer, 0, len);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != in) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (null != out) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 读取97-2003格式
*
* @param filePath 文件路径
* @throws java.io.IOException
*/
@SuppressWarnings("rawtypes")
public static List<Map> readExcel2003(String filePath) throws IOException {
// 返回结果集
List<Map> valueList = new ArrayList<Map>();
FileInputStream fis = null;
try {
fis = new FileInputStream(filePath);
HSSFWorkbook wookbook = new HSSFWorkbook(fis); // 创建对Excel工作簿文件的引用
HSSFSheet sheet = wookbook.getSheetAt(0); // 在Excel文档中，第一张工作表的缺省索引是0
int rows = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数
Map<Integer, String> keys = new HashMap<Integer, String>();
int cells = 0;
// 遍历行（第1行 表头） 准备Map里的key
HSSFRow firstRow = sheet.getRow(0);
if (firstRow != null) {
// 获取到Excel文件中的所有的列
cells = firstRow.getPhysicalNumberOfCells();
// 遍历列
for (int j = 0; j < cells; j++) {
// 获取到列的值
try {
HSSFCell cell = firstRow.getCell(j);
String cellValue = getCellValue(cell);
keys.put(j, cellValue);
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 遍历行（从第二行开始）
for (int i = 1; i < rows; i++) {
// 读取左上端单元格(从第二行开始)
HSSFRow row = sheet.getRow(i);
// 行不为空
if (row != null) {
// 准备当前行 所储存值的map
Map<String, Object> val = new HashMap<String, Object>();
boolean isValidRow = false;
// 遍历列
for (int j = 0; j < cells; j++) {
// 获取到列的值
try {
HSSFCell cell = row.getCell(j);
String cellValue = getCellValue(cell);
val.put(keys.get(j), cellValue);
if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) {
isValidRow = true;
}
} catch (Exception e) {
e.printStackTrace();
}
}
// 第I行所有的列数据读取完毕，放入valuelist
if (isValidRow) {
valueList.add(val);
}
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
fis.close();
}
return valueList;
}
public static void calcCharts() throws Exception {
// alle Dateien einlesen im obigen Ordner
File[] files = new File(folderIN).listFiles();
int i = 0;
Messreihe[] RQs = new Messreihe[zINTER];
Messreihe[] RISs = new Messreihe[zINTER];
Vector<Messreihe>[] vRISs = new Vector[zINTER];
Vector<Messreihe>[] vFITDATA = new Vector[zINTER];
ReturnIntervallStatistik[] ris = new ReturnIntervallStatistik[zINTER];
// 58 files and 9 sections ...
double tTEST_DATA[][][] = new double[files.length][zINTER][5];
// 0=RQs, 1=m 2=RR 3=FEHLENDE-m 4=FEHLENDE-RR
for (int ii = 0; ii < zINTER; ii++) {
ris[ii] = new ReturnIntervallStatistik(pScale, pBinning);
// ris[ii].scaleY_LN = sLN;
// ris[ii].scaleY_LOG = sLOG;
RQs[ii] = new Messreihe("RQ_" + ii);
RISs[ii] = new Messreihe("RIS_" + ii);
vRISs[ii] = new Vector<Messreihe>();
vFITDATA[ii] = new Vector<Messreihe>();
}
Vector<Messreihe> all = new Vector<Messreihe>();
double linFitMIN;
double linFitMAX;
for (File f : files) {
if (f.getName().endsWith(".txt")) {
DecimalFormat nf = new DecimalFormat("0.0000000");
Messreihe data =
MessreihenLoader.getLoader().loadMessreihe(new File(folderIN + f.getName()));
Messreihe[] parts = data.split(zL, zINTER);
int j = 0;
// r ist die Messreihe mit den normalen Daten
for (Messreihe r : parts) {
double vRQ = r.getAvarage();
// System.out.print( "file -> " + i + " part -> " + j + "\t" + nf.format( vRQ ) + "\t" );
// System.out.print( r.toString() );
tTEST_DATA[i][j][0] = vRQ;
vRISs[j].add(r);
RQs[j].addValue(vRQ);
ReturnIntervallStatistik rr = new ReturnIntervallStatistik(pScale, pBinning);
rr.doScale_by_Rq = scaleRQ;
// rr.scaleY_LN = sLN;
// rr.scaleY_LOG = sLOG;
rr.setRQ(vRQ);
rr.addDistData(r.getYValues());
if (scaleRQ) {
linFitMIN = linFit_MIN * vRQ;
linFitMAX = linFit_MAX * vRQ;
// System.out.println( "( " + linFit_MIN + ", " + linFit_MAX + ") ");
} else {
linFitMIN = linFit_MIN;
linFitMAX = linFit_MAX;
// System.out.println( "( " + linFitMIN + ", " + linFitMAX + ") ");
}
;
// hier sollten nun die Daten für jede Reihe da sein ...
/** Problem: in den einzelnen Reihen kann man nicht sinnvoll den Anstieg bestimmen !!! */
Messreihe toFit = rr.mrHaeufigkeit.shrinkX(linFit_MIN, linFit_MAX);
SimpleRegression reg;
try {
reg = toFit.linFit(linFit_MIN, linFit_MAX);
double slope = reg.getSlope();
double RR = reg.getRSquare();
// System.out.println("m=" + slope + " \tRR=" + RR);
tTEST_DATA[i][j][1] = slope;
tTEST_DATA[i][j][2] = RR;
all.add(toFit);
} catch (Exception ex) {
// Logger.getLogger(RISAnalyse3.class.getName()).log(Level.SEVERE, null, ex);
}
vFITDATA[j].add(rr.mrHaeufigkeit);
try {
ris[j].add(rr);
} catch (Exception ex) {
Logger.getLogger(RISAnalyse3.class.getName()).log(Level.SEVERE, null, ex);
}
j++;
}
i++;
}
}
if (showRAWData) {
for (int i3 = 0; i3 < zINTER; i3++) {
MultiChart.openAndStore(
vRISs[i3],
label + " Intervall: " + i3 + " - scale=" + pScale + ", binning=" + pBinning,
"r",
"P(r)",
false,
folderOUT,
label + "_P(r)",
getParameterSet());
}
}
System.out.print(folderOUT);
Messreihe rq = new Messreihe("Rq(i)");
Messreihe rqSIGMA = new Messreihe("sigma(Rq(i))");
Vector<Messreihe> chart2Data = new Vector<Messreihe>();
chart2Data.add(rq);
chart2Data.add(rqSIGMA);
Vector<Messreihe> vMR = new Vector<Messreihe>();
// für alle Intervalle des Files ...
for (int d = 0; d < zINTER; d++) {
try {
ris[d].calcMessreihen();
} catch (Exception ex) {
Logger.getLogger(RISAnalyse3.class.getName()).log(Level.SEVERE, null, ex);
}
RISs[d] = ris[d].mrHaeufigkeit;
// System.out.println( "(b)\n" + ris[d].toString() );
ris[d].mrVerteilung.setLabel("Int_" + (d + 1));
vMR.add(ris[d].mrVerteilung);
RQs[d].calcAverage();
rq.addValuePair((double) d, RQs[d].getAvarage());
rqSIGMA.addValuePair((double) d, RQs[d].getStddev());
}
rq.normalize();
rqSIGMA.normalize();
// auf VMR nun den Fit ANWENDEN ...
Messreihe fitData1 = new Messreihe();
fitData1.setLabel("m");
Messreihe fitData2 = new Messreihe();
fitData2.setLabel("RR");
Vector<Messreihe> vFITS = new Vector<Messreihe>();
SimpleRegression reg2;
int c = 0;
for (Messreihe mr : vMR) {
try {
reg2 = mr.linFit(linFit_MIN, linFit_MAX);
double slope = reg2.getSlope();
double RR = reg2.getRSquare();
fitData1.addValuePair(c, slope);
fitData2.addValuePair(c, RR);
System.out.println("\tm=" + slope + " \tRR=" + RR);
c++;
} catch (Exception ex) {
}
}
;
fitData1.normalize();
fitData2.normalize();
vFITS.add(fitData1);
vFITS.add(fitData2);
if (showRAWRIS) {
for (int i3 = 0; i3 < zINTER; i3++) {
MultiChart.openAndStore(
vFITDATA[i3],
"vFIT " + label + " Intervall: " + i3 + " - scale=" + pScale + ", binning=" + pBinning,
"r",
"P(r)",
false,
folderOUT,
label + "_P(r)",
getParameterSet());
}
}
String labelX = "r/Rq";
String labelY = "P(r)*Rq";
if (!scaleRQ) {
labelX = "r";
labelY = "P(r)";
}
if (sLOG) {
labelY = "log(" + labelY + ")";
}
if (sLN) {
labelY = "ln(" + labelY + ")";
}
if (showCharts) {
MultiChart.openAndStore(
vMR,
label + " - scale=" + pScale + ", binning=" + pBinning,
labelX,
labelY,
true,
folderOUT,
label + "_P(r)",
getParameterSet());
MultiChart.openAndStore(
vFITS,
"FIT " + label + " - scale=" + pScale + ", binning=" + pBinning,
"i",
"m(i) RR(i)",
true,
folderOUT,
label + "_m(i)_RR(i)",
getParameterSet());
MultiChart.openAndStore(
chart2Data,
label + " - Rq(i)",
"i",
"<Rq>, <sigma(Rq)>",
true,
folderOUT,
label + "_Rq(i)",
getParameterSet());
MultiChart.open(
all,
"ALL " + label + " - scale=" + pScale + ", binning=" + pBinning,
"x",
"y",
false,
getParameterSet());
}
MesswertTabelle tab1 = new MesswertTabelle();
tab1.setHeader(getParameterSet());
tab1.setLabel(label + "_P(r)");
tab1.setMessReihen(vMR);
tab1.writeToFile(new File(folderOUT + File.separator + tab1.getLabel() + ".dat"));
MesswertTabelle tab6 = new MesswertTabelle();
tab6.setHeader(getParameterSet());
tab6.setLabel(label + "_m(i)_RR(i)");
tab6.setMessReihen(vFITS);
tab6.writeToFile(
new File(folderOUT + File.separator + "out" + File.separator + tab6.getLabel() + ".dat"));
MesswertTabelle tab2 = new MesswertTabelle();
tab2.setHeader(getParameterSet());
tab2.setLabel(label + "_Rq(i)");
tab2.setMessReihen(chart2Data);
tab2.writeToFile(new File(folderOUT + File.separator + tab2.getLabel() + ".dat"));
String fn = folderOUT + "/tTests_" + label + ".xls";
String fnTEMPL = folderOUT + "/tTests.xls";
HSSFWorkbook wb = null;
HSSFSheet[] sheets = new HSSFSheet[3];
File f2 = new File(fn);
File f1 = new File(fnTEMPL);
// Lesen der bestehenden Tabelle
FileInputStream fis = null;
try {
//
// Create a FileInputStream that will be use to read the excel file.
//
fis = new FileInputStream(fn);
//
// Create an excel workbook from the file system.
//
wb = new HSSFWorkbook(fis);
//
// Get the first sheet on the workbook.
//
} catch (Exception ex) {
ex.printStackTrace();
System.err.println(">>> " + ex.getMessage());
System.out.println("> Erzeuge neue XLS Datei ... ");
wb = new HSSFWorkbook();
sheets[0] = wb.createSheet("R_q");
sheets[1] = wb.createSheet("m");
sheets[2] = wb.createSheet("RR");
}
Messreihe[] mrRESULTS = new Messreihe[5];
try {
sheets[0] = wb.getSheet("R_q");
sheets[1] = wb.getSheet("m");
sheets[2] = wb.getSheet("RR");
// zur Sammlung der Mittelwerte für die Resultate ...
mrRESULTS[0] = new Messreihe("<R_q>");
mrRESULTS[1] = new Messreihe("<m>");
mrRESULTS[2] = new Messreihe("<RR>");
mrRESULTS[3] = new Messreihe("{m}");
mrRESULTS[4] = new Messreihe("{RR}");
// SCHRANKE für auszulassende Werte
double epsilon = 1e-6;
// ANZAHL der Reihen
for (int i2 = 1; i2 < files.length; i2++) {
// ANZAHL der INTERVALLE
for (int j2 = 0; j2 < zINTER; j2++) {
// ANZAHL der MESSSGRÖSSEN
for (int k2 = 0; k2 < 3; k2++) {
HSSFRow row = sheets[k2].getRow(i2);
if (row == null) row = sheets[k2].createRow(i2);
row.createCell(1).setCellValue(i2);
double value = tTEST_DATA[i2][j2][k2];
if (!Double.isNaN(value)) {
if (value > epsilon) {
row.createCell(j2 + 1).setCellValue(value);
}
} else {
row.createCell(j2 + 1).setCellValue("");
}
// System.out.println( i2 +"\t" + j2 + "\t" + k2 + "\t" + tTEST_DATA[i2][j2][k2]);
}
}
}
// MW für alle Spalten berechnen ... für drei Messwerte ...
for (int x = 0; x < 3; x++) {
double eps = 1e-3;
double anz = 0.0;
double summe = 0.0;
int counterNAN_values = 0;
// ANZAHL der INTERVALLE
for (int j2 = 0; j2 < zINTER; j2++) {
// ANZAHL der Reihen
for (int i2 = 1; i2 < files.length; i2++) {
double value = tTEST_DATA[i2][j2][x];
double RR = tTEST_DATA[i2][j2][2];
if (!Double.isNaN(value)) {
// if ( value > eps ) { // Variante 1
// if ( RR > 0.75) { // Variante 2
summe = summe + value;
anz++;
// }
} else {
counterNAN_values++;
}
}
double mw = summe / anz;
mrRESULTS[x].addValuePair(j2, mw);
if (x > 0) mrRESULTS[x + 2].addValuePair(j2, counterNAN_values);
anz = 0.0;
summe = 0.0;
counterNAN_values = 0;
}
}
mrRESULTS[3].normalize();
mrRESULTS[4].normalize();
Vector<Messreihe> v = new Vector<Messreihe>();
v.add(mrRESULTS[0]);
v.add(mrRESULTS[1]);
v.add(mrRESULTS[2]);
v.add(mrRESULTS[3]);
v.add(mrRESULTS[4]);
if (showCharts) {
MultiChart.openAndStore(
v,
label + " - scale=" + pScale + ", binning=" + pBinning,
"i",
"f(i)",
true,
folderOUT,
label + "_chart3",
getParameterSet());
}
FileOutputStream fout2 = new FileOutputStream(f2.getAbsolutePath());
wb.write(fout2);
fout2.close();
String[] titles = {"P(r)", "Rq(i)", "chart3", "m(i)_RR(i)"};
report.createResultlinePNG(label, titles, getParameterSetHTML());
System.out.println("[READY] " + label + "");
} catch (Exception ex) {
ex.printStackTrace();
}
}
/**
* Method main
*
* <p>Given 1 argument takes that as the filename, inputs it and dumps the cell values/types out
* to sys.out
*
* <p>given 2 arguments where the second argument Is the word "Write" and the first Is the
* filename - Writes out a sample (test) spReadsheet (see public HSSF(String filename, bool
* Write)).
*
* <p>given 2 arguments where the first Is an input filename and the second an output filename
* (not Write), attempts to fully Read in the spReadsheet and fully Write it out.
*
* <p>given 3 arguments where the first Is an input filename and the second an output filename
* (not Write) and the third Is "modify1", attempts to Read in the spReadsheet, deletes rows 0-24,
* 74-99. Changes cell at row 39, col 3 to "MODIFIED CELL" then Writes it out. Hence this Is
* "modify test 1". If you take the output from the Write test, you'll have a valid scenario.
*
* @param args
*/
public static void main(String[] args) {
if (args.Length < 2) {
/* try
{
HSSF hssf = new HSSF(args[ 0 ]);
Console.WriteLine("Data dump:\n");
HSSFWorkbook wb = hssf.hssfworkbook;
for (int k = 0; k < wb.GetNumberOfSheets(); k++)
{
Console.WriteLine("Sheet " + k);
HSSFSheet sheet = wb.GetSheetAt(k);
int rows = sheet.GetPhysicalNumberOfRows();
for (int r = 0; r < rows; r++)
{
HSSFRow row = sheet.GetPhysicalRowAt(r);
int cells = row.GetPhysicalNumberOfCells();
Console.WriteLine("ROW " + row.GetRowNum());
for (int c = 0; c < cells; c++)
{
HSSFCell cell = row.GetPhysicalCellAt(c);
String value = null;
switch (cell.GetCellType())
{
case HSSFCell.CELL_TYPE_FORMULA :
value = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_NUMERIC :
value = "NUMERIC value="
+ cell.GetNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING :
value = "STRING value="
+ cell.GetStringCellValue();
break;
default :
}
Console.WriteLine("CELL col="
+ cell.GetCellNum()
+ " VALUE=" + value);
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}*/
} else if (args.Length == 2) {
if (args[1].ToLower().Equals("Write")) {
Console.WriteLine("Write mode");
try {
long time = System.currentTimeMillis();
HSSF hssf = new HSSF(args[0], true);
System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time");
} catch (Exception e) {
e.printStackTrace();
}
} else {
Console.WriteLine("ReadWrite test");
try {
HSSF hssf = new HSSF(args[0]);
// HSSFStream hssfstream = hssf.hssfstream;
HSSFWorkbook wb = hssf.hssfworkbook;
FileOutputStream stream = new FileOutputStream(args[1]);
// HSSFCell cell = new HSSFCell();
// cell.SetCellNum((short)3);
// cell.SetCellType(HSSFCell.CELL_TYPE_NUMERIC);
// cell.SetCellValue(-8009.999);
// hssfstream.modifyCell(cell,0,(short)6);
wb.Write(stream);
stream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
} else if ((args.Length == 3) && args[2].ToLower().Equals("modify1")) {
try // delete row 0-24, row 74 - 99 && Change cell 3 on row 39 to string "MODIFIED CELL!!"
{
HSSF hssf = new HSSF(args[0]);
// HSSFStream hssfstream = hssf.hssfstream;
HSSFWorkbook wb = hssf.hssfworkbook;
FileOutputStream stream = new FileOutputStream(args[1]);
HSSFSheet sheet = wb.GetSheetAt(0);
for (int k = 0; k < 25; k++) {
HSSFRow row = sheet.GetRow(k);
sheet.RemoveRow(row);
}
for (int k = 74; k < 100; k++) {
HSSFRow row = sheet.GetRow(k);
sheet.RemoveRow(row);
}
HSSFRow row = sheet.GetRow(39);
HSSFCell cell = row.GetCell((short) 3);
cell.SetCellType(HSSFCell.CELL_TYPE_STRING);
cell.SetCellValue("MODIFIED CELL!!!!!");
// HSSFCell cell = new HSSFCell();
// cell.SetCellNum((short)3);
// cell.SetCellType(HSSFCell.CELL_TYPE_NUMERIC);
// cell.SetCellValue(-8009.999);
// hssfstream.modifyCell(cell,0,(short)6);
wb.Write(stream);
stream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
@Test
public void test_poi() {
final int rowNum = 27;
final int colNum = 15;
HSSFWorkbook wb = null;
Sheet sheet = null;
String today = "2013/8/31";
String sign = "Month to date";
String[] titles = {
"",
"",
"",
"Chinapay eMail\r\n 商城总计",
"Japan Page\r\n 日本馆首页",
"Taiwan Page\r\n 台湾馆首页",
"USA Page\r\n 美国馆首页",
"Anhui Page\r\n 安徽馆首页",
"China Page\r\n 中国馆首页"
};
String[] colNames = {
"",
"Page View (PV)\r\n 浏览量",
"Unique Visitor (UV)\r\n 独立访客",
"Completed Orders\r\n 确认订单",
"Transaction Amount\r\n 交易金额",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量"
};
int n = 0;
int len = 1;
String fileName = "D:/日报.xls";
File f = new File(fileName);
ByteArrayOutputStream byteArrayOut = null;
BufferedImage bufferImg = null;
String[] jpgUrls = {
"http://img.chinapay.com/data/files/store_37452/goods_93/small_201303271804531386.jpg",
"http://img.chinapay.com/data/files/store_44066/goods_37/201308280953576580.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_95/small_201309031434558044.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_180/small_201309031403003861.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_98/small_201309121508186810.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_24/small_201301241133447193.jpg"
};
String[] https = {
"http://emall.chinapay.com/goods/37452/1010000109792.html",
"http://emall.chinapay.com/goods/44066/1010000119323.html",
"http://emall.chinapay.com/goods/289253/1010000119621.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/289253/1010000119627.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/37452/1010000120588.html",
"http://emall.chinapay.com/goods/37452/1010000107096.html"
};
URL url = null;
HSSFHyperlink link = null;
HSSFPatriarch patri = null;
HSSFClientAnchor anchor = null;
try {
if (!f.exists()) {
wb = new HSSFWorkbook();
} else {
FileInputStream in = new FileInputStream(fileName);
wb = new HSSFWorkbook(in);
}
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFCellStyle.THIN_BACKWARD_DIAG);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style.setLeftBorderColor(HSSFColor.RED.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
style.setWrapText(true);
sheet = wb.createSheet("sheet " + ((int) (100000 * Math.random())));
// 设置列的宽度
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeight((short) 400);
Row row = null;
Cell cell = null;
for (int r = 0; r < rowNum; r++) {
row = sheet.createRow(r);
// 设置第1行当高度
if (r == 0) {
row.setHeightInPoints(30);
}
// 设置第2列以后的宽度(即列号>=2的列，列号从0开始)
if (r >= 2) {
sheet.setColumnWidth(r, 3020);
}
for (int c = 0; c < colNum; c++) {
cell = row.createCell(c);
cell.setCellStyle(style);
// 处理第一行
if (r == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 6));
sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 8));
sheet.addMergedRegion(new CellRangeAddress(r, r, 9, 10));
sheet.addMergedRegion(new CellRangeAddress(r, r, 11, 12));
sheet.addMergedRegion(new CellRangeAddress(r, r, 13, 14));
if (c < 3) {
cell.setCellValue(titles[n++]);
} else {
if ((c & 1) == 1) {
System.out.println("c===" + c);
cell.setCellValue(titles[n++]);
}
}
}
// 处理第2~8行
if (r > 0 && r <= 8) {
if (c == 0) {
if (r < 8 && (r & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
System.err.println("row----->" + r + " len----->" + (len));
cell.setCellValue(colNames[len++]);
} else if (r > 8) {
System.out.println("len+++++++++>" + (len));
cell.setCellValue(colNames[len++]);
}
} else if (c == 1) {
cell.setCellValue((r & 1) == 1 ? today : sign);
System.err.println("r---->" + r);
} else if (c == 2) {
cell.setCellValue((r & 1) == 1 ? "当天" : "当月");
} else {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
cell.setCellValue("26.55");
}
}
}
// 处理第8行以后的数据(不包括第8行)
if (r > 8) {
// 设置列高(图片的高度)
if (r % 3 == 0) {
sheet.getRow(r).setHeightInPoints(110);
}
if (c == 0) {
System.err.println("r---->" + r);
cell.setCellValue(colNames[r - 4]);
} else if (c == 1) {
cell.setCellValue((r % 3) == 0 ? today : (r % 3 == 1 ? "PV 浏览量" : "Total Sales 总额"));
} else if (c == 2) {
if (r % 9 == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 8, c, c));
if (r / 9 == 1) cell.setCellValue("当天");
else cell.setCellValue("当月");
cell.setCellStyle(style);
}
} else {
if (r % 3 == 0) {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
// 添加远程图片信息
url = new URL(jpgUrls[(c - 3) / 2]);
bufferImg = ImageIO.read(url.openStream());
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(bufferImg, "jpg", byteArrayOut);
patri = (HSSFPatriarch) sheet.createDrawingPatriarch();
anchor = new HSSFClientAnchor(10, 2, 0, 0, (short) c, r, (short) (c + 2), r + 1);
patri.createPicture(
anchor,
wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
bufferImg.flush();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// System.out.println(https[(c-3)/2]);
// link.setAddress("fetion/"+https[(c-3)/2]);
// cell.setHyperlink(link);
// link = (HSSFHyperlink) cell.getHyperlink();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// link.setAddress(https[(c-3)/2]);
// cell.setHyperlink(link);
}
} else {
if ((c & 1) == 0) {
link = wb.getCreationHelper().createHyperlink(Hyperlink.LINK_URL);
link.setAddress(https[(c - 3) / 2]);
cell.setHyperlink(link); // 设定单元格的链接
cell.setCellValue("图片超链接");
} else {
cell.setCellValue("Number");
}
}
}
}
}
}
// 备注
row = sheet.createRow(27);
cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(27, 27, 0, colNum - 1));
cell.setCellValue("* 销量排名不以销售金额计算，如相同销量者，则以PV量少者为优胜");
FileOutputStream out = new FileOutputStream(fileName);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("++++++++++++ EXCEl文件 success +++++++++++++");
}