急！！！VC6.0操作Excel2003？

[chunyou128]

void CTestExcelDlg::OnButton1() 
{
	// TODO: Add your control notification handler code here
	_Application app;
	Workbooks books;
	_Workbook book;
	Worksheets sheets;
	_Worksheet sheet;
	Range range;
	Font font;
	Range cols;
	COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
	
	if( !app.CreateDispatch("Excel.Application") ){
		this->MessageBox("无法创建Excel应用！");
		return;
	}
	books=app.GetWorkbooks();
	book=books.Add(covOptional);
	sheets=book.GetSheets();
	sheet=sheets.GetItem(COleVariant((short)1));
	range=sheet.GetRange(COleVariant("A1"),COleVariant("A1"));
	for (int i=1;i<5;i++)
	{
		for (int j=1;j<5;j++)
		{
			range.SetItem(_variant_t((long)i),_variant_t((long)j),_variant_t("test")); 

		}
	}
	cols=range.GetEntireColumn();
	cols.AutoFit();
	
	app.SetVisible(TRUE);
	app.SetUserControl(TRUE);
}

VC6.0操作Excel2003，我已经能新建并在一个sheet中写入文字了，但我想切换到另一个sheet中继续写入，该怎么做呢？

[xsc2001]

excel默认有三个sheet，你可以通过下面这句代码得到具体的sheet
sheet=sheets.GetItem(COleVariant((short)1));//这里的1表示是第一个sheet，2表示是第二个sheet，3表示第三个sheet

[chunyou128]

谢谢大家的回复,建立第二个sheet已经成功!(原先程序错在,我不应该建立sheet2,直接用sheet就对了).现在还想请教大家一个问题!Excel单元格中文字太长时,就会重叠!
我只能把一列设置成自适应,我想把所有列都设置成自适应,该怎么设置啊?

[pioneer_public]

wssSheets = wbBook.GetWorksheets();
wsSheet = wssSheets.GetItem(_variant_t("Sheet1")); // Get Sheet1
ExcelApp.SetVisible(FALSE); // 隐藏 //wsSheet.SetName( "Your Sheet" ); // 改名
ExcelApp.SetDisplayAlerts(false);
rngXls = wsSheet.GetCells();
rngXls.AutoFit();

[pioneer_public]

可以=你表格写完后，使用AUTOFIT方法，来让列宽自适应

[jameshooo]

for (int k=1;i<5;i++)
改成
for (int i=1;i<5;i++)

[chunyou128]

[Quote=引用 6 楼 pioneer_public 的回复:]
wssSheets = wbBook.GetWorksheets();
wsSheet = wssSheets.GetItem(_variant_t("Sheet1")); // Get Sheet1
ExcelApp.SetVisible(FALSE); // 隐藏 //wsSheet.SetName( "Your Sheet" ); // 改名
ExcelApp.SetDisplayAlerts(false);
rngXls = wsSheet.GetCells();
rngXls.AutoFit();
[/Quote]
说实话，试了n次，还是不行，报错：类Range的Auto方法无效
代码如下：

void CTestExcelDlg::OnButton1() 
{
	// TODO: Add your control notification handler code here
	_Application app;
	Workbooks books;
	_Workbook book;
	Worksheets sheets;
	_Worksheet sheet;
	Range range;
	Font font;
	Range cols;

	
	if( !app.CreateDispatch("Excel.Application") ){
		this->MessageBox("无法创建Excel应用！");
		return;
	}
	books=app.GetWorkbooks();
	app.SetVisible(false);
	COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

	book=books.Add(covOptional);
	sheets=book.GetWorksheets();
	app.SetVisible(true);
	sheet=sheets.GetItem(_variant_t((long)1));
	range=sheet.GetCells();
	for (int i=1;i<5;i++)
	{
		for (int j=1;j<5;j++)
		{
			range.SetItem(_variant_t((long)i),_variant_t((long)j),_variant_t("搞长一点做测试啊!心态要好!呵呵!")); 
			
		}
	}
	sheets = book.GetWorksheets();
	sheet = sheets.GetItem(_variant_t((long)1));
	app.SetVisible(false);
	app.SetDisplayAlerts(false);
	cols = sheet.GetCells();
	cols.GetEntireColumn();
	cols.AutoFit();
	cols.SetColumnWidth(_variant_t((long)100));


	sheet=sheets.GetItem(_variant_t((long)2));
	range=sheet.GetCells();
	range.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("test"));
	sheet=sheets.GetItem(_variant_t((long)3));
	range=sheet.GetCells();
	sheet.Copy(vtMissing,_variant_t(sheet));


	app.SetUserControl(TRUE);


	book.SaveAs(COleVariant("C:\\a.xls"),_variant_t((long)TRUE),
		covOptional,covOptional,
		covOptional,covOptional,(long)0,covOptional,covOptional,covOptional,covOptional,
		covOptional);
}

