4,011
社区成员
发帖
与我相关
我的任务
分享
void CCreatexlsxDlg::OnBnClickedButtonwrite()
{
CString strFile = _T("D:\\WriteToExcelTest.xlsx");
COleVariant covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
CApplication app;
CWorkbook book;
CWorkbooks books;
CWorksheet sheet;
CWorksheets sheets;
CRange range;
CFont font;
if (!app.CreateDispatch(_T("Excel.Application")))
{
MessageBox(_T("Error!Creat Excel Application Server Faile!"));
}
books = app.get_Workbooks();
//books.AttachDispatch(app.get_Workbooks());可代替上面一行
book = books.Add(covOptional);
//book.AttachDispatch(books.Add(covOptional),true); 可代替上面一行
sheets=book.get_Worksheets();
//sheets.AttachDispatch(book.get_Worksheets(),true); 可代替上面一行
sheet = sheets.get_Item(COleVariant((short)1));
//sheet.AttachDispatch(sheets.get_Item(_variant_t("sheet1")),true); 可代替上面一行
//下面两行,是向A1中写入"Yeah!I can write data to excel!"
range = sheet.get_Range(COleVariant(_T("A1")),COleVariant(_T("A1")));
range.put_Value2(COleVariant(_T("Yeah!I can write data to excel!")));
//下面是向第二行的前十个单元格中输入1到10,十个数字
for(long i=1;i<11;i++)
range.put_Item(_variant_t((long)2),_variant_t((long)i),_variant_t((long)i));
//设置列宽
range = sheet.get_Range(COleVariant(_T("A1")),COleVariant(_T("J1")));
range.put_ColumnWidth(_variant_t((long)5));
//显示表格
app.put_Visible(TRUE);
//保存
book.SaveCopyAs(COleVariant(strFile));
book.put_Saved(true);
//结尾,释放
book.ReleaseDispatch();
books.ReleaseDispatch();
app.ReleaseDispatch();
app.Quit();
}
if (m_bExcel) // If file is an Excel spreadsheet
{
m_Database->OpenEx(m_sDsn, CDatabase::noOdbcDialog);
if (m_bAppend)
{
// Delete old sheet if it exists
m_stempString= "[" + m_sSheetName + "$A1:IV65536]";
m_stempSql.Format ("DROP TABLE %s", m_stempString);
try
{
m_Database->ExecuteSQL(m_stempSql);
}
catch(CDBException *e)
{
m_sLastError = e->m_strError;
m_Database->Close();
return false;
}
// Create new sheet
m_stempSql.Format("CREATE TABLE [%s$A1:IV65536] (", m_sSheetName);
for (int j = 0; j < m_aFieldNames.GetSize(); j++)
{
m_stempSql = m_stempSql + "[" + m_aFieldNames.GetAt(j) +"]" + " char(255), ";
}
m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
m_stempSql += ")";
}
else
{
// Create new sheet
m_stempSql.Format("CREATE TABLE [%s] (", m_sSheetName);
for (int i = 0; i < m_aFieldNames.GetSize(); i++)
{
m_stempSql = m_stempSql + "[" + m_aFieldNames.GetAt(i) +"]" + " char(255), ";
}
m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
m_stempSql += ")";
}
try
{
m_Database->ExecuteSQL(m_stempSql);
if (!m_bAppend)
{
m_dTotalColumns = m_aFieldNames.GetSize();
m_bAppend = true;
}
}
catch(CDBException *e)
{
m_sLastError = e->m_strError;
m_Database->Close();
return false;
}
// Save changed data
for (int k = 1; k < m_dTotalRows; k++)
{
ReadRow(m_atempArray, k+1);
// Create Insert SQL
m_stempSql.Format("INSERT INTO [%s$A1:IV%d] (", m_sSheetName, k);
for (int i = 0; i < m_atempArray.GetSize(); i++)
{
m_stempString.Format("[%s], ", m_aFieldNames.GetAt(i));
m_stempSql = m_stempSql + m_stempString;
}
m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
m_stempSql += ") VALUES (";
for (int j = 0; j < m_atempArray.GetSize(); j++)
{
m_stempString.Format("'%s', ", m_atempArray.GetAt(j));
m_stempSql = m_stempSql + m_stempString;
}
m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
m_stempSql += ")";
// Add row
try
{
m_Database->ExecuteSQL(m_stempSql);
}
catch(CDBException *e)
{
m_sLastError = e->m_strError;
m_Database->Close();
return false;
}
}
m_Database->Close();
m_bTransaction = false;
return true;