个人总结导出excel报表的案例：
//导出报表
protected void btnExport_Click(object sender, EventArgs e)
{
List<ProOutContract> list = GetDataTableFromIList();
if (list == null || (list != null && list.Count == 0))
{
Messabox.ShowError(this, "无记录导出");
return;
}
string name = "~/File/ExcelFile/工程信息外部合同登记表" + Session.SessionID + ".xls";
string savePath = Server.MapPath(name);
bool f = ExportExcel(name, list, savePath);
FileInfo DownloadFile = new FileInfo(savePath); //设置要下载的文件
Response.Clear(); //清除缓冲区流中的所有内容输出
Response.ClearHeaders(); //清除缓冲区流中的所有头
Response.Buffer = false; //设置缓冲输出为false
//设置输出流的 HTTP MIME 类型为application/octet-stream
Response.ContentType = "application/octet-stream";//将 HTTP 头添加到输出流
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("工程信息外部合同登记表.xls", System.Text.Encoding.UTF8));
//Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());//将指定的文件直接写入 HTTP 内容输出流。
//Response.WriteFile(DownloadFile.FullName);
Response.WriteFile(savePath);
Response.Flush(); //向客户端发送当前所有缓冲的输出
File.Delete(savePath);//删除文件
Response.End(); //将当前所有缓冲的输出发送到客户端
}
//得到报表数据；
public List<ProOutContract> GetDataTableFromIList()
{
int pageNumber = 1;
PageBean page = new PageBean();
page.CurrentPage = pageNumber;
page.PageSize = 100000;
ProOutContract pb = null;
if (ViewState["queryModel"] != null)
{
pb = ViewState["queryModel"] as ProOutContract;
}
else
{
pb = new ProOutContract();
}
List<ProOutContract> list = null;
list = pm.GetListByPage(pb, page);
return list;
}
//导出报表
public bool ExportExcel(string reportName, List<ProOutContract> list, string saveFileName)
{
//获取Excel进程
Process[] P0, P1;
P0 = Process.GetProcessesByName("Excel");
if (list == null)
{
return false;
}
bool fileSaved = false;
Excel.Application m_objExcel = new Excel.Application();
if (m_objExcel == null)
{
return false;
}
Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
Excel.Workbook m_objBook = (Excel.Workbook)(m_objBooks.Add(Excel.XlWBATemplate.xlWBATWorksheet));
Excel.Sheets m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
Excel._Worksheet m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objSheet.Name = "Sheet1";
Excel.Range range = null;
long totalCount = list.Count + 5;
long colnum = 29;//有12列；
((Excel.Range)m_objSheet.Cells).NumberFormatLocal = "@";//修改excel为文本形式
m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[totalCount, colnum]).Borders.LineStyle = XlLineStyle.xlContinuous;//设置所有表格线；
////第一行设置
m_objExcel.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, colnum]).MergeCells = true;//合并单元格
m_objExcel.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, colnum]).HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平对齐方式
m_objExcel.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, colnum]).VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直对齐方式
m_objExcel.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, colnum]).Font.Size = 14;
m_objSheet.Cells[1, 1] = "合同签订进程一览表";
m_objExcel.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[totalCount, 1]).ColumnWidth = 20;//设置列的宽度；
m_objExcel.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[5, colnum]).HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平对齐方式
m_objExcel.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[5, colnum]).VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直对齐方式
//--
for (int i = 1; i <= colnum; i++)
{
if (i <= 2 || (i >= 7 && i <= 9) || i == 27 || i == 28 || i == 29)
{
m_objExcel.get_Range(m_objSheet.Cells[2, i], m_objSheet.Cells[5, i]).MergeCells = true;
}
if (i >= 3 && i <= 6 || i == 23 || i == 24)
{
m_objExcel.get_Range(m_objSheet.Cells[4, i], m_objSheet.Cells[5, i]).MergeCells = true;
}
if (i >= 10 && i <= 19 || i == 25 || i == 26)
{
m_objExcel.get_Range(m_objSheet.Cells[3, i], m_objSheet.Cells[5, i]).MergeCells = true;
}
if (i >= 22 && i <= 24)
{
m_objExcel.get_Range(m_objSheet.Cells[4, i], m_objSheet.Cells[5, i]).MergeCells = true;
}
}
m_objExcel.get_Range(m_objSheet.Cells[2, 3], m_objSheet.Cells[3, 6]).MergeCells = true;
m_objExcel.get_Range(m_objSheet.Cells[2, 10], m_objSheet.Cells[2, 14]).MergeCells = true;
m_objExcel.get_Range(m_objSheet.Cells[2, 15], m_objSheet.Cells[2, 24]).MergeCells = true;
m_objExcel.get_Range(m_objSheet.Cells[2, 25], m_objSheet.Cells[2, 26]).MergeCells = true;
m_objExcel.get_Range(m_objSheet.Cells[4, 20], m_objSheet.Cells[4, 21]).MergeCells = true;
m_objExcel.get_Range(m_objSheet.Cells[3, 20], m_objSheet.Cells[3, 24]).MergeCells = true;
//设置表头：
m_objSheet.Cells[2, 1] = "工程名称";
m_objSheet.Cells[2, 2] = "建设单位";
m_objSheet.Cells[2, 3] = "项目经理信息";
m_objSheet.Cells[4, 3] = "项目经理";
m_objSheet.Cells[4, 4] = "性质";
m_objSheet.Cells[4, 5] = "联系方式";
m_objSheet.Cells[4, 6] = "压证项目经理";
m_objSheet.Cells[2, 7] = "中标公示时间";
m_objSheet.Cells[2, 8] = "合同归档时间";
m_objSheet.Cells[2, 9] = "合同订立时间";
m_objSheet.Cells[2, 10] = "工程概况";
m_objSheet.Cells[3, 10] = "工程地址";
m_objSheet.Cells[3, 11] = "工程内容";
m_objSheet.Cells[3, 12] = "合同工期";
m_objSheet.Cells[3, 13] = "质量目标";
m_objSheet.Cells[3, 14] = "安全目标";
m_objSheet.Cells[2, 15] = "合同价款及支付";
m_objSheet.Cells[3, 15] = "合同价款（元）";
m_objSheet.Cells[3, 16] = "履约保证金";
m_objSheet.Cells[3, 17] = "取费标准";
m_objSheet.Cells[3, 18] = "让利";
m_objSheet.Cells[3, 19] = "付款方式";
m_objSheet.Cells[3, 20] = "支付额";
m_objSheet.Cells[4, 20] = "预付款";
m_objSheet.Cells[5, 20] = "金额";
m_objSheet.Cells[5, 21] = "扣回方式";
m_objSheet.Cells[4, 22] = "工程进度款";
m_objSheet.Cells[4, 23] = "结算办理时限";
m_objSheet.Cells[4, 24] = "结算后收款";
m_objSheet.Cells[2, 25] = "工程开工日期";
m_objSheet.Cells[3, 25] = "类别";
m_objSheet.Cells[3, 26] = "日期";
m_objSheet.Cells[2, 27] = "是否签订目标责任书";
m_objSheet.Cells[2, 28] = "是否公示";
m_objSheet.Cells[2, 29] = "备注";
//写入数值
for (int r = 0; r < list.Count; r++)
{
int t = r + 6;
m_objSheet.Cells[t, 1] = list[r].PName;
m_objSheet.Cells[t, 2] = list[r].Part;
m_objSheet.Cells[t, 3] = list[r].PManager;
m_objSheet.Cells[t, 4] = list[r].PProperty;
m_objSheet.Cells[t, 5] = list[r].Contact;
m_objSheet.Cells[t, 6] = list[r].YZManager;
m_objSheet.Cells[t, 7] = list[r].BidTime;
m_objSheet.Cells[t, 8] = list[r].GuiTime;
m_objSheet.Cells[t, 9] = list[r].AssignTime;
m_objSheet.Cells[t, 10] = list[r].PAddress;
m_objSheet.Cells[t, 11] = list[r].PContent;
m_objSheet.Cells[t, 12] = list[r].TimeLimit;
m_objSheet.Cells[t, 13] = list[r].QualityGoal;
m_objSheet.Cells[t, 14] = list[r].SafeGoal;
m_objSheet.Cells[t, 15] = list[r].CMoney;
m_objSheet.Cells[t, 16] = list[r].EnsureMoney;
m_objSheet.Cells[t, 17] = list[r].MLevel;
m_objSheet.Cells[t, 18] = list[r].RL;
m_objSheet.Cells[t, 19] = list[r].PayType;
m_objSheet.Cells[t, 20] = list[r].YFMoney;
m_objSheet.Cells[t, 21] = list[r].KHWay;
m_objSheet.Cells[t, 22] = list[r].ProcessMoney;
m_objSheet.Cells[t, 23] = list[r].JSTimeLimit;
m_objSheet.Cells[t, 24] = list[r].JSMoney;
m_objSheet.Cells[t, 25] = list[r].OpenType;
m_objSheet.Cells[t, 26] = list[r].OpenTime;
m_objSheet.Cells[t, 27] = list[r].GoalBook;
m_objSheet.Cells[t, 28] = list[r].ShowTip;
m_objSheet.Cells[t, 29] = list[r].Remarks;
}
//保存文件
if (saveFileName != "")
{
try
{
m_objBook.Saved = true;
m_objBook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
}
}
else
{
fileSaved = false;
}
//释放Excel对应的对象
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (m_objSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
m_objSheet = null;
}
if (m_objBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
m_objBook = null;
}
if (m_objBooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
m_objBooks = null;
}
m_objExcel.Quit();
if (m_objExcel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
m_objExcel = null;
}
GC.Collect();
return fileSaved;
}
------------------------------------------------------
从excel报表导入数据库的案例：
（1）先由excel数据转为datatable数据：
public static System.Data.DataTable DataTableforExcel(string strExcelFileName, string strSheetName,int columnsCount)
{
System.Data.DataTable dt = new System.Data.DataTable();
for (int i = 0; i <columnsCount; i++)
{
dt.Columns.Add(new DataColumn());
}
Excel.Application excel = new Excel.Application();
Excel.Workbook m_objBook = (Excel.Workbook)(excel.Workbooks.Add(strExcelFileName));
Worksheet sheet = null;
foreach (Worksheet wsheet in m_objBook.Worksheets)
{
if (wsheet.Name == strSheetName)
{
sheet = wsheet;
break;
}
}
if (sheet != null)
{
int row = 2;
while (true)
{
Range rName = sheet.Cells[row, 1] as Range;
if (rName.Text.ToString().Trim().Length == 0)
{
break;
}
DataRow dr = dt.NewRow();
for (int i = 0; i <columnsCount; i++)
{
Range rContent = sheet.Cells[row, i + 1] as Range;
dr[i] = rContent.Text;
}
dt.Rows.Add(dr);
row += 1;
}
}
excel.Quit();
return dt;
}
}
------------调用方法通过遍历数据行循环插入即可；
