最近开发伐区设计管理系统,使用表格进行打印,做了一个模板文件,但是总是不太好用,只好重新设置一个空表格,代码如下:
void CExcelTestDlg::OnBtnUseexcel()
{
_Application AppExcel;
Workbooks books;
_Workbook book;
Worksheets sheets;
_Worksheet sheet;
Range range,cells;
CString ExtPath,strCell1,strCell2;
char path[MAX_PATH];
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
_variant_t vLineStyle;
_variant_t vWeight;
_variant_t vClrIndex;
_variant_t vColor;
if(!AppExcel.CreateDispatch("Excel.Application",NULL))
{
AfxMessageBox("创建Excel服务失败!");
exit(1);
}
GetCurrentDirectory(MAX_PATH,path);
ExtPath=path;
ExtPath+="//MyExcel.xls";
CFileFind filefind;
int iFileExist= filefind.FindFile((LPCTSTR)ExtPath);
books.AttachDispatch(AppExcel.GetWorkbooks(),TRUE);
AppExcel.SetVisible(FALSE);
if(iFileExist)
{
book.AttachDispatch(books.Add(_variant_t(ExtPath)));
}
else
{
book.AttachDispatch(books.Add(covOptional));
}
sheets.AttachDispatch(book.GetSheets(),TRUE);
///
sheet.AttachDispatch(sheets.GetItem(_variant_t((long)1)),TRUE);
sheet.SetName("伐区角规样地表");
cells.AttachDispatch(sheet.GetCells(),true);
range.AttachDispatch(sheet.GetRange(COleVariant("A1"),COleVariant("Q20")),TRUE);
//range.SetItem(_variant_t((long)2),_variant_t((long)1),_variant_t("Hello"));
//设置列宽
range.AttachDispatch(sheet.GetColumns(),true);
range.SetColumnWidth(_variant_t((long)8));
//设置行高
range.AttachDispatch(sheet.GetRows(),true);
range.SetRowHeight(COleVariant((long)20));
/
Range rg,rg2,cell;
///**设置边框**///
vLineStyle.vt=VT_I2; //线的样式:0-无;1-SOLID;2-BIG DOT;3-SMALL DOT;4-DASH DOT;5-DASH DOT DOT;
vLineStyle.lVal=1;
vWeight.vt=VT_I2; //线的宽度
vWeight.lVal=2;
vClrIndex.vt=VT_I2; //COLOR的索引值
vClrIndex.lVal=1; //1-black;2-white;3-red;4-green;5-blue;6-yellow;7-pink;8-dark blue.
vColor.vt=VT_UI4;
vColor.uintVal=RGB(0,0,0);
//绘制横线
for(int i=1;i<10;i++)
{
strCell1.Format("A%d",i*2);
strCell2.Format("H%d",i*2);
rg.AttachDispatch(sheet.GetRange(COleVariant(strCell1),COleVariant(strCell2)),true);
rg.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
strCell1.Format("J%d",i*2);
strCell2.Format("Q%d",i*2);
rg2.AttachDispatch(sheet.GetRange(COleVariant(strCell1),COleVariant(strCell2)),true);
rg2.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
}
//绘制竖线
for(i=0;i<4;i++)
{
strCell1.Format("%c2",(char)('A'+i*2));
strCell2.Format("%c19",(char)('A'+i*2));
rg.AttachDispatch(sheet.GetRange(COleVariant(strCell1),COleVariant(strCell2)),true);
rg.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
strCell1.Format("%c2",(char)('J'+i*2));
strCell2.Format("%c19",(char)('J'+i*2));
rg2.AttachDispatch(sheet.GetRange(COleVariant(strCell1),COleVariant(strCell2)),true);
rg2.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
}
//绘制外框粗线
vWeight.lVal=3;
rg.AttachDispatch(sheet.GetRange(COleVariant("A2"),COleVariant("H19")),TRUE);
rg2.AttachDispatch(sheet.GetRange(COleVariant("J2"),COleVariant("Q19")),TRUE);
rg.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
rg2.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
/
//显示字符串内容
char chItem[14][20];
long iCell;
strcpy(chItem[0],"伐区号");
strcpy(chItem[1],"小班号");
strcpy(chItem[2],"样地号");
strcpy(chItem[3],"树种");
strcpy(chItem[4],"径阶");
strcpy(chItem[5],"记号");
strcpy(chItem[6],"断面积");
strcpy(chItem[7],"平均高");
strcpy(chItem[8],"形高");
strcpy(chItem[9],"公顷蓄积");
strcpy(chItem[10],"出材率");
strcpy(chItem[11],"出材量");
strcpy(chItem[12],"调查人员");
strcpy(chItem[13],"时间");
for(i=0;i<4;i++)
{
iCell=i*2+1;
cell.AttachDispatch(cells.GetItem(COleVariant(long(1)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i]));
iCell=i*2+10;
cell.AttachDispatch(cells.GetItem(COleVariant(long(1)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i]));
}
for(i=0;i<8;i++)
{
iCell=i+1;
cell.AttachDispatch(cells.GetItem(COleVariant(long(2)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i+4]));
cell.SetHorizontalAlignment(COleVariant(long(3)));
cell.SetVerticalAlignment(COleVariant(long(2)));
iCell=i+10;
cell.AttachDispatch(cells.GetItem(COleVariant(long(2)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i+4]));
cell.SetHorizontalAlignment(COleVariant(long(3)));
cell.SetVerticalAlignment(COleVariant(long(2)));
}
for(i=0;i<2;i++)
{
iCell=i*5+1;
cell.AttachDispatch(cells.GetItem(COleVariant(long(20)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i+12]));
iCell=i*5+10;
cell.AttachDispatch(cells.GetItem(COleVariant(long(20)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i+12]));
}
///
//保存文件
book.SaveAs(COleVariant(ExtPath),covOptional,covOptional,covOptional,covOptional,covOptional,1,covOptional,covOptional,covOptional,covOptional);
AppExcel.SetVisible(true);
book.PrintPreview(_variant_t(false));
//
//关闭并释放对象
book.Close(covOptional,covOptional,covOptional);
books.Close();
AppExcel.Quit();
//
cell.ReleaseDispatch();
cells.ReleaseDispatch();
rg.ReleaseDispatch();
rg2.ReleaseDispatch();
range.ReleaseDispatch();
sheet.ReleaseDispatch();
sheets.ReleaseDispatch();
book.ReleaseDispatch();
books.ReleaseDispatch();
AppExcel.ReleaseDispatch();
}
void CExcelTestDlg::OnBtnUseexcel()
{
_Application AppExcel;
Workbooks books;
_Workbook book;
Worksheets sheets;
_Worksheet sheet;
Range range,cells;
CString ExtPath,strCell1,strCell2;
char path[MAX_PATH];
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
_variant_t vLineStyle;
_variant_t vWeight;
_variant_t vClrIndex;
_variant_t vColor;
if(!AppExcel.CreateDispatch("Excel.Application",NULL))
{
AfxMessageBox("创建Excel服务失败!");
exit(1);
}
GetCurrentDirectory(MAX_PATH,path);
ExtPath=path;
ExtPath+="//MyExcel.xls";
CFileFind filefind;
int iFileExist= filefind.FindFile((LPCTSTR)ExtPath);
books.AttachDispatch(AppExcel.GetWorkbooks(),TRUE);
AppExcel.SetVisible(FALSE);
if(iFileExist)
{
book.AttachDispatch(books.Add(_variant_t(ExtPath)));
}
else
{
book.AttachDispatch(books.Add(covOptional));
}
sheets.AttachDispatch(book.GetSheets(),TRUE);
///
sheet.AttachDispatch(sheets.GetItem(_variant_t((long)1)),TRUE);
sheet.SetName("伐区角规样地表");
cells.AttachDispatch(sheet.GetCells(),true);
range.AttachDispatch(sheet.GetRange(COleVariant("A1"),COleVariant("Q20")),TRUE);
//range.SetItem(_variant_t((long)2),_variant_t((long)1),_variant_t("Hello"));
//设置列宽
range.AttachDispatch(sheet.GetColumns(),true);
range.SetColumnWidth(_variant_t((long)8));
//设置行高
range.AttachDispatch(sheet.GetRows(),true);
range.SetRowHeight(COleVariant((long)20));
/
Range rg,rg2,cell;
///**设置边框**///
vLineStyle.vt=VT_I2; //线的样式:0-无;1-SOLID;2-BIG DOT;3-SMALL DOT;4-DASH DOT;5-DASH DOT DOT;
vLineStyle.lVal=1;
vWeight.vt=VT_I2; //线的宽度
vWeight.lVal=2;
vClrIndex.vt=VT_I2; //COLOR的索引值
vClrIndex.lVal=1; //1-black;2-white;3-red;4-green;5-blue;6-yellow;7-pink;8-dark blue.
vColor.vt=VT_UI4;
vColor.uintVal=RGB(0,0,0);
//绘制横线
for(int i=1;i<10;i++)
{
strCell1.Format("A%d",i*2);
strCell2.Format("H%d",i*2);
rg.AttachDispatch(sheet.GetRange(COleVariant(strCell1),COleVariant(strCell2)),true);
rg.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
strCell1.Format("J%d",i*2);
strCell2.Format("Q%d",i*2);
rg2.AttachDispatch(sheet.GetRange(COleVariant(strCell1),COleVariant(strCell2)),true);
rg2.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
}
//绘制竖线
for(i=0;i<4;i++)
{
strCell1.Format("%c2",(char)('A'+i*2));
strCell2.Format("%c19",(char)('A'+i*2));
rg.AttachDispatch(sheet.GetRange(COleVariant(strCell1),COleVariant(strCell2)),true);
rg.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
strCell1.Format("%c2",(char)('J'+i*2));
strCell2.Format("%c19",(char)('J'+i*2));
rg2.AttachDispatch(sheet.GetRange(COleVariant(strCell1),COleVariant(strCell2)),true);
rg2.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
}
//绘制外框粗线
vWeight.lVal=3;
rg.AttachDispatch(sheet.GetRange(COleVariant("A2"),COleVariant("H19")),TRUE);
rg2.AttachDispatch(sheet.GetRange(COleVariant("J2"),COleVariant("Q19")),TRUE);
rg.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
rg2.BorderAround(vLineStyle,vWeight,vClrIndex,vColor);
/
//显示字符串内容
char chItem[14][20];
long iCell;
strcpy(chItem[0],"伐区号");
strcpy(chItem[1],"小班号");
strcpy(chItem[2],"样地号");
strcpy(chItem[3],"树种");
strcpy(chItem[4],"径阶");
strcpy(chItem[5],"记号");
strcpy(chItem[6],"断面积");
strcpy(chItem[7],"平均高");
strcpy(chItem[8],"形高");
strcpy(chItem[9],"公顷蓄积");
strcpy(chItem[10],"出材率");
strcpy(chItem[11],"出材量");
strcpy(chItem[12],"调查人员");
strcpy(chItem[13],"时间");
for(i=0;i<4;i++)
{
iCell=i*2+1;
cell.AttachDispatch(cells.GetItem(COleVariant(long(1)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i]));
iCell=i*2+10;
cell.AttachDispatch(cells.GetItem(COleVariant(long(1)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i]));
}
for(i=0;i<8;i++)
{
iCell=i+1;
cell.AttachDispatch(cells.GetItem(COleVariant(long(2)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i+4]));
cell.SetHorizontalAlignment(COleVariant(long(3)));
cell.SetVerticalAlignment(COleVariant(long(2)));
iCell=i+10;
cell.AttachDispatch(cells.GetItem(COleVariant(long(2)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i+4]));
cell.SetHorizontalAlignment(COleVariant(long(3)));
cell.SetVerticalAlignment(COleVariant(long(2)));
}
for(i=0;i<2;i++)
{
iCell=i*5+1;
cell.AttachDispatch(cells.GetItem(COleVariant(long(20)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i+12]));
iCell=i*5+10;
cell.AttachDispatch(cells.GetItem(COleVariant(long(20)),COleVariant(iCell)).pdispVal);
cell.SetValue2(COleVariant(chItem[i+12]));
}
///
//保存文件
book.SaveAs(COleVariant(ExtPath),covOptional,covOptional,covOptional,covOptional,covOptional,1,covOptional,covOptional,covOptional,covOptional);
AppExcel.SetVisible(true);
book.PrintPreview(_variant_t(false));
//
//关闭并释放对象
book.Close(covOptional,covOptional,covOptional);
books.Close();
AppExcel.Quit();
//
cell.ReleaseDispatch();
cells.ReleaseDispatch();
rg.ReleaseDispatch();
rg2.ReleaseDispatch();
range.ReleaseDispatch();
sheet.ReleaseDispatch();
sheets.ReleaseDispatch();
book.ReleaseDispatch();
books.ReleaseDispatch();
AppExcel.ReleaseDispatch();
}