C# 写Excel文件

Question

有C# 写Excel文件，Excel文件是一个模板。
想把数据放在Excel文件指定的地方。
不可以修改模板。
该如何处理呢？有个思路也行，因为现在我不知道该如何指定位置。谢谢。

Answer 1

Microsoft.Office.Interop.Excel.Application Myexcel = new Microsoft.Office.Interop.Excel.Application();
if (Myexcel == null)
{
return;
}

Microsoft.Office.Interop.Excel._Workbook xBk;
xBk = Myexcel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel._Worksheet xSt;
xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;

//设置标题等
string Title = null;
if (this.cbox_depart.Text.Trim().Length > 0)
{
if (this.cbox.Text.Trim().Length > 0)
Title = cbox.Text.Trim() + "评价结果表";
else
Title = cbox_depart.Text.Trim() + "评价结果表";
}
else
Title=DateTime.Now.Year +"全员评价汇总统计表";

xSt.Name = Title;

//报表的格式设置
xSt.Cells[1, 6] = Title;
xSt.get_Range(Myexcel.Cells[1, 6], Myexcel.Cells[1, 6]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
xSt.get_Range(Myexcel.Cells[1, 6], Myexcel.Cells[1, 6]).Font.Bold = true;
xSt.get_Range(Myexcel.Cells[1, 6], Myexcel.Cells[1, 6]).Font.Size = 20;

xSt.Cells[3, 1] = "排名";
xSt.Cells[3, 2] = "姓名";
xSt.Cells[3, 3] = "职位";
xSt.Cells[3, 4] = "上级评分";
xSt.Cells[3, 5] = "下级/同级评分";
xSt.Cells[3, 6] = "其他部门同事评分";
xSt.Cells[3, 7] = "客户评分";
xSt.Cells[3, 8] = "人事评分";
xSt.Cells[3, 9] = "总分";
xSt.Cells[3, 10] = "其他人员建议";

//写excel dgv是一个datagridview
//下面是用循环把datagridview中的内容写道写到excel
for (int rowIndex = 0; rowIndex < DGV.Rows.Count;rowIndex ++ )
{
int colIndex = 0;
for ( colIndex = 1; colIndex < DGV.ColumnCount; colIndex++)
{
xSt.Cells[rowIndex + 4, colIndex] = DGV.Rows[rowIndex].Cells[colIndex - 1].Value.ToString();
}

}

//后台处理
Myexcel.Visible = true;

如果你的数据是查询到datatable中了的话，可以
用datatable.rows.count获取数据的行数
collums.count获取列数向Excel中写类似上面把datagridview中的内容写到excel中

Answer 2

你可以设置excel排版 代码如下 public class OfficeExcelIO
{
private int _ReturnStatus;
private string _ReturnMessage;/// <summary>
/// 把DataTable导出到EXCEL
/// </summary>
public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
{
if (dt == null)
{
_ReturnStatus = -1;
_ReturnMessage = "数据集为空！";
return false;
}
bool fileSaved = false;
Application xlApp = new ApplicationClass();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
return false;
}Workbooks workbooks = xlApp.Workbooks;
Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size = 12;
worksheet.Cells.HorizontalAlignment = -4108; //-4108居中-4152居右 -4131 居左
Range range;long totalCount = dt.Rows.Count;
int dtColumnCount = dt.Columns.Count;
long rowRead = 0;
float percent = 0;
//写入字段
for (int i = 0; i < dtColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Range)worksheet.Cells[1, i + 1];
range.ColumnWidth = 15;
range.NumberFormatLocal = "@ ";
//range.Interior.ColorIndex = 15;
//range.Font.Bold = true;
range.Font.Size = 10;
}
((Range)worksheet.Cells[1, 4]).Font.Color = ((Range)worksheet.Cells[1, 5]).Font.Color =
((Range)worksheet.Cells[1, 9]).Font.Color = ((Range)worksheet.Cells[1, 10]).Font.Color =
((Range)worksheet.Cells[1, 13]).Font.Color = ((Range)worksheet.Cells[1, 14]).Font.Color =
((Range)worksheet.Cells[1, 15]).Font.Color = ((Range)worksheet.Cells[1, 16]).Font.Color = ColorTranslator.ToOle(Color.Red);
((Range)worksheet.Cells[1, 11]).HorizontalAlignment = -4152;//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
((Range)worksheet.Cells[r + 2, 2]).NumberFormatLocal = "@";//文本格式
for (int i = 0; i < dtColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
((Range)worksheet.Cells[r + 2, 4]).Font.Size = 10;
((Range)worksheet.Cells[r + 2, 10]).Font.Size = 10;
((Range)worksheet.Cells[r + 2, 13]).Font.Size = 10;
((Range)worksheet.Cells[r + 2, 14]).Font.Size = 10;
((Range)worksheet.Cells[r + 2, 11]).HorizontalAlignment = -4152;
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
//保存文件
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
using(System.IO.StreamWriter sw=System.IO.File.CreateText(@"c:\error.txt")){
sw.WriteLine(ex.ToString());
}
fileSaved = false;
_ReturnStatus = -1;
_ReturnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;
}
}
else
{
fileSaved = false;
}//释放Excel对应的对象
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
//workbooks.Close();
}
xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
return fileSaved;
}
}

