→ loveme00835:用置底文網址放code吧, 這樣很難用到 07/19 14:43
→ tropical72:突然覺得 kkman 好好用,下載 90secs 07/19 15:05
→ Zephyr750:用寄外部信件的方式下載吧！ 07/19 16:33
推 softwind:用 google 的 project host 放code比較好吧... 07/19 23:08
→ loveme00835:看原po有沒有那個搜尋需求囉 07/19 23:12
由於外國有人也是這樣做，但是因為他的code包成.dll
（雖然也有opensource的，不過因為我還沒申請帳號，
暫時還沒有打算申請，所以無法下載，所以自己做一個）
感謝Edison.Shih.提供辛苦學習成果，可以讓這個class更完備
將Excel常用功能建成class，方便大家可以更方便的使用Excel功能
希望可以讓這個版更好，更多資源。
class屬名的kx Wang就是我，因為我覺得把「kawasaki的機種產品名」（我的ID）
放上去會惹誤會，所以放上kx
/*******************************************************************
* xlef.h *
* 此class由kx Wang設計，並發佈初版 *
* 二版則由Edison.Shih.提供函式，補足初版之不足 *
* *
* 由Edison.Shih.提供的函式補足，會做edisonx的記號 *
* *
* Class由 Visual C++ 6 開發 *
* 適用於Microsoft Excel 2003 於 Microsoft Excel 2003 SP3 測試無誤 *
* 測試平台 Microsoft Windows XP SP3 *
* 2011/7/11 kx Wang *
*******************************************************************/
#ifndef __XLEF_H__
#define __XLEF_H__
#include "excel.h"
#include "stdafx.h"
enum Boarder_Style
{
BS_NONE = 0, //無框線
BS_SOLIDLINE, //一般線
BS_BIGDASH, //小間隔虛線- - - - - -有粗細
BS_SMALLDASH, //大間隔虛線- - - - - -無粗細
BS_DOTDASH, //虛線-.-.-.-.-.-.
BS_DASHDOTDOT, //虛線.-..-..-..-..-.
BS_DOUBLSOLID = 9, //雙線============（不受粗細改變）
BS_SLASHDASH = 13 //雙線-/-/-/-/-/-/（不受粗細改變）
};
enum Boarder_Weight//（粗細）
{
BA_HAITLINE = 1, //比一般小（所以用虛線表示）
BA_THIN, //一般
BA_MEDIUM, //粗
BA_THICK //厚
};
enum Horizontal_Alignment
{
HA_GENERAL = 1,
HA_LEFT, //edisonx
HA_CENTER,
HA_RIGHT, //edisonx
HA_FILL, //重複至填滿 //edisonx
HA_JUSTIFYPARA, //段落重排（有留白邊，有自動斷行）
HA_CENTERACROSS, //跨欄置中（不合拼儲存格）
HA_JUSTIFY, //分散對齊（縮排）
};
enum Vertical_Alignment
{
VA_TOP = 1, //edisonx
VA_CENTER, //edisonx
VA_BOTTOM, //edisonx
VA_JUSTIFYPARA, //段落重排（有留白邊，有自動斷行）
VA_JUSTIFY //分散對齊
};
enum Histogram_Chart_Type
{
CT_AREA = 0, //區域
CT_COLUMN, //方柱
CT_CONE, //圓錐
CT_CYLINDER, //圓柱
CT_PYRAMID //金字塔
};
enum Stock_Type
{
ST_HLC = 0, //最高-最低-收盤
ST_OHLC, //開盤-最高-最低-收盤
ST_VHLC, //成交量-最高-最低-收盤
ST_VOHLC //成交量-開盤-最高-最低-收盤
};
class xlsFile
{
COleVariant VOptional, VTRUE, VFALSE;
_Application objApp;
Workbooks objBooks;
_Workbook objBook;
Sheets objSheets;
_Worksheet objSheet,objSheetT;
Range range,col,row;
Interior cell;
Font font;
COleException e;
LPDISPATCH lpDisp;
ChartObjects chartobjects;
ChartObject chartobject;
_Chart xlsChart;
VARIANT var;
Shapes shapes;
//暫存的字串
char buf[200];
char buf1[200];
char buf2[200];
public:
xlsFile();
~xlsFile();
//xlsFile&
//開了檔案之後可以繼續選擇Sheet和命名
xlsFile& New();
xlsFile& Open();
xlsFile& Open(const char*);
void SaveAs(const char*);
void Save();
//void Quit(CString FileName);
void Quit();
void SetVisible(bool);//設定視窗為看得見，並把控制權交給使用者
//----------------------------------------------------
//Sheet操作
long SheetTotal(); //取得 Sheet 個數
void SetSheetName(short, const char*); //由SheetNumber 指定SheetName
CString GetSheetName(short); //由SheetNumber 取得SheetName
xlsFile& SelectSheet(const char*); //由SheetName 選擇Sheet
xlsFile& SelectSheet(short); //由SheetNumber 選擇Sheet
void CopySheet(const char*); //複製SheetName 指定插入Sheet的位置，並指定新Sheet名稱
void CopySheet(short); //複製SheetNumber 指定插入Sheet的位置，並指定名稱
void DelSheet(const char*); //選SheetName 刪除Sheet
void DelSheet(short); //選SheetNumber 刪除Sheet
//-----------------------------------------------------
//Cell計數計算
long GetHorztlStartCell(); // 起始行
long GetVrticlStartCell(); // 起始列
long GetHorztlTotalCell(); // 總行數
long GetVrticlTotalCell(); // 總列數
//-----------------------------------------------------
//xlsFile& 選了格子之後可以繼續下「讀」「寫」的成員函數
//選一格
xlsFile& SelectCell(const char* );
xlsFile& SelectCell(const char* , int );
xlsFile& SelectCell(char,int);
xlsFile& SelectCell(char,char,int);
//選一個範圍
xlsFile& SelectCell(const char* , const char* );
xlsFile& SelectCell(const char* , int ,const char* , int );
xlsFile& SelectCell(char,int,char,int);
xlsFile& SelectCell(char,char,int,char,char,int);
//--------------------------------------------
void ClearCell(); //清除儲存格
xlsFile& SetMergeCells(short vMerge = TRUE,
bool isCenterAcross = true); //合併儲存格（通常會配跨欄置中）
//--------------------------------------------
//對齊
xlsFile& SetHorztlAlgmet(short); //水平對齊
xlsFile& SetVrticlAlgmet(short); //垂直對齊
xlsFile& SetTextAngle(short Angle); //方向-文字角度
xlsFile& AutoNewLine(bool NewLine); //自動換行
//---------------------------------------------
//格線
xlsFile& SetCellBorder(long BoarderStyle = 1,
int BoarderWeight = 2, long BoarderColor = 1); //設定框線粗細和顏色
//---------------------------------------
//儲存格大小
void AutoFitHight(); //自動調整列高
void AutoFitWidth(); //自動調整欄寬
xlsFile& SetCellHeight(float); //設定列高
xlsFile& SetCellWidth(float); //設定欄寬
//---------------------------------------------
//字
xlsFile& SetFont(const char* fontType = "新細明體"); //設定字型（預設新細明體）
xlsFile& SetFontBold(bool isBold = true); //粗體
xlsFile& SetFontStrkthrgh(bool isBold = true); //刪除線
xlsFile& SetFontSize(short fontSize = 12); //設定字體大小（預設12pt）
xlsFile& SetFontColor(short colorIndex = 1); //字型顏色（預設黑色）
//---------------------------------------------
xlsFile& SetCellColor(short);//設定底色
//---------------------------------------------
//（17-32隱藏版也有收進來）
//Microsoft Excel 的顏色排序是依
//紅、橙、黃、綠、藍、靛、紫、灰（y），由深到淺（x）
//不過絕對RGB並沒有規律的存在這個表裡
short SelectColor(short x = 8, short y = 7); //依excel介面的座標選擇顏色
short SelectColor(const char ColorChar = 'W'); //快速版（黑D、白W、紅R、綠G、藍B、黃Y）
//---------------------------------------------
//設定資料進儲存格（存成字串）
//一般版
void SetCell(int);
void SetCell(double);
void SetCell(long);
void SetCell(const char* );
void SetCell(CString );
//自訂細部格式版
void SetCell(const char*, int);
void SetCell(const char*, double);
void SetCell(const char*, long);
//--------------------------------------------
//取值
int GetCell2Int();
CString GetCell2CStr();
double GetCell2Double();
//--------------------------------------------
//排序（依列排序）//edisonx
void Sort(CString IndexCell1, long DeCrement1,
CString IndexCell2 = "", long DeCrement2 = 1,
CString IndexCell3 = "", long DeCrement3 = 1);
//--------------------------------------------
//皆由edisonx提供函數資料
//儲存圖表圖片.bmp（.jpg亦可以）
void SaveChart(CString FullBmpPathName);
//圖表（三類型的函數在每次建立都要使用）
//使用前必須選擇貼上Chart的儲存格範圍
//選擇資料範圍
xlsFile& SelectChartRange(const char* , const char* );
xlsFile& SelectChartRange(const char* , int ,const char* , int );
xlsFile& SelectChartRange(char,int,char,int);
xlsFile& SelectChartRange(char,char,int,char,char,int);
//設定Chart參數
xlsFile& SetChart(short XaxisByToporLeft = 2, bool isLabelVisable = 1,
CString = "" , CString = "" , CString = "" );
//區域、直方、方柱、圓柱、圓錐、金字塔
void InsertHistogramChart(int shapeType = CT_COLUMN, bool is3D = 0,
int isVrticlorHorztlorOther = 0,
int isNone_Stack_Percent = 0);
//其它（特殊圖表）
void InsertBubleChart(bool is3D = 0); //泡泡圖
void InsertDoughnutChart(bool Explode = 0); //圓環圖
void InsertSurfaceChart(bool is3D = 0, bool isWire = 0); //曲面圖
void InsertRadarChart(bool isWire = 0, bool isDot = 1); //雷達圖
void InsertPieChart(bool Explode = 0, int type2Dor3DorOf = 0); //圓餅圖
void InsertLineChart(bool isDot = 1, bool is3D = 0, int isNone_Stack_Percent = 0); //折線圖
void InsertXYScatterChart(bool isDot, bool isLine, bool Smooth); //離散圖
void InsertStockChart(int); //股票圖
//--------------------------------------------
void InsertImage(const char* , float , float ); //插入圖片
void InsertImage(const char* ); //插入圖片（先選取範圍，圖檔必失真）
private:
//在Sheet新增圖表
void xlsFile::NewChart();
//防止任何運算
void operator+(const xlsFile&);
void operator-(const xlsFile&);
void operator*(const xlsFile&);
void operator/(const xlsFile&);
void operator%(const xlsFile&);
void operator=(const xlsFile&);
bool operator<(const xlsFile&);
bool operator>(const xlsFile&);
bool operator>=(const xlsFile&);
bool operator<=(const xlsFile&);
bool operator==(const xlsFile&);
bool operator!=(const xlsFile&);
bool operator&&(const xlsFile&);
bool operator&(const xlsFile&);
bool operator||(const xlsFile&);
bool operator|(const xlsFile&);
bool operator>>(const xlsFile&);
bool operator<<(const xlsFile&);
};
#endif
//----------------------------------------------------------------
//----------------------------------------------------------------
/*******************************************************************
* xlef.cpp *
* 此class由kx Wang設計，並發佈初版 *
* 二版則由Edison.Shih.提供函式，補足初版之不足 *
* *
* 由Edison.Shih.提供的函式補足，會做edisonx的記號 *
* *
* Class由 Visual C++ 6 開發 *
* 適用於Microsoft Excel 2003 於 Microsoft Excel 2003 SP3 測試無誤 *
* 測試平台 Microsoft Windows XP SP3 *
* 2011/7/11 kx Wang *
*******************************************************************/
#include "stdafx.h"
#include "xlef.h"
#include <comdef.h>
xlsFile::xlsFile():
VOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR), VFALSE((short)FALSE), VTRUE((short)TRUE)
{
ZeroMemory(buf,sizeof(buf));
ZeroMemory(buf1,sizeof(buf1));
ZeroMemory(buf2,sizeof(buf2));
//Step 1.叫Excel應用程式
if(!objApp.CreateDispatch("Excel.Application",&e))
{
CString str;
str.Format("Excel CreateDispatch() failed w/err 0x%08lx", e.m_sc);
AfxMessageBox(str, MB_SETFOREGROUND);
}
};
xlsFile::~xlsFile()
{
//objApp.SetUserControl(TRUE); //移至visualable
range.ReleaseDispatch();
//chartobject.ReleaseDispatch();
//chartobjects.ReleaseDispatch();
objSheet.ReleaseDispatch();
objSheets.ReleaseDispatch();
objBook.ReleaseDispatch();
objBooks.ReleaseDispatch();
objApp.ReleaseDispatch();
}
//開新檔案
xlsFile& xlsFile::New()
{
objBooks = objApp.GetWorkbooks();
objBook = objBooks.Add(VOptional);
objSheets = objBook.GetWorksheets();
return *this;
}
//開啟一個已存在的檔案
xlsFile& xlsFile::Open(const char* path)
{
objBooks = objApp.GetWorkbooks();
objBook.AttachDispatch(objBooks.Add(_variant_t(path)));
objBook.Activate();
objSheets = objBook.GetWorksheets();
return *this;
}
void xlsFile::SaveAs(const char* strTableName)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf, "%s", strTableName);
objBook.SaveAs(
COleVariant(buf),
VOptional, VOptional,
VOptional, VOptional,
VOptional, 1,
VOptional, VFALSE,
VOptional, VOptional, VOptional);
}
void xlsFile::Save()
{
objBook.Save();
}
void xlsFile::Quit()
{
objBook.Close(VFALSE,VOptional, VOptional);
objBooks.Close();
objApp.Quit();
}
void xlsFile::SetVisible(bool a)
{
objApp.SetVisible(a); //顯示Excel檔
objApp.SetUserControl(a);//使用者控制後，就不可以自動關閉
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Sheet操作
//取得 Sheet 個數
long xlsFile::SheetTotal()
{
return objSheets.GetCount();//edisonx
}
//-------------------------
//由SheetNumber 指定SheetName
void xlsFile::SetSheetName(short SheetNumber, const char* SheetName)
{
objSheet = objSheets.GetItem(COleVariant(SheetNumber));
objSheet.SetName(SheetName);//設定sheet名稱
}
//由SheetNumber 取得SheetName
CString xlsFile::GetSheetName(short SheetNumber)
{
objSheet = objSheets.GetItem(COleVariant(SheetNumber));
return objSheet.GetName();//edisonx
}
//-------------------------
//由SheetName 選擇Sheet
xlsFile& xlsFile::SelectSheet(const char* SheetName)
{
objSheet = objSheets.GetItem(_variant_t(SheetName));
objSheet.Activate();//edisonx
return *this;
}
//由SheetNumber 選擇Sheet
xlsFile& xlsFile::SelectSheet(short SheetNumber)
{
objSheet = objSheets.GetItem(COleVariant(SheetNumber));
objSheet.Activate();//edisonx
return *this;
}
//-------------------------
//複製SheetName 指定插入Sheet的位置，並指定新Sheet名稱
void xlsFile::CopySheet(const char* SheetName)
{
objSheet.AttachDispatch(objSheets.GetItem(_variant_t(SheetName)),true);
objSheet.Copy(vtMissing,_variant_t(objSheet));
}
//複製SheetNumber 指定插入Sheet的位置，並指定名稱
void xlsFile::CopySheet(short SheetNumber)
{
objSheet.AttachDispatch(objSheets.GetItem(COleVariant(SheetNumber)));
objSheet.Copy(vtMissing,_variant_t(objSheet));
}
//-------------------------
//刪除Sheet
//選SheetName
void xlsFile::DelSheet(const char* SheetName)
{
objSheet = objSheets.GetItem(_variant_t(SheetName));
objSheet.Delete();//edisonx
}
//選SheetNumber
void xlsFile::DelSheet(short SheetNumber)
{
objSheet = objSheets.GetItem(COleVariant(SheetNumber));
objSheet.Delete();//edisonx
}
///////////////////////////////////////////////////////////////////////////////////////////
//Cell操作
//Cell計數計算
// 取得起始列
long xlsFile::GetHorztlStartCell()
{
Range usedrange;
usedrange.AttachDispatch(objSheet.GetUsedRange());
return usedrange.GetColumn();
}
// 取得起始行
long xlsFile::GetVrticlStartCell()
{
Range usedrange;
usedrange.AttachDispatch(objSheet.GetUsedRange());
return usedrange.GetRow();
}
// 取得總列數
long xlsFile::GetHorztlTotalCell()
{
Range usedrange;
usedrange.AttachDispatch(objSheet.GetUsedRange());
range.AttachDispatch(usedrange.GetColumns());
return range.GetCount();
}
// 取得總行數
long xlsFile::GetVrticlTotalCell()
{
Range usedrange;
usedrange.AttachDispatch(objSheet.GetUsedRange());
range.AttachDispatch(usedrange.GetRows());
return range.GetCount();
}
//-------------------------
//Cell格式設定
//-------------------------
//選格子
//選一格
xlsFile& xlsFile::SelectCell(const char* x)
{
range=objSheet.GetRange(COleVariant(x),COleVariant(x));
return *this;
}
xlsFile& xlsFile::SelectCell(const char* x, int y)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf,"%s%d",x,y);
range=objSheet.GetRange(COleVariant(buf),COleVariant(buf));
return *this;
}
//小於Z
xlsFile& xlsFile::SelectCell(char x, int y)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf,"%c%d",x,y);
range=objSheet.GetRange(COleVariant(buf),COleVariant(buf));
return *this;
}
//大於Z，開始選AA
xlsFile& xlsFile::SelectCell(char x1,char x2,int y)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf,"%c%c%d",x1,x2,y);
range=objSheet.GetRange(COleVariant(buf),COleVariant(buf));
return *this;
}
//-------------------------
//選格子
//選範圍
xlsFile& xlsFile::SelectCell(const char* x1,const char* x2)
{
range=objSheet.GetRange(COleVariant(x1),COleVariant(x2));
return *this;
}
xlsFile& xlsFile::SelectCell(const char* x1, int y1, const char* x2, int y2)
{
ZeroMemory(buf1,sizeof(buf1));
ZeroMemory(buf2,sizeof(buf2));
sprintf(buf1,"%s%d",x1,y1);
sprintf(buf2,"%s%d",x2,y2);
range=objSheet.GetRange(COleVariant(buf1),COleVariant(buf2));
return *this;
}
//小於Z
xlsFile& xlsFile::SelectCell(char x1, int y1, char x2, int y2)
{
ZeroMemory(buf1,sizeof(buf1));
ZeroMemory(buf2,sizeof(buf2));
sprintf(buf1,"%c%d",x1,y2);
sprintf(buf2,"%c%d",x1,y2);
range=objSheet.GetRange(COleVariant(buf1),COleVariant(buf2));
return *this;
}
//大於Z，開始選AA
xlsFile& xlsFile::SelectCell(char xA1, char xB1, int y1, char xA2, char xB2, int y2)
{
ZeroMemory(buf1,sizeof(buf1));
ZeroMemory(buf2,sizeof(buf2));
sprintf(buf1,"%c%c%d",xA1,xB1,y1);
sprintf(buf2,"%c%c%d",xA2,xB2,y2);
range=objSheet.GetRange(COleVariant(buf1),COleVariant(buf2));
return *this;
}
//-------------------------
//-------------------------
//清除儲存格
void xlsFile::ClearCell()
{
//先選取一個範圍的儲存格
range.Clear();//edisonx
}
//合併儲存格
xlsFile& xlsFile::SetMergeCells(short vMerge, bool isCenterAcross)
{
//先選取一個範圍的儲存格
range.SetMergeCells(_variant_t(vMerge));
if(isCenterAcross) SetHorztlAlgmet(HA_CENTERACROSS);
return *this;
}
//-------------------------
//-------------------------
//對齊方式
//水平對齊
xlsFile& xlsFile::SetHorztlAlgmet(short position)
{
range.SetHorizontalAlignment(COleVariant(position));
return *this;
}
//垂直對齊
xlsFile& xlsFile::SetVrticlAlgmet(short position)
{
range.SetVerticalAlignment(COleVariant(position));
return *this;
}
//對齊方式的方向幾度（+90~-90）
xlsFile& xlsFile::SetTextAngle(short Angle)
{
range.SetOrientation(COleVariant(Angle));
return *this;
}
//設定文字自動換行
xlsFile& xlsFile::AutoNewLine(bool NewLine)
{
if(NewLine) range.SetWrapText(VTRUE);
else range.SetWrapText(VFALSE);
return *this;
}
//-------------------------
//-------------------------
//設定框線、框線顏色
xlsFile& xlsFile::SetCellBorder(long BoarderStyle, int BoarderWeight, long BoarderColor)
{
range.BorderAround(_variant_t(BoarderStyle), BoarderWeight, BoarderColor,_variant_t((long)RGB(0,0,0)));
return *this;
}
//-------------------------
//-------------------------
//設定欄寬列高
//自動調整列高
void xlsFile::AutoFitWidth()
{
col = range.GetEntireColumn(); //選取某個範圍的一整排
col.AutoFit(); //自動調整一整排的欄寬
}
//自動調整欄寬
void xlsFile::AutoFitHight()
{
row = range.GetEntireRow(); //選取某個範圍的一整排
row.AutoFit(); //自動調整一整排的列高
}
//設定列高
xlsFile& xlsFile::SetCellHeight(float height)
{
range.SetRowHeight(_variant_t(height));
return *this;
}
//設定欄寬
xlsFile& xlsFile::SetCellWidth(float height)
{
range.SetColumnWidth(_variant_t(height));
return *this;
}
//-------------------------
//-------------------------
//設定字型
xlsFile& xlsFile::SetFont(const char* fontType)
{
font = range.GetFont();
font.SetName(_variant_t(fontType));
return *this;
}
//粗體
xlsFile& xlsFile::SetFontBold(bool isBold)
{
font = range.GetFont();
if (isBold) font.SetBold(VTRUE);
else font.SetBold(VFALSE);
return *this;
}
//刪除線
xlsFile& xlsFile::SetFontStrkthrgh(bool isStrike)
{
font = range.GetFont();
if (isStrike) font.SetStrikethrough(VTRUE); //edisonx
else font.SetStrikethrough(VFALSE); //edisonx
return *this;
}
//字型大小
xlsFile& xlsFile::SetFontSize(short fontSize)
{
font = range.GetFont();
font.SetSize(_variant_t(fontSize)); //字型大小pt
return *this;
}
//字型顏色
xlsFile& xlsFile::SetFontColor(short colorIndex)
{
font = range.GetFont();
font.SetColorIndex(_variant_t(colorIndex)); //字色(預設黑色)
return *this;
}
//-------------------------
//-------------------------
//設定底色
xlsFile& xlsFile::SetCellColor(short colorIndex)
{
cell = range.GetInterior(); //取得選取範圍，設定儲存格的記憶體位址
cell.SetColorIndex(_variant_t(colorIndex)); //設定底色（查表）
return *this;
}
//選擇顏色（適合字色和底色）依excel介面的座標選擇顏色
short xlsFile::SelectColor(short x, short y)
{
//Microsoft Excel 的顏色排序是依
//紅、橙、黃、綠、藍、靛、紫、灰（y）
//由深到淺（x）
switch(x)
{
case 1:
if(y == 1) return 1;
else if(y == 2) return 9;
else if(y == 3) return 3;
else if(y == 4) return 7;
else if(y == 5) return 38;
else if(y == 6) return 17;
else if(y == 7) return 38;
break;
case 2:
if(y == 1) return 53;
else if(y == 2) return 46;
else if(y == 3) return 45;
else if(y == 4) return 44;
else if(y == 5) return 40;
else if(y == 6) return 18;
else if(y == 7) return 26;
break;
case 3:
if(y == 1) return 52;
else if(y == 2) return 12;
else if(y == 3) return 43;
else if(y == 4) return 6;
else if(y == 5) return 36;
else if(y == 6) return 19;
else if(y == 7) return 27;
break;
case 4:
if(y == 1) return 51;
else if(y == 2) return 10;
else if(y == 3) return 50;
else if(y == 4) return 4;
else if(y == 5) return 35;
else if(y == 6) return 20;
else if(y == 7) return 28;
break;
case 5:
if(y == 1) return 49;
else if(y == 2) return 14;
else if(y == 3) return 42;
else if(y == 4) return 8;
else if(y == 5) return 34;
else if(y == 6) return 21;
else if(y == 7) return 29;
break;
case 6:
if(y == 1) return 11;
else if(y == 2) return 5;
else if(y == 3) return 41;
else if(y == 4) return 33;
else if(y == 5) return 37;
else if(y == 6) return 22;
else if(y == 7) return 30;
break;
case 7:
if(y == 1) return 55;
else if(y == 2) return 47;
else if(y == 3) return 13;
else if(y == 4) return 54;
else if(y == 5) return 39;
else if(y == 6) return 23;
else if(y == 7) return 31;
break;
case 8:
if(y == 1) return 56;
else if(y == 2) return 16;
else if(y == 3) return 48;
else if(y == 4) return 15;
else if(y == 5) return 2;
else if(y == 6) return 24;
else if(y == 7) return 32;
break;
}
return 2;//預設白色
}
short xlsFile::SelectColor(const char ColorChar)
{
switch(ColorChar)
{
//黑色
case 'D':
case 'd':
return 1;
break;
//白色
case 'W':
case 'w':
return 2;
break;
//紅色
case 'R':
case 'r':
return 3;
break;
//綠色
case 'G':
case 'g':
return 4;
break;
//藍色
case 'B':
case 'b':
return 5;
break;
//黃色
case 'Y':
case 'y':
return 6;
break; }
return 2;//預設白色
}
///////////////////////////////////////////////////////////////////////////////////////////
//Cell操作
//Cell填值
//-------------------------
//SetCell()
void xlsFile::SetCell(int Data)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf,"%d",Data);
range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}
void xlsFile::SetCell(long Data)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf,"%d",Data);
range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}
void xlsFile::SetCell(double Data)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf,"%f",Data);
range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}
void xlsFile::SetCell(const char* Data)
{
ZeroMemory(buf,sizeof(buf));
strcpy(buf,Data);
//sprintf(buf,"%s",Data);
range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}
void xlsFile::SetCell(CString Data)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf,"%s",Data);
range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}
void xlsFile::SetCell(const char* Format, int Data)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf,Format,Data);
range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}
void xlsFile::SetCell(const char* Format, double Data)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf,Format,Data);
range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}
void xlsFile::SetCell(const char* Format, long Data)
{
ZeroMemory(buf,sizeof(buf));
sprintf(buf,Format,Data);
range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t(buf));
}
//-------------------------
CString xlsFile::GetCell2CStr()
{
return (char*)_bstr_t(range.GetItem(_variant_t((long)1), _variant_t((long)1)));
}
int xlsFile::GetCell2Int()
{
COleVariant vResult = range.GetValue2(); //edisonx
vResult.ChangeType(VT_INT); //edisonx
return vResult.intVal; //edisonx
}
double xlsFile::GetCell2Double()
{
COleVariant vResult = range.GetValue2(); //edisonx
vResult.ChangeType(VT_R8); //edisonx
return vResult.dblVal; //edisonx
}
///////////////////////////////////////////////////////////////////////////////////////////
//演算法操作
//排序
void xlsFile::Sort(CString IndexCell1, long DeCrement1,
CString IndexCell2, long DeCrement2,
CString IndexCell3, long DeCrement3)
{
VARIANT key1, key2, key3;
V_VT(&key1) = VT_DISPATCH;
V_DISPATCH(&key1)=objSheet.GetRange(COleVariant(IndexCell1),COleVariant(IndexCell1));
if(IndexCell2.IsEmpty())
{
range.Sort(key1, DeCrement1, VOptional, VOptional, 1, VOptional, 1, 2, //一般選項
VOptional, _variant_t((short)TRUE), //進階
1, 1, 1, 1, 1); //未知選項//edisonx
}
else
{
V_VT(&key2) = VT_DISPATCH;
V_DISPATCH(&key2)=objSheet.GetRange(COleVariant(IndexCell2),COleVariant(IndexCell2));
if(IndexCell3.IsEmpty())
{
range.Sort(key1, DeCrement1, key2, VOptional, DeCrement2, VOptional, 1, 2, //一般選項
VOptional, _variant_t((short)TRUE), //進階
1, 1, 1, 1, 1); //未知選項//edisonx
}
else
{
V_VT(&key3) = VT_DISPATCH;
V_DISPATCH(&key3)=objSheet.GetRange(COleVariant(IndexCell3),COleVariant(IndexCell3));
range.Sort(key1, DeCrement1, key2, VOptional, DeCrement2, key3, DeCrement3, 2, //一般選項
VOptional, _variant_t((short)TRUE), //進階
1, 1, 1, 1, 1); //未知選項//edisonx
}
}
}
///////////////////////////////////////////////////////////////////////////////////////////
//圖表操作
//插入圖表（一條龍code）
void xlsFile::NewChart()
{
//在Sheet新增圖表
lpDisp = objSheet.ChartObjects(VOptional);
chartobjects.AttachDispatch(lpDisp);
//圖表符合儲存格範圍的大小
chartobject = chartobjects.Add(
(float)range.GetLeft().dblVal,
(float)range.GetTop().dblVal,
(float)range.GetWidth().dblVal,
(float)range.GetHeight().dblVal );
//資料來源（範圍left, top預設為 比較Item和Group）
xlsChart.AttachDispatch(chartobject.GetChart());
}
//儲存圖表
//edisonx
void xlsFile::SaveChart(CString FullBmpPathName)
{
xlsChart.Export(LPCTSTR(FullBmpPathName),VOptional,VOptional);
}
//選擇表格資料的範圍
xlsFile& xlsFile::SelectChartRange(const char* x1,const char* x2)
{
NewChart();
lpDisp = objSheet.GetRange(COleVariant(x1),COleVariant(x2));
range.AttachDispatch(lpDisp);
return *this;
}
xlsFile& xlsFile::SelectChartRange(const char* x1, int y1, const char* x2, int y2)
{
NewChart();
ZeroMemory(buf1,sizeof(buf1));
ZeroMemory(buf2,sizeof(buf2));
sprintf(buf1,"%s%d",x1,y1);
sprintf(buf2,"%s%d",x2,y2);
lpDisp = objSheet.GetRange(COleVariant(buf1),COleVariant(buf2));
range.AttachDispatch(lpDisp);
return *this;
}
//小於Z
xlsFile& xlsFile::SelectChartRange(char x1, int y1, char x2, int y2)
{
NewChart();
ZeroMemory(buf1,sizeof(buf1));
ZeroMemory(buf2,sizeof(buf2));
sprintf(buf1,"%c%d",x1,y2);
sprintf(buf2,"%c%d",x1,y2);
lpDisp = objSheet.GetRange(COleVariant(buf1),COleVariant(buf2));
range.AttachDispatch(lpDisp);
return *this;
}
//大於Z，開始選AA
xlsFile& xlsFile::SelectChartRange(char xA1, char xB1, int y1, char xA2, char xB2, int y2)
{
NewChart();
ZeroMemory(buf1,sizeof(buf1));
ZeroMemory(buf2,sizeof(buf2));
sprintf(buf1,"%c%c%d",xA1,xB1,y1);
sprintf(buf2,"%c%c%d",xA2,xB2,y2);
lpDisp = objSheet.GetRange(COleVariant(buf1),COleVariant(buf2));
range.AttachDispatch(lpDisp);
return *this;
}
// 設定表格參數（預設會顯示立體直方圖）
xlsFile& xlsFile::SetChart(short XaxisByToporLeft, bool isLabelVisable, CString TitleString, CString XaxisTitle, CString YaxisTitle)
{
var.vt = VT_DISPATCH;
var.pdispVal = lpDisp;
short LabelVisable = (isLabelVisable) ? TRUE : FALSE ;
xlsChart.ChartWizard(var, // const VARIANT& Source.
COleVariant((short)11), // const VARIANT& fix please, Gallery: 3d Column. 1 or 11 是否轉動3D（3D類適用, 1轉，11不轉）
COleVariant((short)1), // const VARIANT& fix please, Format, use default
COleVariant(XaxisByToporLeft), // const VARIANT& PlotBy: 1.X 2.Y 圖表的x軸要使用 表格的1:X-top還是2:Y-left
COleVariant((short)1), // const VARIANT& Category Labels fix please 不當軸的那個資料，從第幾個格子開始算（比較群組資料數量）
COleVariant((short)1), // const VARIANT& Series Labels. Start X, 不當軸的那個資料，資料名稱要用幾排格子（更改名字）
COleVariant(LabelVisable), // const VARIANT& HasLegend. 是否要顯示群組資料標籤
//以下可不填
_variant_t(COleVariant(TitleString)), // const VARITNT& Title
_variant_t(COleVariant(XaxisTitle)), // const VARIANT& CategoryTitle
_variant_t(COleVariant(YaxisTitle)), // const VARIANT& ValueTitle
VOptional // const VARIANT& ExtraTitle
);
return *this;
}
//插入圖表
void xlsFile::InsertHistogramChart(int shapeType, bool is3D,
int isVrticl_Horztl_Other,
int isNone_Stack_Percent)
{
long ChartType = 51;
if (shapeType == 0)//Area
{
if(!is3D) //2D
{
if(isNone_Stack_Percent == 0) ChartType = 1;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 77;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 76;//有百分比
}
else //3D
{
if(isNone_Stack_Percent == 0) ChartType = -4098;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 78;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 79;//百分比
}
}
else if (shapeType == 1)//直方圖
{
if(isVrticl_Horztl_Other == 0)//直的
{
if(!is3D) //2D
{
if(isNone_Stack_Percent == 0) ChartType = 51;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 52;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 53;//有百分比
}
else //3D
{
if(isNone_Stack_Percent == 0) ChartType = 54;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 55;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 56;//百分比
}
}
else if(isVrticl_Horztl_Other == 1)//橫的
{
if(!is3D) //2D
{
if(isNone_Stack_Percent == 0) ChartType = 57;
else if (isNone_Stack_Percent == 1) ChartType = 58;
else if (isNone_Stack_Percent == 2) ChartType = 59;
}
else //3D
{
if(isNone_Stack_Percent == 0) ChartType = 60;
else if (isNone_Stack_Percent == 1) ChartType = 61;
else if (isNone_Stack_Percent == 2) ChartType = 62;
}
}
else ChartType = -4100; //平面 必3D
}
else if (shapeType == 2) //CONE
{
if(isVrticl_Horztl_Other == 0)//直的
{
if(isNone_Stack_Percent == 0) ChartType = 92;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 93;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 94;//百分比
}
else if(isVrticl_Horztl_Other == 1)//橫的
{
if(isNone_Stack_Percent == 0) ChartType = 95;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 96;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 97;//百分比
}
else ChartType = 98;//平面 必3D
}
else if (shapeType == 3)
{
if(isVrticl_Horztl_Other == 0) //直的
{
if(isNone_Stack_Percent == 0) ChartType = 99;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 100;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 101;//百分比
}
else if(isVrticl_Horztl_Other == 1) //橫的
{
if(isNone_Stack_Percent == 0) ChartType = 102;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 103;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 104;//百分比
}
else ChartType = 105;//平面 必3D
}
else if (shapeType == 4)
{
if(isVrticl_Horztl_Other == 0)//直的
{
if(isNone_Stack_Percent == 0) ChartType = 106;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 107;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 108;//百分比
}
else if(isVrticl_Horztl_Other == 1)//橫的
{
if(isNone_Stack_Percent == 0) ChartType = 109;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 110;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 111;//百分比
}
else ChartType = 112; //平面 必3D
}
xlsChart.SetChartType((long)ChartType);
}
//泡泡
void xlsFile::InsertBubleChart(bool is3D)
{
long ChartType = 51;
ChartType = (!is3D) ? 15 : 87;
xlsChart.SetChartType((long)ChartType);
}
//圓環
void xlsFile::InsertDoughnutChart(bool Explode)
{
long ChartType = 51;
if(!Explode) ChartType = -4120;
else ChartType = 80;
xlsChart.SetChartType((long)ChartType);
}
//曲面
void xlsFile::InsertSurfaceChart(bool is3D, bool isWire)
{
long ChartType = 51;
if (is3D)
{
if (!isWire) ChartType = 83;
else ChartType = 84;
}
else
{
if (!isWire) ChartType = 85;
else ChartType = 86;
}
xlsChart.SetChartType((long)ChartType);
}
//雷達
void xlsFile::InsertRadarChart(bool isWire, bool isDot)
{
long ChartType = 51;
if (isWire)
{
if (!isDot) ChartType = -4151;
else ChartType = 81;
}
else ChartType = 82;
xlsChart.SetChartType((long)ChartType);
}
//圓餅
void xlsFile::InsertPieChart(bool Explode, int type2Dor3DorOf)
{
long ChartType = 51;
if(!Explode)
{
if (type2Dor3DorOf == 0) ChartType = 5;
else if (type2Dor3DorOf == 1) ChartType = -1402;
else if (type2Dor3DorOf == 2) ChartType = 68;
}
else
{
if (type2Dor3DorOf == 0) ChartType = 69;
else if (type2Dor3DorOf == 1) ChartType = 70;
else if (type2Dor3DorOf == 2) ChartType = 71;
}
xlsChart.SetChartType(ChartType);
}
void xlsFile::InsertLineChart(bool isDot, bool is3D, int isNone_Stack_Percent)
{
long ChartType = 51;
if(!is3D)//3D
{
if(!isDot)
{
if(isNone_Stack_Percent == 0) ChartType = 4;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 63;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 64;//有百分比
}
else
{
if(isNone_Stack_Percent == 0) ChartType = 65;//無堆疊
else if (isNone_Stack_Percent == 1) ChartType = 66;//有堆疊
else if (isNone_Stack_Percent == 2) ChartType = 67;//有百分比
}
}
else ChartType = -4101;//3D
xlsChart.SetChartType((long)ChartType);
}
//離散圖
void xlsFile::InsertXYScatterChart(bool isDot, bool isLine, bool Smooth)
{
long ChartType = 51;
if(!isLine) ChartType = -4169;//3D
else
{
if(Smooth)
{
if(isDot) ChartType = 72;
else ChartType = 73;
}
else
{
if(isDot) ChartType = 74;
else ChartType = 75;
}
}
xlsChart.SetChartType((long)ChartType);
}
//股票圖
void xlsFile::InsertStockChart(int StockType)
{
long ChartType = 51;
if (StockType == 0) ChartType = 88;
else if (StockType == 1) ChartType = 89;
else if (StockType == 2) ChartType = 90;
else if (StockType == 3) ChartType = 91;
xlsChart.SetChartType((long)ChartType);
}
///////////////////////////////////////////////////////////////////////////////////////////
//圖片
//插入圖（從檔案）
void xlsFile::InsertImage(const char* FileNamePath, float Width, float Height)
{
shapes = objSheet.GetShapes();
shapes.AddPicture(
FileNamePath, //LPCTSTR Filename
false, //long LinkToFile
true, //long SaveWithDocument
(float)range.GetLeft().dblVal, //float Left
(float)range.GetTop().dblVal, //float Top
Width, //float Width
Height //float Height
);
}
void xlsFile::InsertImage(const char* FileNamePath)
{
shapes = objSheet.GetShapes();
shapes.AddPicture(
FileNamePath, //LPCTSTR Filename
false, //long LinkToFile
true, //long SaveWithDocument
(float)range.GetLeft().dblVal, //float Left
(float)range.GetTop().dblVal, //float Top
(float)range.GetWidth().dblVal, //float Width
(float)range.GetHeight().dblVal //float Height
);
}
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 60.248.125.78