最近项目中一直在写XML、Table、Excel之间的转化。之前一直都是不考虑格式的导出，今天给出一个格式，让按照格式导出，还真把我这新手为难了一翻，网上给出的资料基本一样。为了一个单元格文字变色纠结了很久。下面把学习资料发出，希望对新手学习有所帮助：
下面是会用到的导出属性。
合并单元格属性：
worksheet.get_Range(worksheet.Cells[rowIndex, columnCount + 1], worksheet.Cells[rowIndex + 2, columnCount + 1]).MergeCells = true;
设置某一个单元格中字体的颜色：
worksheet.get_Range(worksheet.Cells[rowIndex, 5], worksheet.Cells[rowIndex, 8]).Font.ColorIndex = 5;(这个在网上找的一直变不了色，后面自己试出来了)
字体颜色的index值：
选定区间设置字符串格式或数字格式：
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[rowIndex, 1], worksheet.Cells[rowCount+rowIndex-1, columnCount-1]);
range.NumberFormat = "@";//设置数字文本格式
Microsoft.Office.Interop.Excel.Range rangeinfo = worksheet.get_Range(worksheet.Cells[rowIndex, 4], worksheet.Cells[rowCount + rowIndex - 1, 4]);
rangeinfo.NumberFormat = "00";
用于汇总和计算时(所计算的字段值必须是数字格式)：
worksheet.Cells[rowIndex + i, columnCount+1] = "=CEILING(D" + (rowIndex + i).ToString() + "*1.01+1,2)"; i是变量
PS：一下代码则可导出如下图的Excel格式：
// 导出为Excel格式文件
/// </summary>
/// <param name="dt">作为数据源的DataTable</param>
/// <param name="saveFile">带路径的保存文件名</param>
/// <param name="title">一个Excel sheet的标题</param>
public static void DataTabletoExcel(System.Data.DataTable dt, string saveFile)
{
Microsoft.Office.Interop.Excel.Application rptExcel = new Microsoft.Office.Interop.Excel.Application();
if (rptExcel == null)
{
PublicClass.HintBox("无法打开EXcel，请检查Excel是否可用或者是否安装好Excel");
return;
} int rowCount = dt.Rows.Count;//行数
int columnCount = dt.Columns.Count;//列数
int rowIndex = ;
int colindex = ;
//保存文化环境
System.Globalization.CultureInfo currentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Microsoft.Office.Interop.Excel.Workbook workbook = rptExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item();
worksheet.Name = "报表";//一个sheet的名称
rptExcel.Visible = true;//打开导出的Excel文件 worksheet.Cells[, ] = "";//模版号
rowIndex++;
//第二行内容
Microsoft.Office.Interop.Excel.Range rangeinfo1 = worksheet.get_Range(worksheet.Cells[rowIndex, colindex + ],worksheet.Cells[rowIndex, colindex + ]);
rangeinfo1.NumberFormat = "@";
worksheet.Cells[rowIndex, colindex] = "S#262229";
worksheet.Cells[rowIndex, colindex+] = dt.Columns[].ColumnName;
worksheet.Cells[rowIndex, colindex + ] = dt.Rows[][];
worksheet.Cells[rowIndex, colindex+] = "EAN";
//合并打印数量单元格
worksheet.Cells[rowIndex, columnCount+] = "打印数量";
worksheet.Cells[rowIndex, columnCount+] = "包装数量";
worksheet.get_Range(worksheet.Cells[rowIndex, columnCount + ], worksheet.Cells[rowIndex + , columnCount + ]).MergeCells = true;
worksheet.get_Range(worksheet.Cells[rowIndex, columnCount + ], worksheet.Cells[rowIndex + , columnCount + ]).MergeCells = true;
rowIndex++;
//第三行内容
worksheet.Cells[rowIndex, ] = "貨名";
worksheet.Cells[rowIndex, ] = "Line";
//合并第三行第二列
worksheet.get_Range(worksheet.Cells[rowIndex, ], worksheet.Cells[rowIndex+, ]).MergeCells = true;
worksheet.Cells[rowIndex, ] = "序號";
worksheet.Cells[rowIndex, ] = "數量";
worksheet.Cells[rowIndex, ] = "1 (EUR) size";
worksheet.Cells[rowIndex, ] = "1a (€)";
worksheet.Cells[rowIndex, ] = "2 (UK) size";
worksheet.Cells[rowIndex, ] = "2a (₤)";
worksheet.get_Range(worksheet.Cells[rowIndex, ], worksheet.Cells[rowIndex, ]).Font.Bold = true;
worksheet.Cells[rowIndex, ] = "";
worksheet.Cells[rowIndex, ] = "";
worksheet.Cells[rowIndex, ] = "";
worksheet.Cells[rowIndex, ] = "";
worksheet.Cells[rowIndex, ] = "8(barcode)";
worksheet.Cells[rowIndex, ] = "";
worksheet.get_Range(worksheet.Cells[rowIndex, ], worksheet.Cells[rowIndex, ]).Font.ColorIndex = ;
rowIndex++;
//填充列标题
for (int i = ; i < columnCount-; i++)
{
if (i > )
{
worksheet.Cells[rowIndex, i + ] = dt.Columns[i].ColumnName; }
else
{
worksheet.Cells[rowIndex, i+] = dt.Columns[i].ColumnName; }
}
rowIndex++; //创建对象数组存储DataTable的数据，这样的效率比直接将Datateble的数据填充worksheet.Cells[row,col]高
object[,] objData = new object[rowCount, columnCount]; //填充内容到对象数组
for (int r = ; r < rowCount; r++)
{
for (int col = ; col < columnCount-; col++)
{
objData[r, col] = dt.Rows[r][col].ToString();
} System.Windows.Forms.Application.DoEvents();
} //将对象数组的值赋给Excel对象
Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[rowIndex, ], worksheet.Cells[rowCount+rowIndex-, columnCount-]);
range.NumberFormat = "@";//设置数字文本格式
Microsoft.Office.Interop.Excel.Range rangeinfo = worksheet.get_Range(worksheet.Cells[rowIndex, ], worksheet.Cells[rowCount + rowIndex - , ]);
rangeinfo.NumberFormat = "";
range.Value2 = objData; for (int i = ; i < rowCount; i++)
{
if (i > )
{ //计算打印数量
worksheet.Cells[rowIndex + i, columnCount+] = "=CEILING(D" + (rowIndex + i).ToString() + "*1.01+1,2)"; }
else
{ worksheet.Cells[rowIndex + i, columnCount+] = "=CEILING(D" + (rowIndex + i).ToString() + "*1.01+1,2)";
}
} //设置格式
rptExcel.StandardFont = "新細明體";
rptExcel.StandardFontSize = ;
worksheet.get_Range(worksheet.Cells[, ], worksheet.Cells[rowCount+rowIndex, columnCount]).Columns.AutoFit();//设置单元格宽度为自适应
worksheet.get_Range(worksheet.Cells[, ], worksheet.Cells[rowCount+rowIndex, columnCount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Bold = true;
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Color= ConsoleColor.Blue;
// worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount + 2, columnCount]).Borders.LineStyle = 1;//设置边框
//汇总
rowIndex = rowCount + rowIndex;
worksheet.Cells[rowIndex, ] = "=SUM(D5:D10)"; //恢复文化环境
System.Threading.Thread.CurrentThread.CurrentCulture = currentCI;
try
{
//rptExcel.Save(saveFile); //自动创建一个新的Excel文档保存在“我的文档”里，如果不用SaveFileDialog就可用这种方法
workbook.Saved=true;
workbook.SaveCopyAs(saveFile);//以复制的形式保存在已有的文档里
PublicClass.HintBox("数据已经成功导出为Excel文件！");
}
catch (Exception ex)
{
PublicClass.HintBox("导出文件出错，文件可能正被打开，具体原因：" + ex.Message);
}
finally
{
dt.Dispose();
rptExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(rptExcel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
GC.Collect();
KillAllExcel();
}
}
/// <summary>
/// 获得所有的Excel进程
/// </summary>
/// <returns>所有的Excel进程</returns>
private static List<Process> GetExcelProcesses()
{
Process[] processes = Process.GetProcesses();
List<Process> excelProcesses = new List<Process>(); for (int i = ; i < processes.Length; i++)
{
if (processes[i].ProcessName.ToUpper() == "EXCEL")
excelProcesses.Add(processes[i]);
} return excelProcesses;
}
private static void KillAllExcel()
{
List<Process> excelProcess = GetExcelProcesses();
for (int i = ; i < excelProcess.Count; i++)
{
excelProcess[i].Kill();
}
}