自制Excel表格模板代码
来源：互联网 发布：2016茶叶大数据 编辑：程序博客网 时间：2024/04/30 09:32
最近开发伐区设计管理系统，使用表格进行打印，做了一个模板文件，但是总是不太好用，只好重新设置一个空表格，代码如下：
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();
}
- 自制Excel表格模板代码
- java写入excel表格模板
- excel表格按地市汇总工具，自制的，哈哈
- 导出EXCEL表格的代码
- java代码操作Excel表格
- jxl解析excel表格代码
- Java代码解析Excel表格
- Excel表格上传模板设计问题
- jxls使用模板导出Excel表格
- java excel导出模板代码
- 用WPS自制表格
- vs2010导入导出excel表格代码
- java代码读取Excel表格类
- java代码读取excel表格内容
- PHP实现导入excel表格的代码
- php导出excel表格部分代码
- java poi 导出excel表格 代码
- 表格维护生成器和模板代码
- Rtems简介
- 嵌入式学习指南
- C++多态技术
- Core Java 笔记
- 更新“易语言.飞扬”编译器，改进“遍历循环(foreach)”
- 自制Excel表格模板代码
- 嵌入式初学者学习之门
- 边学边用Mysql(2):索引的作用
- 测试用例评审检查单
- ARM的开发步骤
- Get-UICulture 帮助信息
- C++ 编译器帮你生成的函数
- 嵌入式Linux系统工程师都学些什么？
- CMMI关键字
