我一直为数据用OLE转存EXCEL那蜗牛般的速度而苦恼，万般无奈之际偶然想到EXCEL可以转为HTML，HTML也可以转成EXCEL，哦！先将数据转成HTML表格（文本），然后利用OLE将HTML转成EXCEL表，这岂不是一条捷径！HTML是文本，所以转成HTML速度飞快。
如何转成HTML？首先您可以在EXCEL中画一个您需要的表格样式，将其转成HTML，那么这个HTML就是您的样板，您可以模仿着将数据写入其中。
代码如下：（要这个工程代码给我发EMAIL：wghsoft@126.com）
//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include "Unit1.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//---------------------------------------------------------------------------
#include<Comobj.hpp>
#include<Utilcls.h>
//---------------------------------------------------------------------------
char * test(int col)
{
int m=(col-1) / 26;
int n=(col-1) % 26;
static char s[5]="";
if(m){
s[0]='A'+m;
s[1]='A'+n;
s[2]=0;
}
else{
s[0]='A'+n;
s[1]=0;
}
ShowMessage( String(s) );
return s;
}
//--------------------------------------------------------
BigData(char * str)
{
double d=atof(str);
if(d>9999999999999.0) return true;
return false;
}
//---------------------------------------------------------------------------
char * CountCol(char *p, int col, int row)
{
int m=(col-1) / 26;
int n=(col-1) % 26;
static char str[10];
char s[5]="";
if(m){
s[0]='A'+m;
s[1]='A'+n;
s[2]=0;
}
else{
s[0]='A'+n;
s[1]=0;
}
// ShowMessage( String(s) );
sprintf(str,"%s%s%d/0", p, s, row);
return str;
}
//---------------------------------------------------
void __fastcall TForm1::SaveToExcel(char * FileName)
{
Variant ex,wk,sht,Range; //ole万能变量，定义excel对象使用
int FieldType[10]={0,0,0,0,2,0,0,0};
try{
ex=CreateOleObject("Excel.Application"); //启动Excel
wk=ex.OlePropertyGet("ActiveWorkBook");//创建工作簿对象
ex.OlePropertyGet("WorkBooks").OleFunction("Add");//添加一个工作薄
sht=ex.OlePropertyGet("ActiveSheet");//创建工作区
}
catch(...)
{
MessageDlg("无法启动Excel,可能尚未安装或文件已经损坏！",mtError,TMsgDlgButtons()<<mbYes,0);
return;
}
int iSRow=0;
int iSCol=0;
char *cRange;
cRange=CountCol("A1:", StringGrid1->ColCount - iSCol, 1);
Range = sht.OlePropertyGet("Range",cRange);
Range.OleFunction("Merge", false);
//Range.OlePropertySet("VerticalAlignment", Edit1->Text.ToInt());//居中
Range.OlePropertySet("HorizontalAlignment",3);//居中
Range.OlePropertyGet("Interior").OlePropertySet("ColorIndex",24);
Range .OlePropertyGet("Borders",1).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",2).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",3).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",4).OlePropertySet("LineStyle",1);
ex.OlePropertySet("Caption","查询结果转入EXCEL：");
ex.OlePropertySet("StatusBar"," 【SQL TOOLS】 WGHSoft.ICBC Tel:0571-87924880 Email: wghsoft@126.com");
sht.OlePropertySet("Name", "核对单");
sht.OlePropertyGet("Cells",1,1).OlePropertySet("Value","测 试 EXCEL");
sht.OlePropertyGet("Cells",1,1).OlePropertyGet("Font").OlePropertySet("Name","华文中宋");
sht.OlePropertyGet("Cells",1,1).OlePropertyGet("Font").OlePropertySet("Size",28);
int i=2;
for(int row=iSRow;row<StringGrid1->RowCount;row++,i++)
for(int j=1, col=iSCol;col<StringGrid1->ColCount;col++,j++){
if(i==2) ex.OlePropertyGet("Cells",j).OlePropertySet("ColumnWidth", StringGrid1->ColWidths[j-1]/7); //宽
if(i%2==0)
sht.OlePropertyGet("Cells",i,j).OlePropertyGet("Interior").OlePropertySet("Color",RGB(213,251,206));
else
sht.OlePropertyGet("Cells",i,j).OlePropertyGet("Interior").OlePropertySet("Color",RGB(188,244,254));
switch(FieldType[col-1]){
case 2:
if(BigData(StringGrid1->Cells[col][row].c_str()))
sht.OlePropertyGet("Cells",i, j).OlePropertySet("NumberFormatLocal","@");
break;
default:
sht.OlePropertyGet("Cells",i, j).OlePropertySet("NumberFormatLocal","@");
break;
}
sht.OlePropertyGet("Cells",i, j).OlePropertySet("Value", Trim(StringGrid1->Cells[col][row]).c_str());
}
cRange=CountCol("A2:", StringGrid1->ColCount - iSCol, i-1);
Range = sht.OlePropertyGet("Range", cRange);
Range .OleProcedure("Select");
Range .OlePropertyGet("Borders",1).OlePropertySet("LineStyle",1);//边框
Range .OlePropertyGet("Borders",2).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",3).OlePropertySet("LineStyle",1);
Range .OlePropertyGet("Borders",4).OlePropertySet("LineStyle",1);
Range .OlePropertySet("RowHeight", 20); //行高
sht.OleFunction("SaveAs",FileName); //表格保存
ex.OlePropertySet("Visible",(Variant)true); //使Excel可见
}
//----------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
double pTimes, cTimes;
pTimes=Now().Val*100000;
SaveToExcel("test.xls");
cTimes=Now().Val*100000;
Label4->Caption=(cTimes-pTimes);
}
//--------------------------------------------------------
void __fastcall TForm1::TestData(int num)
{
StringGrid1->RowCount=2;
String Today=Now().FormatString("yyyy-mm-dd hh:mm:ss");
for(int i=1;i<=num;i++){
StringGrid1->Cells[0][i]=i;
StringGrid1->Cells[1][i]=String(2020+i);
StringGrid1->Cells[2][i]=Today;
StringGrid1->Cells[3][i]="代理发行国债手续费收入";
StringGrid1->Cells[4][i]=( (double)random(8903680))/100;
StringGrid1->Cells[5][i]= random (10000);
StringGrid1->RowCount++;
}
}
void __fastcall TForm1::FormCreate(TObject *Sender)
{
StringGrid1->Cells[0][0]="序号";
StringGrid1->Cells[1][0]="交易码";
StringGrid1->Cells[2][0]="开始时间";
StringGrid1->Cells[3][0]="内容";
StringGrid1->Cells[4][0]="金 额";
StringGrid1->Cells[5][0]="号码";
TestData(UpDown1->Position);
}
//--------------------------------------------------------------------------
int GetPosition(String *str, int start,const String oldStr)
{
int len=str->Length();
String tmp=str->SubString(start,len-start+1);
int p=tmp.Pos(oldStr);
if(!p) return 0;
return p+start-1;
}
//----------------------------------------------------------------------------------
bool TForm1::ReplaceStr(String *str, const String oldStr, const String newStr)
{
static String tmp;
int ps=0;
int s;
if(oldStr.Length()==0) return false;
int nlen=newStr.Length();
int olen=oldStr.Length();
for(int i=0;;i++){
if(ps==0) s=str->Pos(oldStr);
else s=GetPosition(str, ps+nlen-1, oldStr);
if(s==0 || i>20) return true;
int len = str->Length();
tmp = str->SubString(1, s-1) + newStr + str->SubString(s+olen, len);
*str=tmp;
ps=s;
}
}
//-----------------------------------------
/*
18XlFileFormat.xlAddInMicrosoft Office Excel 加载宏(*.xla)
6XlFileFormat.xlCSVCSV (逗号分隔)(*.csv)
22XlFileFormat.xlCSVMac
24XlFileFormat.xlCSVMSDOS
23XlFileFormat.xlCSVWindows
-4158XlFileFormat.xlCurrentPlatformText
7XlFileFormat.xlDBF2DBF 2 (dBASE II) (*.dbf)
8XlFileFormat.xlDBF3DBF 3 (dBASE III) (*.dbf)
11XlFileFormat.xlDBF4DBF 4 (dBASE IV) (*.dbf)
9XlFileFormat.xlDIFDIF (数据交换格式)(*.dif)
16XlFileFormat.xlExcel2Microsoft Excel 2.1 工作表(*.xls)
27XlFileFormat.xlExcel2FarEast
29XlFileFormat.xlExcel3Microsoft Excel 3.0 工作表(*.xls)
33XlFileFormat.xlExcel4Microsoft Excel 4.0 工作表(*.xls)
35XlFileFormat.xlExcel4WorkbookMicrosoft Excel 4.0 工作簿(*.xlw)
39XlFileFormat.xlExcel5Micorosoft Excel 5.0/95 工作薄(*.xls)
39XlFileFormat.xlExcel7Micorosoft Excel 5.0/95 工作薄(*.xls)
43XlFileFormat.xlExcel9795Microsoft Excel 97- Excel 2003 & 5.0/95 工作簿(.*.xls)
44XlFileFormat.xlHtml网页(*.htm;*.html)
26XlFileFormat.xlIntlAddIn
25XlFileFormat.xlIntlMacro
2XlFileFormat.xlSYLKSYLK (符号链接)(*.slk)
17XlFileFormat.xlTemplate模板(*.xlt)
19XlFileFormat.xlTextMac
21XlFileFormat.xlTextMSDOS文本文件(制表符分隔)(*.txt)
36XlFileFormat.xlTextPrinter带格式文本文件(空格分隔)(*.prn)
20XlFileFormat.xlTextWindows
42XlFileFormat.xlUnicodeTextUnicode 文本(*.txt)
45XlFileFormat.xlWebArchive单个文件网页(*.mht;*.mhtml)
14XlFileFormat.xlWJ2WD1WD1 (1-2-3) (*.wd1)
40XlFileFormat.xlWJ3
41XlFileFormat.xlWJ3FJ3
5XlFileFormat.xlWK1WK1 (1-2-3) (*.wk1)
31XlFileFormat.xlWK1ALLWK1,ALL (1-2-3) (*.wk1)
30XlFileFormat.xlWK1FMTWK1,FMT (1-2-3) (*.wk1)
15XlFileFormat.xlWK3WK3 (1-2-3) (*.wk3)
32XlFileFormat.xlWK3FM3WK3,FM3 (1-2-3) (*.wk3)
38XlFileFormat.xlWK4WK4 (1-2-3) (*.wk4)
4XlFileFormat.xlWKSWKS (Works) (*.wks)
-4143XlFileFormat.xlWorkbookNormalMicrosoft Office Excel 工作簿(*.xls)
28XlFileFormat.xlWorks2FarEast
34XlFileFormat.xlWQ1WQ1 (Quattro Pro/DOS) (*.wq1)
46XlFileFormat.xlXMLSpreadsheetXML 表格(*.xml)
*/
#define XlFileFormat -4143
bool HTMLToEXCEL(Char *HFile, Char *sFileName)
{
Variant vExcelApp, vWorkbook, vRange;
char EFile[200];
sprintf(EFile,"%s.xls/0", sFileName);
if(FileExists(EFile))
if(!DeleteFile(EFile)){
ShowMessage("文件被使用中！");
return false;
}
try{
vExcelApp = Variant::CreateObject("Excel.Application");
}
catch(...)
{
MessageDlg("无法启动Excel,可能尚未安装或文件已经损坏！",mtError,TMsgDlgButtons()<<mbYes,0);
return false;
}
//vExcelApp.OlePropertySet("Visible", false);
try{
vExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Open", HFile);
vExcelApp.OlePropertySet("Caption","查询结果转入EXCEL：");
vExcelApp.OlePropertySet("StatusBar"," 【SQL TOOLS】WGHSoft.ICBC Tel:0571-87924880 Email: wghsoft@126.com");
vWorkbook = vExcelApp.OlePropertyGet("ActiveWorkbook");
vWorkbook.OleProcedure("SaveAs", EFile, XlFileFormat);
}
catch(...){
MessageDlg("无法启动Excel！",mtError,TMsgDlgButtons()<<mbYes,0);
::ShellExecute(NULL, "open", HFile, NULL, "", SW_SHOW);
return false;
}
vExcelApp.OlePropertySet("Visible",(Variant)true);
vWorkbook = Unassigned;
vExcelApp = Unassigned;
return true;
}
//-----------------------------------
#define TOTAL_WIDTH 2
#define COL_WIDTH 4
#define CAPTION 6
#define TITLE 9
#define DATA_LINE 11
#define TAILE_COL_WIDTH 14
#define TEMPLATE_HEAD "./template/head.htm"
#define TEMPLATE_BODY "./template/body.htm"
// 彻底抛弃用OLE做EXCEL BY 王光红
//-------------------
bool TForm1::StringGridToHtml(TStringGrid *Sg, char *sFileName)
{
FILE * ofp;
TStringList *HTMList;
int FieldType[10]={0,0,0,0,2,0,0,0};
bool ret;
double pTimes, cTimes;
char FileName[200];
sprintf(FileName,"%s.htm/0", sFileName);
pTimes=Now().Val*100000;
HTMList=new TStringList();
if(FileExists(TEMPLATE_HEAD) && FileExists(TEMPLATE_BODY)) {
HTMList->LoadFromFile(TEMPLATE_BODY);
ret=CopyFile(TEMPLATE_HEAD, FileName, false);
if(!ret){
ShowMessage("文件被编辑中!");
return false;
}
}
else {
ShowMessage("模板文件不存在！");
return false;
}
ofp=fopen(FileName,"a+");
if(ofp==NULL){
ShowMessage("文件无法创建！！");
return false;
}
int Total_Width=0;
double Rate=12*1.1/Sg->Font->Size; //转入EXCEL的字体大小为12
String s, DataClass;
for(int i=0;i<HTMList->Count;i++){
s=HTMList->Strings[i];
switch(i){
case TOTAL_WIDTH: //总宽度
for(int j=0;j<Sg->ColCount;j++)
Total_Width +=(Sg->ColWidths[j]);
Total_Width *=Rate;
ReplaceStr(&s,"$Total_Width1000", String(Total_Width));
ReplaceStr(&s,"$Total_Width750", String(int(Total_Width*0.75)) );
fputs(s.c_str(), ofp);
fputs("/n", ofp);
break;
case COL_WIDTH: //每列宽度
for(int j=0;j<Sg->ColCount;j++){
s=HTMList->Strings[i];
int iSize=int(Sg->ColWidths[j]*Rate);
ReplaceStr(&s,"$Col_Width100", iSize);
ReplaceStr(&s,"$Col_Width3200", String(iSize*32));
ReplaceStr(&s,"$Col_Width75", String(int(iSize*0.75)));
fputs(s.c_str(), ofp);
fputs("/n", ofp);
}
break;
case TAILE_COL_WIDTH:
for(int j=0;j<Sg->ColCount;j++){
s=HTMList->Strings[i];
int iSize=int(Sg->ColWidths[j]*Rate);
ReplaceStr(&s,"$Width100", iSize);
ReplaceStr(&s,"$Width75", String(int(iSize*0.75)));
fputs(s.c_str(), ofp);
fputs("/n", ofp);
}
break;
case CAPTION: //标题
ReplaceStr(&s,"$Caption","测试表");
ReplaceStr(&s,"$Colspan", Sg->ColCount);
ReplaceStr(&s,"$Total_Width1000", String(Total_Width));
ReplaceStr(&s,"$Total_Width750", String(int(Total_Width*0.75)));
fputs(s.c_str(), ofp);
fputs("/n", ofp);
break;
case TITLE:
// fputs("<td height=24 class=xl24 style='height:18.0pt;border-top:none'>序号</td>/n", ofp);
for(int j=0;j<Sg->ColCount;j++){
s=HTMList->Strings[i];
s="<td height=26 class=xl24 style='height:18.0pt'>$Title</td>",
ReplaceStr(&s,"$Title", Sg->Cells[j][0]);
fputs(s.c_str(), ofp);
fputs("/n", ofp);
}
break;
case DATA_LINE: //写入数据
for(int row=1;row<Sg->RowCount;row++){
fflush(ofp);
fputs("<tr height=24 style='mso-height-source:userset;height:18.0pt'>/n", ofp);
for(int col=0;col<Sg->FixedCols;col++)
fprintf(ofp, "<td height=24 class=xl24 style='height:18.0pt'>%s</td>/n", Sg->Cells[col][row].c_str());
for(int col=Sg->FixedCols;col<Sg->ColCount;col++){
s=HTMList->Strings[i];
switch(FieldType[col]){
case ftFloat:
case ftCurrency:
case ftBCD:
case 2:
//金额类型
//if(BigData(Sg->Cells[col][row].c_str()))
if(row%2==0)
DataClass="xl28";
else
DataClass="xl29";
s="<td class=$Data_Class align=right x:num=/"$Money/">$Values</td> ";
ReplaceStr(&s,"$Data_Class", DataClass);
ReplaceStr(&s,"$Money", Sg->Cells[col][row]);
ReplaceStr(&s,"$Values", Sg->Cells[col][row]);
fputs(s.c_str(), ofp); fputs("/n", ofp);
break;
default:
if(row%2==0) DataClass="xl25";
else DataClass="xl26";
ReplaceStr(&s,"$Data_Class", DataClass);
ReplaceStr(&s,"$Values", Sg->Cells[col][row]);
fputs(s.c_str(), ofp); fputs("/n", ofp);
break;
} //switch
}//for
fputs("</tr>/n", ofp);
}//for
break;
default:
fputs(s.c_str(), ofp); fputs("/n", ofp);
break;
} // switch
} //for
fclose(ofp);
ret=HTMLToEXCEL(FileName, sFileName);
cTimes=Now().Val*100000;
Label4->Caption=(cTimes-pTimes);
if(ret)DeleteFile(FileName);
return true;
}
//---------------------------------------------------------------------------
String RemoveExtendName(String sFileName)//去除后缀
{
String dFileName;
if(sFileName.Pos("."))
dFileName=sFileName.SubString(1,sFileName.Pos(".")-1);
else dFileName=sFileName;
return dFileName;
}
//----------------------------------------------------------
void __fastcall TForm1::Button5Click(TObject *Sender)
{
String FileName=RemoveExtendName("c:/wsj.htm");
StringGridToHtml(StringGrid1, FileName.c_str());
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button2Click(TObject *Sender)
{
TestData(UpDown1->Position);
}
//---------------------------------------------------------------------------
扫一扫
349
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
