用COM操作自动生成excel，怎样控制单元格的高度，宽度，以及文字居中？？？

[qq948641731]

//在一下程序的基础下，怎样制单元格的高度，宽度，以及文字居中？？？请高手指点，写详细点，分不多了,谢谢各位
class ImportExcel
{   
private Excel.Application objExcel = null;//一个excel对象
     private Excel.Workbooks objBooks = null; //
    private Excel._Workbook objBook = null;
    private Excel.Sheets objSheets = null;
    private Excel._Worksheet objSheet = null;
    private Excel.Range objRange = null;
    private Excel.Font objFont = null;
 protected void ExportyhInfo_Click(object sender, EventArgs e)
    {

        objExcel = new Excel.Application();
        objBooks = (Excel.Workbooks)objExcel.Workbooks;
        objBook = (Excel._Workbook)(objBooks.Add(objOpt));
        objSheets = (Excel.Sheets)objBook.Worksheets;
        objSheet = (Excel._Worksheet)(objSheets.get_Item(1));

     objRange = objSheet.get_Range("A3", objOpt);
        objRange.set_Value(objOpt, "受理员");
        objRange.WrapText = true;//自动换行
        objFont = objRange.Font;
        objFont.Bold = true;
        objFont.Size = 10;
//在此处如何控制A3的高度，宽度，文字居中
.......
........
   }

}

[qq948641731]

我自己顶，顶起！

[human_2]

Rows("4:4").RowHeight = 61.5
Columns("A:A").ColumnWidth = 31.63
objRange .HorizontalAlignment = xlCenter

[qq948641731]

[Quote=引用 2 楼 human_2 的回复:]
Rows("4:4").RowHeight = 61.5
Columns("A:A").ColumnWidth = 31.63
objRange .HorizontalAlignment = xlCenter
[/Quote]
网上查过，没有用，你在我上面的程序基础上改下

[takako_mu]

你都沒定義寬度怎么設？

[qq948641731]

怎么定义，请贴代码

[qq948641731]

不控制单元格的高度宽度，那我如果要控制整列的宽度总行吧，怎么控制贴代码
谢谢！

[sealzrt]

在用Excel作报表的时候，可能需要操作单元格的边框和填充颜色和纹理等操作，下面的代码说明如何设置选中的单元格的填充纹理和边框



try

{

ThisApplication = new Excel.Application();



ThisWorkbook = ThisApplication.Workbooks.Open("z:\\Book1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);





ThisApplication.DisplayAlerts = false;

xlSheet = (Excel.Worksheet)ThisWorkbook.Worksheets.get_Item(1);

Excel.Range range = xlSheet.get_Range("G4","H5");

range.Value = "123";



Excel.Style st = ThisWorkbook.Styles.Add("PropertyBorder", Type.Missing);



range.Interior.Pattern = Excel.XlPattern.xlPatternCrissCross;

range.Borders.Weight = 2;

range.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;

range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;

range.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;

range.Borders.get_Item(XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone;

range.Borders.get_Item(XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone;

range.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlLineStyleNone;

range.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone;

range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlDot;

range.Borders.get_Item(XlBordersIndex.xlEdgeLeft).Color = System.Drawing.ColorTranslator.ToOle(Color.Red);



ThisWorkbook.SaveAs("z:\\Book2.xls", Type.Missing, Type.Missing,

Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,

Type.Missing, Type.Missing, Type.Missing, Type.Missing);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

ThisWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);

ThisApplication.Workbooks.Close();



ThisApplication.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(ThisWorkbook);

System.Runtime.InteropServices.Marshal.ReleaseComObject(ThisApplication);

ThisWorkbook = null;

ThisApplication = null;

GC.Collect();

this.Close();

}

[sealzrt]

/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dt">要导出的DataTable</param>
public void ExportToExcel(System.Data.DataTable dt)
{
if (dt == null) return;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
// lblMsg.Text = "无法创建Excel对象，可能您的机子未安装Excel";
lblMsg.Text = GetLocalResourceObject("noexcel").ToString();
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;

//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;//居中
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高

}
//写入内容
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9;//字体大小
//加边框
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}

range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}

try
{
workbook.Saved = true;
workbook.SaveCopyAs(System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\EduceWordFiles\\" + this.Context.User.Identity.Name + ".xls");
}
catch (Exception ex)
{
//lblMsg.Text = "导出文件时出错,文件可能正被打开！\n" + ex.Message;
lblMsg.Text = GetLocalResourceObject("error").ToString() + "\n" + ex.Message;
}


workbooks.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();

xlApp.Quit();

int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁

#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killId = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killId = m;
}
}
if (excelProc[killId].HasExited == false)
{
excelProc[killId].Kill();
}
#endregion


//提供下载
BIClass.BusinessLogic.Util.ResponseFile(Page.Request, Page.Response, "ReportToExcel.xls"
, System.Web.HttpRuntime.AppDomainAppPath + "XMLFiles\\EduceWordFiles\\" + this.Context.User.Identity.Name + ".xls", 1024000);
}

[zzxap]

http://topic.csdn.net/u/20090325/09/8cb8ee1e-ff1e-4d79-8101-0331fd74ce01.html

[zzxap]

[CODE=C#]
若想实现更加富于变化或者行列不规则的excel导出时，可用本法。
public void OutputExcel(DataView dv,string str)
{
//dv为要输出到Excel的数据，str为标题名称
GC.Collect();
Application excel;// = new Application();
int rowIndex=4;
int colIndex=1;

_Workbook xBk;
_Worksheet xSt;

excel= new ApplicationClass();

xBk = excel.Workbooks.Add(true);

xSt = (_Worksheet)xBk.ActiveSheet;

//
//取得标题
//
foreach(DataColumn col in dv.Table.Columns)
{
colIndex++;
excel.Cells[4,colIndex] = col.ColumnName;
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}

//
//取得表格中的数据
//
foreach(DataRowView row in dv)
{
rowIndex ++;
colIndex = 1;
foreach(DataColumn col in dv.Table.Columns)
{
colIndex ++;
if(col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if(col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
}
}
}
//
//加载一个合计行
//
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum,2] = "合计";
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
//
//设置选中的部分的颜色
//
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();
xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19;//设置为浅黄色，共计有56种
//
//取得整个报表的标题
//
excel.Cells[2,2] = str;
//
//设置整个报表的标题格式
//
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22;
//
//设置报表表格为最适应宽度
//
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select();
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit();
//
//设置整个报表的标题为跨列居中
//
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select();
xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
//
//绘制边框
//
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗
//
//显示效果
//
excel.Visible=true;

//xSt.Export(Server.MapPath(".")+""+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
xBk.SaveCopyAs(Server.MapPath(".")+""+this.xlfile.Text+".xls");

ds = null;
xBk.Close(false, null,null);

excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
string path = Server.MapPath(this.xlfile.Text+".xls");

System.IO.FileInfo file = new System.IO.FileInfo(path);
Response.Clear();
Response.Charset="GB2312";
Response.ContentEncoding=System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());

// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";

// 把文件流发送到客户端
Response.WriteFile(file.FullName);
// 停止页面的执行

Response.End();
}


[/CODE]