利用vc的mfc做的Excel表格处理工具

下面两个文件是逻辑处理文件。已经调通。

CExcelmadetoolDlg.h

在这里插入代码片 // ExcelmadetoolDlg.h : header file // #if !defined(AFX_EXCELMADETOOLDLG_H__9A8C90C3_A3D6_4DCB_BC96_4F62C05EEE78__INCLUDED_) #define AFX_EXCELMADETOOLDLG_H__9A8C90C3_A3D6_4DCB_BC96_4F62C05EEE78__INCLUDED_ #if _MSC_VER > 1000 #pragma once #endif // _MSC_VER > 1000 / // CExcelmadetoolDlg dialog #include "tlhelp32.h" //头文件 class CExcelmadetoolDlg : public CDialog { // Construction public: CExcelmadetoolDlg(CWnd* pParent = NULL); // standard constructor // Dialog Data //{{AFX_DATA(CExcelmadetoolDlg) enum { IDD = IDD_EXCELMADETOOL_DIALOG }; // NOTE: the ClassWizard will add data members here //}}AFX_DATA // ClassWizard generated virtual function overrides //{{AFX_VIRTUAL(CExcelmadetoolDlg) int madeExcel(); void GetEditworth();//获取控件的值 void GetSNAllworth();//获取sn总共的值 long chToLong(const char *ch,int len);//用于处理填入序列号的数据 void StrtoInt(CString *const putStr,int *const putInt);//字符串转换为int,不能改变指针地址 CString strSNAllPath;//用于添加SN表单的路径 CString strADDRPath;//用于添加地址表单的路径 int startLine;//起始行 int EndLine;//终止行 CString StartSN;//起始SN序列号 CString CSLogistics;//物流公司 CString CSLogisticsNumber;//物流单号 int IClientSnNum;//客户SN序列号总数 CString SheetName;//表单名字 int impliedSNAllLine;//默认打印总的表格数字 protected: virtual void DoDataExchange(CDataExchange* pDX); // DDX/DDV support //EDIT控件 CEdit* pBoxStart; CEdit* pBoxEnd; //}}AFX_VIRTUAL // Implementation protected: HICON m_hIcon; // Generated message map functions //{{AFX_MSG(CExcelmadetoolDlg) virtual BOOL OnInitDialog(); afx_msg void OnSysCommand(UINT nID, LPARAM lParam); afx_msg void OnPaint(); afx_msg HCURSOR OnQueryDragIcon(); afx_msg void OnOk2(); afx_msg void Onaddr(); virtual void OnOK(); //}}AFX_MSG DECLARE_MESSAGE_MAP() }; //{{AFX_INSERT_LOCATION}} // Microsoft Visual C++ will insert additional declarations immediately before the previous line. #endif // !defined(AFX_EXCELMADETOOLDLG_H__9A8C90C3_A3D6_4DCB_BC96_4F62C05EEE78__INCLUDED_)

CExcelmadetoolDlg.cpp

