//读Xls
BOOL ReadFromExcel()
{
_ConnectionPtr pConn; CString strConn;
_RecordsetPtr pRs; CString strSql;
try{
pConn.CreateInstance("ADODB.Connection");
pRs.CreateInstance("ADODB.Recordset");
strConn.Format(_T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Excel\\SysDic.mdb"));
pConn->Open((_bstr_t)strConn, "", "", adModeUnknown);
//strSql.Format(_T("Delete * from Region"));
//pConn->Execute((_bstr_t)strSql, NULL, adCmdText);
}
catch (_com_error e){
AfxMessageBox(e.Description());
}
CApplication app;
CWorkbooks books;CWorkbook book;
CWorksheets sheets;CWorksheet sheet;
CRange range,cell;
CString strFileName;
strFileName.Format(_T("D:\\Excel\\县市名称.xls"));
LPDISPATCH lpDisp = NULL;
if(!app.CreateDispatch(_T("Excel.Application"))){
AfxMessageBox(_T("Excel进程创建失败!"));
return FALSE;
}
app.put_Visible(FALSE);
books.AttachDispatch(app.get_Workbooks());
lpDisp = books.Open(strFileName,_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));
book.AttachDispatch(lpDisp);
sheets.AttachDispatch(book.get_Worksheets());
sheet = book.get_ActiveSheet();
//此时的Range是一个区域
range = sheet.get_UsedRange();
range.AttachDispatch(range.get_Rows());
long RowNum = range.get_Count();
range.AttachDispatch(range.get_Columns());
long ColNum = range.get_Count();
CString strTmp; strTmp.Format(_T("Row = %d, Col = %d"), RowNum, ColNum);
AfxMessageBox(strTmp);
//此时的区域是单元格的集合
range.AttachDispatch(sheet.get_Cells()); VARIANT vtValue; CString strValue;
CString strCode, strRegion; strCode.Format(_T("")); strRegion.Format(_T(""));
for(int i = 2; i <= RowNum; i++){
strCode.Format(_T("")); strRegion.Format(_T(""));
cell.AttachDispatch(range.get_Item(COleVariant((long)(i)), COleVariant((long)2)).pdispVal);
vtValue = cell.get_Text();
strRegion = vtValue.bstrVal;
cell.AttachDispatch(range.get_Item(COleVariant((long)(i)), COleVariant((long)3)).pdispVal);
vtValue = cell.get_Text();
strCode = vtValue.bstrVal;
try{
strSql.Format(_T("Insert Into CityTown(Code, Region) Values('%s', '%s')"), strCode, strRegion);
pConn->Execute((_bstr_t)strSql, NULL, adCmdText);
}
catch(_com_error e){
AfxMessageBox(e.Description());
}
}
app.put_AlertBeforeOverwriting(false);
app.put_DisplayAlerts(false);
cell.ReleaseDispatch();
range.ReleaseDispatch();
sheet.ReleaseDispatch();
sheets.ReleaseDispatch();
book.ReleaseDispatch();
books.ReleaseDispatch();
app.Quit();
app.ReleaseDispatch();
try{
pConn->Close();
}
catch (_com_error e){
AfxMessageBox(e.Description());
}
AfxMessageBox(_T("Save Successfully !"));
return TRUE;
}
//写Xls
BOOL WriteIntoExcel()
{
CApplication app;
CWorkbooks books;CWorkbook book;
CWorksheets sheets;CWorksheet sheet;
CRange range,cols;
COleVariant opt((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
if(!app.CreateDispatch(_T("Excel.Application"))){
AfxMessageBox(_T("Excel进程创建失败!"));
return FALSE;
}
app.put_Visible(FALSE); //设置app属性,及其进行如下操作
app.put_UserControl(TRUE);
books = app.get_Workbooks();
book = books.Add(COleVariant(strExcel));//加载模板
sheets = book.get_Worksheets();
sheet = sheets.get_Item(COleVariant((short)1));//获取sheet1
_RecordsetPtr pRs; CString strSql, strAddr, strValue; int nIndex = 0;
try{
pRs.CreateInstance(_T("ADODB.Recordset"));
strSql.Format(_T("Select * from Parcels Where Done = 1 Order by Pno"));
pRs->Open((_bstr_t)strSql, pConn.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText);
while(!pRs->adoEOF){
strAddr.Format(_T("A%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("Pno"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("B%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("Pid")); } catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("C%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("Area"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("D%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("OwnShip"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("E%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("DoName"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("F%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("DoId"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("G%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("Dept"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("H%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("UseName"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("I%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("Card"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("J%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("UseId"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("K%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("GainWay"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("L%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("Plant"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("M%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("Note"));} catch(...){}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
strAddr.Format(_T("N%d"), nIndex+3);
try{strValue = pRs->GetCollect(_T("Nation"));} catch(...){ /*AfxMessageBox(e.Description());*/}
range = sheet.get_Range(COleVariant(strAddr),COleVariant(strAddr)); range.put_Value2(COleVariant(strValue));
strValue.Format(_T(""));
pRs->MoveNext(); nIndex++;
}
pRs->Close();
}
catch(_com_error e){
AfxMessageBox(e.Description());
}
range = sheet.get_UsedRange();//加载已使用的单元格
//设置齐方式为水平垂直居中
//水平对齐:默认=1,居中=-4108,左=-4131,右=-4152
//垂直对齐:默认=2,居中=-4108,左=-4160,右=-4107
range.put_HorizontalAlignment(_variant_t((long)-4108));
range.put_VerticalAlignment(_variant_t((long)-4108));
///设置整体的字体、字号及颜色//
//Font font;
//font.AttachDispatch(m_ExlRge.GetFont());
//font.SetName(_variant_t("宋体"));//字体
//font.SetColorIndex(_variant_t((long)11));//字的颜色
//font.SetSize(_variant_t((long)12));//字号
///设置标题字体及颜色//
//font.SetBold(_variant_t((long)1));//粗体
// font.SetSize(_variant_t((long)13));
//font.SetColorIndex(_variant_t((long)2));
//保存
book.SaveCopyAs(COleVariant(strExcel));
book.put_Saved(true);
range.ReleaseDispatch();
sheet.ReleaseDispatch();
sheets.ReleaseDispatch();
book.ReleaseDispatch();
books.ReleaseDispatch();
app.Quit();
app.ReleaseDispatch();
return TRUE;
}