[liuzxchina]

不知道是不是需要激活Sheet，

我一般都先Active

OOL CExcelEx::SetActiveSheet(long nIndex)
{
	if (!IsOpen())
	{
		return FALSE;
	}
	if (m_pWorksheet->m_lpDispatch != NULL)
	{
		if (m_pWorksheet->GetIndex() == nIndex)
		{
			return TRUE;
		}
		else
		{
			m_pWorksheet->ReleaseDispatch();
		}
	}

	Worksheets worksheets = m_pWorkbook->GetWorksheets();
	m_pWorksheet->AttachDispatch(worksheets.GetItem(_variant_t(nIndex)));
	if (m_pWorksheet->m_lpDispatch != NULL)
	{
		m_pWorksheet->Activate();
	}
	return (m_pWorksheet->m_lpDispatch != NULL);
}

[chunyou128]

[Quote=引用 1 楼 xsc2001 的回复:]
excel默认有三个sheet，你可以通过下面这句代码得到具体的sheet
sheet=sheets.GetItem(COleVariant((short)1));//这里的1表示是第一个sheet，2表示是第二个sheet，3表示第三个sheet
[/Quote]
这个我知道的，我也试过，代码如下：

void CTestExcelDlg::OnButton1() 
{
	// TODO: Add your control notification handler code here
	_Application app;
	Workbooks books;
	_Workbook book;
	Worksheets sheets;

	_Worksheet sheet;
	_Worksheet sheet2;

	Range range;
	Range range2;

	Font font;
	Font font2;

	Range cols;
	Range cols2;

	COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
	
	if( !app.CreateDispatch("Excel.Application") ){
		this->MessageBox("无法创建Excel应用！");
		return;
	}
	books=app.GetWorkbooks();
	book=books.Add(covOptional);
	sheets=book.GetSheets();
	sheet=sheets.GetItem(COleVariant((short)1));
	range=sheet.GetRange(COleVariant("A1"),COleVariant("A1"));
	for (int i=1;i<5;i++)
	{
		for (int j=1;j<5;j++)
		{
			range.SetItem(_variant_t((long)i),_variant_t((long)j),_variant_t("test")); 

		}
	}
	cols=range.GetEntireColumn();
	cols.AutoFit();
	
	app.SetVisible(TRUE);
	app.SetUserControl(TRUE);

	sheet2=sheets.GetItem(COleVariant((short)2)); //想切换到sheet2，但以下的内容却没有输出到
	range2=sheet2.GetRange(COleVariant("A1"),COleVariant("A1"));//sheet2中
	for (int k=1;i<5;i++)
	{
		for (int j=1;j<5;j++)
		{
			range2.SetItem(_variant_t((long)i),_variant_t((long)j),_variant_t("test")); 
			
		}
	}


	book.SaveAs(COleVariant("C:\\a.xls"),covOptional,
		covOptional,covOptional,
		covOptional,covOptional,(long)0,covOptional,covOptional,covOptional,covOptional,
		covOptional);

}

sheet1中写入了内容，但sheet2中却是空白，我怀疑sheet2=sheets.GetItem(COleVariant((short)2));前面是不是要加一些用来释放sheet1的语句？该怎么加呢？

[pizziars]

Range rgColumn;
rgColumn = m_oRange.GetEntireColumn();
rgColumn.AutoFit();