Answer 3

直接导入就行啦

Answer 4

给你一个类，直接调用相关方法就可以了，要引用Aspose.Cellsusing System;
using System.Collections.Generic;
using System.Text;
using Aspose.Cells;
using System.Data;namespace sales
{
public class AsposeExcel
{
private string outFileName = "";
private string fullFilename = "";
private Workbook book = null;
private Worksheet sheet = null;
public AsposeExcel(string outfilename, string tempfilename)//导出
{
outFileName = outfilename;
book = new Workbook();
book.Open("model.xls");
sheet = book.Worksheets[0];
}
public AsposeExcel(string fullfilename)//导入
{
fullFilename = fullfilename;
//book = new Workbook();
//book.Open(tempfilename);
//sheet = book.Worksheets[0];
} private void AddTitle(string title, int columnCount)
{
sheet.Cells.Merge(0, 0, 1, columnCount);
sheet.Cells.Merge(1, 0, 1, columnCount); Cell cell1 = sheet.Cells[0, 0];
cell1.PutValue(title);
cell1.Style.HorizontalAlignment = TextAlignmentType.Center;
cell1.Style.Font.Name = "黑体";
cell1.Style.Font.Size = 14;
cell1.Style.Font.IsBold = true; Cell cell2 = sheet.Cells[1, 0];
cell1.PutValue("查询时间：" + DateTime.Now.ToLocalTime());
cell2.SetStyle(cell1.Style);
} private void AddHeader(DataTable dt)
{
Cell cell = null;
for (int col = 0; col < dt.Columns.Count; col++)
{
cell = sheet.Cells[0, col];
cell.PutValue(dt.Columns[col].ColumnName);
cell.Style.Font.IsBold = true;
}
} private void AddBody(DataTable dt)
{
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
}
}
} public Boolean DatatableToExcel(DataTable dt)
{
//DataTable dt_header;
//DataRow dr = dt_header.Rows[0];
//foreach (DataColumn c in dt.Columns)
//{
// dr.c.ColumnName);
//}
//dt_header.Rows.Add();
// string dfd= dt.GetSheet(strSheetName).GetParameter(1).Name;
Boolean yn = false;
try
{
//sheet.Name = sheetName; //AddTitle(title, dt.Columns.Count);
//AddHeader(dt_header);
AddBody(dt); sheet.AutoFitColumns();
//sheet.AutoFitRows(); book.Save(outFileName);
yn = true;
return yn;
}
catch (Exception e)
{
return yn;
// throw e;
}
} public DataTable ExcelToDatatalbe()//导入
{
Workbook book = new Workbook();
book.Open(fullFilename);
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
//获取excel中的数据保存到一个datatable中
DataTable dt_Import = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, false);
// dt_Import.
return dt_Import;
} }
}