在这里插入代码片 // ExcelmadetoolDlg.cpp : implementation file // #include "stdafx.h" #include "Excelmadetool.h" #include "ExcelmadetoolDlg.h" #include <comdef.h> #include "excel.h" #include <shlwapi.h> #include <sstream> #include <string> #include <iostream> #include <stdlib.h> #include <math.h> #include <limits.h> #include <windows.h> #pragma comment(lib,"shlwapi.lib") //如果没有这行，会出现link错误 //#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF", "adoEOF") #ifdef _DEBUG #define new DEBUG_NEW #undef THIS_FILE static char THIS_FILE[] = __FILE__; #endif //要操作表，必须先逐步获取Workbooks->Workbook->Worksheets->Worksheet->Range //并设置全局变量 _Application g_app; Workbooks g_books; _Workbook g_book; Sheets g_sheets; //低版本Office请将这改为 WorkSheets _Worksheet g_sheet; Range g_range; Font font; int SNnum=0; / // CAboutDlg dialog used for App About class CAboutDlg : public CDialog { public: CAboutDlg(); // Dialog Data //{{AFX_DATA(CAboutDlg) enum { IDD = IDD_ABOUTBOX }; //}}AFX_DATA // ClassWizard generated virtual function overrides //{{AFX_VIRTUAL(CAboutDlg) protected: virtual void DoDataExchange(CDataExchange* pDX); // DDX/DDV support //}}AFX_VIRTUAL // Implementation protected: //{{AFX_MSG(CAboutDlg) //}}AFX_MSG DECLARE_MESSAGE_MAP() }; CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD) { //{{AFX_DATA_INIT(CAboutDlg) //}}AFX_DATA_INIT } void CAboutDlg::DoDataExchange(CDataExchange* pDX) { CDialog::DoDataExchange(pDX); //{{AFX_DATA_MAP(CAboutDlg) //}}AFX_DATA_MAP } BEGIN_MESSAGE_MAP(CAboutDlg, CDialog) //{{AFX_MSG_MAP(CAboutDlg) // No message handlers //}}AFX_MSG_MAP END_MESSAGE_MAP() / // CExcelmadetoolDlg dialog CExcelmadetoolDlg::CExcelmadetoolDlg(CWnd* pParent /*=NULL*/) : CDialog(CExcelmadetoolDlg::IDD, pParent) { //{{AFX_DATA_INIT(CExcelmadetoolDlg) // NOTE: the ClassWizard will add member initialization here //}}AFX_DATA_INIT // Note that LoadIcon does not require a subsequent DestroyIcon in Win32 m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME); } void CExcelmadetoolDlg::DoDataExchange(CDataExchange* pDX) { CDialog::DoDataExchange(pDX); //{{AFX_DATA_MAP(CExcelmadetoolDlg) // NOTE: the ClassWizard will add DDX and DDV calls here //}}AFX_DATA_MAP } BEGIN_MESSAGE_MAP(CExcelmadetoolDlg, CDialog) //{{AFX_MSG_MAP(CExcelmadetoolDlg) ON_WM_SYSCOMMAND() ON_WM_PAINT() ON_WM_QUERYDRAGICON() ON_BN_CLICKED(IDOK2, OnOk2) ON_BN_CLICKED(IDOK3, Onaddr) //}}AFX_MSG_MAP END_MESSAGE_MAP() / // CExcelmadetoolDlg message handlers BOOL CExcelmadetoolDlg::OnInitDialog() { CDialog::OnInitDialog(); // Add "About..." menu item to system menu. // IDM_ABOUTBOX must be in the system command range. ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX); ASSERT(IDM_ABOUTBOX < 0xF000); CMenu* pSysMenu = GetSystemMenu(FALSE); if (pSysMenu != NULL) { CString strAboutMenu; strAboutMenu.LoadString(IDS_ABOUTBOX); if (!strAboutMenu.IsEmpty()) { pSysMenu->AppendMenu(MF_SEPARATOR); pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu); } } // Set the icon for this dialog. The framework does this automatically // when the application's main window is not a dialog SetIcon(m_hIcon, TRUE); // Set big icon SetIcon(m_hIcon, FALSE); // Set small icon // TODO: Add extra initialization here //绑定控件 pBoxStart = (CEdit*) GetDlgItem(IDC_EDIT1); pBoxEnd = (CEdit*) GetDlgItem(IDC_EDIT2); //初始化 startLine=0; EndLine=0; //给定控件默认值 pBoxStart-> SetWindowText( "2" ); pBoxEnd-> SetWindowText( "2" ); AfxOleInit(); if(!g_app.CreateDispatch("Excel.Application")) { AfxMessageBox("无法启动Excel服务器"); return; } SetWindowText("Excel表格处理工具"); return TRUE; // return TRUE unless you set the focus to a control } void CExcelmadetoolDlg::OnSysCommand(UINT nID, LPARAM lParam) { if ((nID & 0xFFF0) == IDM_ABOUTBOX) { CAboutDlg dlgAbout; dlgAbout.DoModal(); } else { CDialog::OnSysCommand(nID, lParam); } } // If you add a minimize button to your dialog, you will need the code below // to draw the icon. For MFC applications using the document/view model, // this is automatically done for you by the framework. void CExcelmadetoolDlg::OnPaint() { if (IsIconic()) { CPaintDC dc(this); // device context for painting SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0); // Center icon in client rectangle int cxIcon = GetSystemMetrics(SM_CXICON); int cyIcon = GetSystemMetrics(SM_CYICON); CRect rect; GetClientRect(&rect); int x = (rect.Width() - cxIcon + 1) / 2; int y = (rect.Height() - cyIcon + 1) / 2; // Draw the icon dc.DrawIcon(x, y, m_hIcon); } else { CDialog::OnPaint(); } } // The system calls this to obtain the cursor to display while the user drags // the minimized window. HCURSOR CExcelmadetoolDlg::OnQueryDragIcon() { return (HCURSOR) m_hIcon; } void CExcelmadetoolDlg::OnOk2() { // TODO: Add your control notification handler code here COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); CFileDialog dlg(true,"配置文件",NULL,0, "表格文件(.xls)|*.xls|表格文件(.xlsx)|*.xlsx"); //打开文件 if(IDOK==dlg.DoModal()) strSNAllPath =dlg.GetPathName(); //没有添加提示，工作簿创建 // if(!g_app.CreateDispatch("Excel.Application")) // { // AfxMessageBox("无法启动Excel服务器"); // return; // } //利用模板文件建立新文档 g_books.AttachDispatch(g_app.GetWorkbooks(),true); g_book.AttachDispatch(g_books.Add(_variant_t(strSNAllPath)),true); // //获取所有的工作簿 // g_books = g_app.GetWorkbooks(); // // //用来锁定对应的工作簿 // g_books.AttachDispatch(g_app.GetWorkbooks(),true); // g_book = g_books.Open( strSNAllPath,covOptional,covOptional, // covOptional,covOptional,covOptional,covOptional, // covOptional,covOptional,covOptional,covOptional, // covOptional,covOptional,covOptional,covOptional); //得到Worksheets，工作表 g_sheets.AttachDispatch(g_book.GetWorksheets(),true); g_sheet=g_sheets.GetItem(COleVariant((short)1)); //获得使用的区域 g_range.AttachDispatch(g_sheet.GetUsedRange(),true); //获取使用的行数 int usedrowNum=0; g_range.AttachDispatch(g_range.GetRows(),true); usedrowNum=g_range.GetCount(); //获取使用的列数 int usedrowCol=0; g_range.AttachDispatch(g_range.GetColumns(),true); usedrowCol=g_range.GetCount(); //获取第一行第一列的值 COleVariant vResult; g_range.AttachDispatch(g_sheet.GetCells()); g_range.AttachDispatch(g_range.GetItem(COleVariant((long)2), COleVariant((long)1)).pdispVal);//行与列 vResult = g_range.GetValue2(); //获取工作表的名字 CString sheetName=g_sheet.GetName(); //展现文档 // g_app.SetVisible(true); //释放对象 g_books.Close(); g_app.Quit(); g_range.ReleaseDispatch(); g_sheet.ReleaseDispatch(); g_sheets.ReleaseDispatch(); g_book.ReleaseDispatch(); g_books.ReleaseDispatch(); g_app.ReleaseDispatch(); } void CExcelmadetoolDlg::OnOK() { // TODO: Add extra validation here //_Application g_Newapp; //Workbooks g_Newbooks; //_Workbook g_Newbook; //Sheets g_Newsheets; //低版本Office请将这改为 WorkSheets //_Worksheet g_Newsheet; //Range g_Newrange; //Font Newfont; //GetEditworth();//获取EDIT控件的值 if( FAILED( CoInitialize(NULL) ) ) { AfxMessageBox("初始化COM支持库失败!"); } //利用模板文件建立新文档 //g_Newbooks.AttachDispatch(g_Newapp.GetWorkbooks(),true); // g_Newbook.AttachDispatch(g_Newbooks.Add(_variant_t(strADDRPath)),true); // //得到Worksheets //g_Newsheets.AttachDispatch(g_Newbook.GetWorksheets(),true); //得到sheet1 //g_Newsheet.AttachDispatch(g_Newsheets.GetItem(_variant_t("sheet1")),true); //得到全部Cells，此时,g_range是cells的集合 //g_Newrange.AttachDispatch(g_Newsheet.GetCells(),true); //设置1行1列的单元的值 //g_Newrange.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("This Is A Excel Test Program!")); //得到所有的列 //g_Newrange.AttachDispatch(g_Newsheet.GetColumns(),true); //得到第一列 //g_Newrange.AttachDispatch(g_Newrange.GetItem(_variant_t((long)1),vtMissing).pdispVal,true); //设置列宽 //g_Newrange.SetColumnWidth(_variant_t((long)20)); //Range cols; //cols = g_Newrange.GetEntireColumn();//选择整列，并设置宽度为自适应 //cols.AutoFit(); //调用模板中预先存放的宏 //g_app.Run(_variant_t("CopyRow"),_variant_t((long)10), _variant_t(vtMissing),_variant_t(vtMissing), //_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing), //_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing), //_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing), //_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing),_variant_t(vtMissing)); //制作表格 //font=g_range.GetFont(); // g_range=g_sheet.GetRange(COleVariant("A2"),COleVariant("A2")); //g_range.SetFormula(COleVariant("=RAND()*100000")); //g_range.SetNumberFormat(COleVariant("$0.00")); //制作表格内容 //填写标题. // g_range = g_sheet.GetRange(COleVariant("A1"),COleVariant("A1")); // g_range.SetNumberFormatLocal(COleVariant("列1")); // g_range = g_sheet.GetRange(COleVariant("B1"),COleVariant("B1")); // g_range.SetNumberFormatLocal(COleVariant("列2")); // g_range = g_sheet.GetRange(COleVariant("C1"),COleVariant("C1")); // g_range.SetNumberFormatLocal(COleVariant("列3")); // g_range = g_sheet.GetRange(COleVariant("D1"),COleVariant("D1")); // g_range.SetNumberFormatLocal(COleVariant("列4")); // g_range = g_sheet.GetRange(COleVariant("E1"),COleVariant("E1")); // g_range.SetNumberFormatLocal(COleVariant("列5")); // g_Newrange.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("SN序列号")); // g_Newrange.SetItem(_variant_t((long)1),_variant_t((long)2),_variant_t("物流单号")); // //获取所有的工作簿 COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); if(!g_app.CreateDispatch("Excel.Application")) { AfxMessageBox("无法启动Excel服务器"); return; } //为了防止用户多点 MessageBox("正在写入,请稍后！"); g_books = g_app.GetWorkbooks(); //用来锁定对应的工作簿 g_books.AttachDispatch(g_app.GetWorkbooks(),true); g_book.AttachDispatch(g_books.Add(_variant_t(strADDRPath)),true); /* g_book = g_books.Open( strADDRPath,covOptional,covOptional, covOptional,covOptional,covOptional,covOptional, covOptional,covOptional,covOptional,covOptional, covOptional,covOptional,covOptional,covOptional); */ //得到Worksheets，工作表 g_sheets.AttachDispatch(g_book.GetWorksheets(),true); g_sheet=g_sheets.GetItem(COleVariant((short)1)); //获得使用的区域 g_range.AttachDispatch(g_sheet.GetUsedRange(),true); //获取使用的行数 int usedrowNum=0; g_range.AttachDispatch(g_range.GetRows(),true); usedrowNum=g_range.GetCount(); //获取使用的列数 int usedrowCol=0; g_range.AttachDispatch(g_range.GetColumns(),true); usedrowCol=g_range.GetCount(); //获取收货公司表单 //获取第2行第6列的值 GetEditworth();//函数出现问题 //取值 // CString strStart; // CString strEnd; // // pBoxStart-> GetWindowText(strStart); // pBoxEnd-> GetWindowText(strEnd); // startLine= atoi( LPCTSTR(strStart) ); // EndLine= atoi( LPCTSTR(strEnd) ); for(int i=startLine;i<EndLine+1;i++) { //获取第I行表单总数 // COleVariant vResultSNALL; // g_range.AttachDispatch(g_sheet.GetCells()); // g_range.AttachDispatch(g_range.GetItem(COleVariant((long)i), COleVariant((long)6)).pdispVal);//行与列 // vResultSNALL = g_range.GetValue2(); _variant_t var; CString tmpStr;//存储每个格子的数据 g_range.AttachDispatch(g_sheet.GetCells()); var =g_range.GetItem(_variant_t((long)i),_variant_t((long)7));//读取第r行c列的数据，注意行和列都是从1开始 tmpStr.Format("%s",(char*)(_bstr_t)var);//转换成CString StrtoInt(&tmpStr,&IClientSnNum); //CString CSnAll; //CSnAll.Format("%f", vResultSNALL.dblVal); //SN起始数 var =g_range.GetItem(_variant_t((long)i),_variant_t((long)8));//读取第r行c列的数据，注意行和列都是从1开始 StartSN.Format("%s",(char*)(_bstr_t)var);//转换成CString //物流公司名字 var =g_range.GetItem(_variant_t((long)i),_variant_t((long)10));//读取第r行c列的数据，注意行和列都是从1开始 CSLogistics.Format("%s",(char*)(_bstr_t)var);//转换成CString //物流单号 var =g_range.GetItem(_variant_t((long)i),_variant_t((long)11));//读取第r行c列的数据，注意行和列都是从1开始 CSLogisticsNumber.Format("%s",(char*)(_bstr_t)var);//转换成CString // COleVariant vResultWuliu; // g_range.AttachDispatch(g_range.GetItem(COleVariant((long)i), COleVariant((long)9)).pdispVal);//行与列 // vResultWuliu = g_range.GetValue2(); // CSLogistics=(BSTR)vResultWuliu.pbstrVal; //CSLogistics.Format("%f", vResultWuliu.dblVal); //给定表单名字 CString strCOmpany; CString strPeopleName; //获取收货公司序号 CString CNumberComper; var =g_range.GetItem(_variant_t((long)i),_variant_t((long)3));//读取第r行c列的数据，注意行和列都是从1开始 CNumberComper.Format("%s",(char*)(_bstr_t)var);//转换成CString //获取公司名字 var =g_range.GetItem(_variant_t((long)i),_variant_t((long)4));//读取第r行c列的数据，注意行和列都是从1开始 strCOmpany.Format("%s",(char*)(_bstr_t)var);//转换成CString // COleVariant vResultGetcompany; // g_range.AttachDispatch(g_range.GetItem(COleVariant((long)i), COleVariant((long)3)).pdispVal);//行与列 // vResultGetcompany = g_range.GetValue2(); // strCOmpany=(BSTR)vResultGetcompany.pbstrVal; //strCOmpany.Format("%f", vResultGetcompany.dblVal); //获取人名 var =g_range.GetItem(_variant_t((long)i),_variant_t((long)4));//读取第r行c列的数据，注意行和列都是从1开始 strPeopleName.Format("%s",(char*)(_bstr_t)var);//转换成CString //除掉人名后面的电话号码 //rPeopleName = strPeopleName.substr(0, strPeopleName.length() - 11); //rPeopleName.erase(strPeopleName.end() - 1); // COleVariant vResultGetPeopleName; // g_range.AttachDispatch(g_range.GetItem(COleVariant((long)i), COleVariant((long)4)).pdispVal);//行与列 // vResultGetPeopleName = g_range.GetValue2(); // strPeopleName=(BSTR)vResultGetcompany.pbstrVal; // strPeopleName.Format("%f", vResultGetPeopleName.dblVal); //表单名字 SheetName=CNumberComper+strCOmpany+strPeopleName; madeExcel(); } //导入数据. // putlonth=Modeworth.GetLength(); // putWorth=Modeworth.Right(putlonth-3); // //总行数 // int AllrowsWorth=100; //madeExcel() ; //int m,n; //int rows=50; //打印预览 g_book.SetSaved(true); g_app.SetVisible(true); // //g_book.PrintPreview(_variant_t(false)); //释放对象 g_books.Close(); g_app.Quit(); g_range.ReleaseDispatch(); g_sheet.ReleaseDispatch(); g_sheets.ReleaseDispatch(); g_book.ReleaseDispatch(); g_books.ReleaseDispatch(); g_app.ReleaseDispatch(); // MessageBox("生产表单完毕!!"); CDialog::OnOK(); } //创建表格 int CExcelmadetoolDlg::madeExcel() { _Application g_Newapp; Workbooks g_Newbooks; _Workbook g_Newbook; Sheets g_Newsheets; //低版本Office请将这改为 WorkSheets _Worksheet g_Newsheet; Range g_Newrange; Font Newfont; if( FAILED( CoInitialize(NULL) ) ) { AfxMessageBox("初始化COM支持库失败!"); } //AfxOleInit();//会引起中断 // //导出 //g_app.ReleaseDispatch(); COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); if(!g_Newapp.CreateDispatch("Excel.Application")) { MessageBox("无法创建Excel应用！"); // exit(1); } //利用模板文件建立新文档 g_Newbooks.AttachDispatch(g_Newapp.GetWorkbooks(),true); //g_Newbook.AttachDispatch(g_Newbooks.Add(_variant_t(strADDRPath)),true); g_Newbook=g_Newbooks.Add(covOptional); //得到Worksheets g_Newsheets.AttachDispatch(g_Newbook.GetWorksheets(),true); //得到sheet1 //g_Newsheet.AttachDispatch(g_Newsheets.GetItem(_variant_t("sheet1")),true); //得到第一个工作表 g_Newsheet=g_Newsheets.GetItem(COleVariant((short)1)); //获得使用的区域 //得到全部Cells，此时,g_range是cells的集合 g_Newrange.AttachDispatch(g_Newsheet.GetCells(),true); //设置1行1列的单元的值 //g_Newrange.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("This Is A Excel Test Program!")); //得到所有的列 g_Newrange.AttachDispatch(g_Newsheet.GetColumns(),true); //得到第一列 //g_Newrange.AttachDispatch(g_Newrange.GetItem(_variant_t((long)1),vtMissing).pdispVal,true); //设置列宽 g_Newrange.SetColumnWidth(_variant_t((long)20)); Range cols; cols = g_Newrange.GetEntireColumn();//选择整列，并设置宽度为自适应 cols.AutoFit(); cols.ReleaseDispatch(); bool TFvisible=false; g_Newapp.SetVisible(TFvisible); //不加文件删不掉 g_Newapp.SetUserControl(TRUE); CString SaveFilePath=_T("D:\\SaveExcel"); CString ExcelName=SheetName+".xlsx"; CString SaveExcelPath="D:\\SaveExcel\\"+ExcelName; if(!PathIsDirectory(SaveFilePath)) { //CreateDirectioy(SaveExcelPath,NULL); AfxMessageBox("存储路径不存在!请在D盘根目录下建立SaveExcel文件夹，注意大小写"); exit(1); } //选择工作表中A1:A1单元格区域 g_Newrange=g_Newsheet.GetRange(COleVariant("A1"),COleVariant("A1")); g_Newrange.SetValue2(COleVariant("SN序列号")); //range.SetValue()必为range.SetValue2() //g_Newrange.SetFormulaR1C1( COleVariant("SN序列号") ); //选择工作表中A1:B1单元格区域 g_Newrange=g_Newsheet.GetRange(COleVariant("B1"),COleVariant("B1")); g_Newrange.SetValue2(COleVariant("物流公司物流单号")); //range.SetValue()必为range.SetValue2() //将CString转化为char数组 char szTemp[30]=""; memcpy(szTemp,StartSN,StartSN.GetLength()); //strcpy_s(szTemp, StartSN); int numIS0;//表示前面有多少0，最后添加 //去掉char数组前面的0 int snLonth=strlen(szTemp); for (int i=0;i<strlen(szTemp);i++) { if (szTemp[i]!='0') { numIS0=i; break; //找出前面有几个0 } } strcpy(szTemp,szTemp+i); // 搬动字符串，版去掉领头的零 unsigned long a = 0; unsigned long b = 1; unsigned long sum = 0; for(i =strlen(szTemp)-1;i >= 0;i--) { a= szTemp[i] - '0'; a =a*b; sum += a; b = b*10; } int szTlong=strlen(szTemp); // CString CTempSNnumber; CTempSNnumber.Format("%s",szTemp); // char ch[]="123456789123456"; // unsigned __int64 LLsum=chToLong(ch,strlen(ch)); // unsigned __int64 mmmm=atoi(CTempSNnumber); // // // unsigned __int64 LStartSN;//= atol(a); // unsigned __int64 LongthSN= _ttol(CTempSNnumber); //unsigned __int64 UI64SNnumber=0; sscanf((LPSTR)(LPCTSTR)CTempSNnumber,"%I64d",&UI64SNnumber); //long filesize; //filesize=atol(m_FileSize.GetBuffer(m_FileSize.GetLength())); // char * pchar; // pchar=(LPSTR)(LPCSTR)StartSN; for(int j=2;j<IClientSnNum+2;j++ ) { char c='0'; CTempSNnumber.Format("%I64d",UI64SNnumber); for(int n=0;n<numIS0;n++) { CTempSNnumber=c+CTempSNnumber; } CTempSNnumber="'"+CTempSNnumber; //StartSN=CTempSNnumber; // StartSN.Format(_T("%I64d"),IStartSN); //g_Newrange.SetItem(_variant_t((long)j),_variant_t((long)0),_variant_t(CTempSNnumber)); // g_Newrange.Select(); // g_Newrange.SetNumberFormatLocal(COleVariant("@")); g_Newrange.SetItem(_variant_t((long)j),_variant_t((long)0),COleVariant(CTempSNnumber)); if(j<3) { g_Newrange.SetItem(_variant_t((long)j),_variant_t((long)1),_variant_t(CSLogistics+CSLogisticsNumber)); } // g_Newrange.SetItem(_variant_t((long)j),_variant_t((long)2),_variant_t(CSLogisticsNumber));//CSLogisticsNumber // //水平对齐：默认＝1,居中＝-4108,左＝-4131,右＝-4152 //垂直对齐：默认＝2,居中＝-4108,左＝-4160,右＝-4107 //设置齐方式为水平垂直居中 // g_Newrange.SetHorizontalAlignment(_variant_t((long)-4131)); // g_Newrange.SetVerticalAlignment(_variant_t((long)-4160)); // //填入的ｓｎ自动加１ UI64SNnumber++; } g_Newbook.SaveAs(COleVariant(SaveExcelPath), covOptional, covOptional, covOptional, covOptional, covOptional, (long)0, covOptional, covOptional, covOptional, covOptional, covOptional); // //释放对象（相当重要！） // g_book.ReleaseDispatch(); // g_books.ReleaseDispatch(); // g_range.ReleaseDispatch(); // g_sheet.ReleaseDispatch(); // // // //退出程序 // g_app.Quit(); // //m_ExlApp一定要释放，否则程序结束后还会有一个Excel进程驻留在内存中，而且程序重复运行的时候会出错 // g_app.ReleaseDispatch(); g_Newbooks.Close(); g_Newapp.Quit(); g_Newrange.ReleaseDispatch(); g_Newsheet.ReleaseDispatch(); g_Newsheets.ReleaseDispatch(); g_Newbook.ReleaseDispatch(); g_Newbooks.ReleaseDispatch(); g_Newapp.ReleaseDispatch(); return 0; } void CExcelmadetoolDlg::Onaddr() { COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); CFileDialog dlg(true,"配置文件",NULL,0, "表格文件(.xls)|*.xls|表格文件(.xlsx)|*.xlsx"); //打开文件 if(IDOK==dlg.DoModal()) strADDRPath =dlg.GetPathName(); //获取所有的工作簿 if(!g_app.CreateDispatch("Excel.Application")) { AfxMessageBox("无法启动Excel服务器"); return; } // g_books = g_app.GetWorkbooks(); //用来锁定对应的工作簿 g_books.AttachDispatch(g_app.GetWorkbooks(),true); g_book.AttachDispatch(g_books.Add(_variant_t(strADDRPath)),true); /* g_book = g_books.Open( strADDRPath,covOptional,covOptional, covOptional,covOptional,covOptional,covOptional, covOptional,covOptional,covOptional,covOptional, covOptional,covOptional,covOptional,covOptional); */ //得到Worksheets，工作表 g_sheets.AttachDispatch(g_book.GetWorksheets(),true); g_sheet=g_sheets.GetItem(COleVariant((short)1)); //获得使用的区域 g_range.AttachDispatch(g_sheet.GetUsedRange(),true); //获取使用的行数 int usedrowNum=0; g_range.AttachDispatch(g_range.GetRows(),true); usedrowNum=g_range.GetCount(); impliedSNAllLine=usedrowNum; //给控件设立建表默认行 CString CTempSNLine; CTempSNLine.Format(_T("%d"),impliedSNAllLine); pBoxEnd-> SetWindowText(CTempSNLine); //获取使用的列数 int usedrowCol=0; g_range.AttachDispatch(g_range.GetColumns(),true); usedrowCol=g_range.GetCount(); //获取工作表的名字 CString sheetName=g_sheet.GetName(); //展现文档 //g_app.SetVisible(true); //释放 g_books.Close(); g_app.Quit(); g_range.ReleaseDispatch(); g_sheet.ReleaseDispatch(); g_sheets.ReleaseDispatch(); g_book.ReleaseDispatch(); g_books.ReleaseDispatch(); g_app.ReleaseDispatch(); } void CExcelmadetoolDlg::GetEditworth() { //取值 CString strStart; CString strEnd; pBoxStart-> GetWindowText(strStart); pBoxEnd-> GetWindowText(strEnd); // int nGetInt = atoi( LPCTSTR(strEnd) ); StrtoInt( &strStart, &startLine) ; StrtoInt( &strEnd, &EndLine) ; } void CExcelmadetoolDlg::StrtoInt(CString * const putStr, int * const putInt) { // const char * pchar=(LPSTR)(LPCSTR)(*putStr); *putInt = atoi(*putStr); } void CExcelmadetoolDlg::GetSNAllworth() { COleVariant vResult; COleVariant covTrue((short)TRUE), covFalse((short)FALSE), covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); if( FAILED( CoInitialize(NULL) ) ) { AfxMessageBox("初始化COM支持库失败!"); } //没有添加提示，工作簿创建 // COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); CFileDialog dlg(true,"配置文件",NULL,0, "表格文件(.xls)|*.xls|表格文件(.xlsx)|*.xlsx"); //打开文件 if(IDOK==dlg.DoModal()) strADDRPath =dlg.GetPathName(); //获取所有的工作簿 g_books = g_app.GetWorkbooks(); //用来锁定对应的工作簿 g_books.AttachDispatch(g_app.GetWorkbooks(),true); g_book = g_books.Open( strADDRPath,covOptional,covOptional, covOptional,covOptional,covOptional,covOptional, covOptional,covOptional,covOptional,covOptional, covOptional,covOptional,covOptional,covOptional); //得到Worksheets，工作表 g_sheets.AttachDispatch(g_book.GetWorksheets(),true); g_sheet=g_sheets.GetItem(COleVariant((short)1)); //获得使用的区域 g_range.AttachDispatch(g_sheet.GetUsedRange(),true); //获取使用的行数 int usedrowNum=0; g_range.AttachDispatch(g_range.GetRows(),true); usedrowNum=g_range.GetCount(); //获取使用的列数 int usedrowCol=0; g_range.AttachDispatch(g_range.GetColumns(),true); usedrowCol=g_range.GetCount(); //获取第2行第一列的值 g_range.AttachDispatch(g_sheet.GetCells()); g_range.AttachDispatch(g_range.GetItem(COleVariant((long)2), COleVariant((long)1)).pdispVal);//行与列 vResult = g_range.GetValue2(); //获取工作表的名字 CString sheetName=g_sheet.GetName(); //展现文档 //g_app.SetVisible(true); //释放 g_books.Close(); g_app.Quit(); g_range.ReleaseDispatch(); g_sheet.ReleaseDispatch(); g_sheets.ReleaseDispatch(); g_book.ReleaseDispatch(); g_books.ReleaseDispatch(); g_app.ReleaseDispatch(); } long CExcelmadetoolDlg::chToLong(const char *ch,int len) { long sum=0; unsigned __int64 aa=LONG_MAX; for(int i=len-1;i>=0;i--) { long mm=long(ch[i]); long nn=mm - 48; sum+=(mm - 48) * pow(10,len-1-i); } return sum; }