“从HTML到EXCEL”——数据转存EXCEL的新思路

原创 2005年05月27日 21:13:00

        我一直为数据用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);
}
//---------------------------------------------------------------------------

保存HTML中的表格数据到Excel

//AutomateExcel方法是保存HTML中的表格数据到Excelfunction AutomateExcel(){  // Start Excel and get Application ob...
  • suleil1
  • suleil1
  • 2015年10月28日 16:56
  • 558

完善Borland C++ Builder可视化控件功能三例

完善Borland C++ Builder可视化控件功能三例王光红笔者在编程中积累了点滴经验,与各位交流,共同提高。一.        让PageControl的TabSheet“弹出来”弹出来的效果...
  • wgh166
  • wgh166
  • 2003年07月15日 07:20
  • 1249

下拉框 ComboBox 的使用

组合框控件简介        组合框其实就是把一个编辑框和一个列表框组合到了一起,分为三种:简易(Simple)组合框、下拉式(Dropdown)组合框和下拉列表式(Drop List)组合框。...

Excel导入easyui dataGrid数据批量保存新思路

 dataGrid数据批量保存,如果是一位老司机可能要这么做: 第一步、先把数据从excel读取,然后转化为json格式 第二步、把json数据JSON.parse(jsonDa...

将excel转存为xml数据

1、从网站上下载tinyxml,下载之后解压打开文件夹,里面有一些测试例子,tinyxml.sln支持vs2010了都,不管这些,我们需要的是那个xml类库。 使用tinyxml我们只需要用到的...

ASP.NET读取Excel中的数据转存到数据库(二)

上回说到读取excel表格的时候,读取到的第一个表在实际运行中并不是Sheet1这个表,可能是一个中文表名,也可能是Sheet1。所以首先可以改进这个地方,代码如下: DataTable sheets...

ASP.NET读取Excel中的数据转存到数据库(一)

【问题描述】 近日需要做一些数据仓库的内容,发现数据库搭好了以后,所有的数据文件都是Excel存储的。然而数据又是及其繁杂,所以在创建好了事实表和维度表以后,准备自己写一个代码将Excel中多维的数据...

PB导入excel转存txt

  • 2011年09月08日 22:28
  • 1004B
  • 下载

【我要写框架之导入Excel数据至数据库】——思路整理

说明 背景: 导入Excel数据到数据库成为目前项目中很常用的技术,整理以前的实现发现:在原来系统中的导入,没有实现方法复用,如果要实现某一处数据的导入,需要将原来的代码复制过去、修修改改,实现过程较...

doc,excel,ppt转存pdf并预览

需要jar包: 下载aspose-words-15.8.0-jdk16.jar包http://pan.baidu.com/s/1nvbJwnv 下载aspose-cells-8.5.2.jar包 h...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:“从HTML到EXCEL”——数据转存EXCEL的新思路
举报原因:
原因补充:

(最多只允许输入30个字)