＋设为首页 ＋添加到收藏夹
如何在 c# 中设置excel 的某一列为文本，以及他的背景颜色，文字的对齐方式
当把数据从应用程序中导入到excel的时候，经常有这种现象
应用程序中的字符串“6951-1”到了excel里面就成了“Jan-51”
请有经验的师兄师弟们给出宝贵的代码片断，不胜感激。
在数字型字符串前面加入一个撇！
cell[0,0] = "6951-1";
using System;
using System.Data;
using System.Drawing;
using Excel;
using System.Reflection;
using Me.DataBase;
namespace Me.ExcelAPP
{
/// <summary>
/// OperateExcel 篕璶磞瓃
/// </summary>
public class OperateExcel
{
public OperateExcel()
{
//
// TODO: 篶ㄧΑ祘Α絏
//
}
public void ExportToExcelFile(string strFilePath,System.Data.DataTable dt,string strServerName,DateTime sysdate)
{
int i=0;
DateTime excelDate=DateTime.Now.AddYears(-1);
const int START_ROW=5;
int intExcelID=0,intRanColorIndex=0;
string[,] sData=new string[dt.Rows.Count,8];
TimeSpan ts=new TimeSpan();
Color colorRanBack=new Color();
Excel.ApplicationClass xApp = new Excel.ApplicationClass();
Excel.Range xRan;
//ъ讽玡秨币Excel郎秈祘ID玂Kill非称
foreach (System.Diagnostics.Process excelProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
//硄筁逼矪瞶玂谍讽╰参いΤExcel秈祘
//ъご琌讽玡祘Α秨币Excel秈祘StartTime程
if (excelProc.StartTime>excelDate)
{
excelDate=excelProc.StartTime ;
intExcelID=excelProc.Id;
}
}
Excel._Workbook xBook = xApp.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\MonitorAlarm.xls",
Missing.Value,Missing.Value,Missing.Value,Missing.Value
,Missing.Value,Missing.Value,Missing.Value,Missing.Value
,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
Excel._Worksheet xSheet = (_Worksheet) (xBook.Worksheets.get_Item(1));
xApp.Visible=false;
//计沮矪瞶砰场だ
try
{
xSheet.Cells[2,1]="Server Name: " + strServerName;
xSheet.Cells[3,1]="Report Time: " + sysdate.ToString("yyyy/MM/dd HH:mm:ss");
for(i=0;i<dt.Rows.Count;i++)
{
sData[i,0]=Convert.ToString(i+1);
sData[i,1]=dt.Rows[i]["IP"].ToString();
sData[i,2]=dt.Rows[i]["AP_NAME"].ToString();
sData[i,3]=dt.Rows[i]["FUNCTION_NAME"].ToString();
sData[i,4]=dt.Rows[i]["AUTORUN_TIME"].ToString() + " (minutes)";
if (dt.Rows[i]["WORK_TIME"].ToString()=="")
{ //讽Rい礚计沮眖ゼ笲︽筁
sData[i,5]="Never Run";
intRanColorIndex=6;//独︹
sData[i,6]="";
sData[i,7]="";
}
else
{
sData[i,6]=Convert.ToDateTime(dt.Rows[i]["WORK_TIME"]).ToString("yyyy/MM/dd HH:mm:ss");
ts= sysdate-Convert.ToDateTime(dt.Rows[i]["WORK_TIME"]);
if (ts.TotalMinutes>Convert.ToSingle(dt.Rows[i]["AUTORUN_TIME"].ToString())*1.5)
{
sData[i,5]="Miss Sign";
intRanColorIndex=3;//︹
sData[i,7]=ts.TotalMinutes.ToString() ;
}
else
{
if (dt.Rows[i]["WORK_FLAG"].ToString()=="0")
{
sData[i,5]="Well Run";
intRanColorIndex=4;//睱厚︹
sData[i,7]="";
}
else
{
sData[i,5]="Fail Run";
intRanColorIndex=46;//卷︹
sData[i,7]=ts.TotalMinutes.ToString();
}
}
}
xRan=xApp.get_Range("A"+(START_ROW+i).ToString(),"H" + (START_ROW+i).ToString());
xRan.Interior.ColorIndex=intRanColorIndex;
xRan.Interior.Pattern=Excel.Constants.xlSolid;
//xSheet.Rows[START_ROW].
//xApp.Rows xRow ;
//xApp.Selection.Interior.ColorIndex = 6;
//xApp.Selection.Interior.Pattern = Excel.Constants.xlSolid;
}
//恶计沮
xRan=xApp.get_Range("A"+START_ROW.ToString(),"H" + Convert.ToString(START_ROW + dt.Rows.Count-1));
xRan.Value=sData;
xApp.AlertBeforeOverwriting=false;
xApp.DisplayAlerts=false;
xApp.EnableAutoComplete=true;
xApp.ActiveWorkbook.SaveAs(strFilePath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value
,Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value
,Missing.Value);
//-----------﹍睦Excel癸禜絏-------------------------
//
// 琌硄筁Kill秈祘よΑ癶Excel
// ┮硂琿絏ぃ
// 眖祘Α呸胯腨略┦σ納某
xRan=null;
xSheet=null;
xBook.Close(Missing.Value,Missing.Value,Missing.Value);
xBook=null;
xApp.Workbooks.Close();
xApp.Quit();
xApp=null;
//-------------------------------------------------------------
//奔ノKill Excel秈祘家舱
KillExcelByID(intExcelID);
//ㄢ.NET矗ㄑ︰ВΜ诀ヘ玡ぃぃ穦紇臫狦
//τ耕╰参戈方珿猔睦奔ぃ睰
//GC.Collect();
//GC.WaitForPendingFinalizers();
}
catch
{
KillExcelByID(intExcelID);
throw;
}
}
private void KillExcel(DateTime startTime)
{
foreach (System.Diagnostics.Process excelProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
TimeSpan ts=excelProc.StartTime-startTime;
if (Math.Abs(ts.TotalMilliseconds)<1000)
{
excelProc.Kill();
}
}
}
private void KillExcelByID(int intExcelID)
{
foreach (System.Diagnostics.Process excelProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
{
if (excelProc.Id==intExcelID)
{
excelProc.Kill();
}
}
}
}
}
Excel里的Color类型和.net里的Color类型不一样，所以只能通过index来赋值，至于如何得到某种颜色的index是多少，只能在Excel里录巨集来得到了：(
