個(gè)人總結(jié)導(dǎo)出excel報(bào)表的案例：

//導(dǎo)出報(bào)表

??? protected void btnExport_Click(object sender, EventArgs e)

??? {

??????? List<ProOutContract> list = GetDataTableFromIList();

??????? if (list == null || (list != null && list.Count == 0))

??????? {

??????????? Messabox.ShowError(this, "無記錄導(dǎo)出");

??????????? return;

??????? }

??????? string name = "~/File/ExcelFile/工程信息外部合同登記表" + Session.SessionID + ".xls";

??????? string savePath = Server.MapPath(name);

??????? bool f = ExportExcel(name, list, savePath);

??????? FileInfo DownloadFile = new FileInfo(savePath); //設(shè)置要下載的文件

??????? Response.Clear(); //清除緩沖區(qū)流中的所有內(nèi)容輸出

??????? Response.ClearHeaders(); //清除緩沖區(qū)流中的所有頭

??????? Response.Buffer = false; //設(shè)置緩沖輸出為false

??????? //設(shè)置輸出流的 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 內(nèi)容輸出流。

??????? //Response.WriteFile(DownloadFile.FullName);

??????? Response.WriteFile(savePath);

??????? Response.Flush(); //向客戶端發(fā)送當(dāng)前所有緩沖的輸出

??????? File.Delete(savePath);//刪除文件

??????? Response.End(); //將當(dāng)前所有緩沖的輸出發(fā)送到客戶端

??? }

??? //得到報(bào)表數(shù)據(jù)；

??? 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;

??? }

??? //導(dǎo)出報(bào)表

??? public bool ExportExcel(string reportName, List<ProOutContract> list, string saveFileName)

??? {

??????? //獲取Excel進(jìn)程

??????? 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;//設(shè)置所有表格線；

??????? ////第一行設(shè)置

??????? 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;//水平對(duì)齊方式

??????? m_objExcel.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, colnum]).VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直對(duì)齊方式

??????? m_objExcel.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, colnum]).Font.Size = 14;

??????? m_objSheet.Cells[1, 1] = "合同簽訂進(jìn)程一覽表";

??????? m_objExcel.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[totalCount, 1]).ColumnWidth = 20;//設(shè)置列的寬度；

???????
m_objExcel.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[5, colnum]).HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平對(duì)齊方式

??????? m_objExcel.get_Range(m_objSheet.Cells[2, 1], m_objSheet.Cells[5, colnum]).VerticalAlignment = XlVAlign.xlVAlignCenter;//垂直對(duì)齊方式

??????? //--

? ??????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;

??????? //設(shè)置表頭：

??????? m_objSheet.Cells[2, 1] = "工程名稱";

??????? m_objSheet.Cells[2, 2] = "建設(shè)單位";

??????? m_objSheet.Cells[2, 3] = "項(xiàng)目經(jīng)理信息";

??????? m_objSheet.Cells[4, 3] = "項(xiàng)目經(jīng)理";

??????? m_objSheet.Cells[4, 4] = "性質(zhì)";

??????? m_objSheet.Cells[4, 5] = "聯(lián)系方式";

??????? m_objSheet.Cells[4, 6] = "壓證項(xiàng)目經(jīng)理";

??? ????m_objSheet.Cells[2, 7] = "中標(biāo)公示時(shí)間";

??????? m_objSheet.Cells[2, 8] = "合同歸檔時(shí)間";

??????? m_objSheet.Cells[2, 9] = "合同訂立時(shí)間";

??????? m_objSheet.Cells[2, 10] = "工程概況";

??????? m_objSheet.Cells[3, 10] = "工程地址";

?

??????? m_objSheet.Cells[3, 11] = "工程內(nèi)容";

??? ????m_objSheet.Cells[3, 12] = "合同工期";

??????? m_objSheet.Cells[3, 13] = "質(zhì)量目標(biāo)";

??????? m_objSheet.Cells[3, 14] = "安全目標(biāo)";

??????? m_objSheet.Cells[2, 15] = "合同價(jià)款及支付";

??????? m_objSheet.Cells[3, 15] = "合同價(jià)款（元）";

??????? m_objSheet.Cells[3, 16] = "履約保證金";

??????? m_objSheet.Cells[3, 17] = "取費(fèi)標(biāo)準(zhǔn)";

??????? m_objSheet.Cells[3, 18] = "讓利";

??????? m_objSheet.Cells[3, 19] = "付款方式";

??????? m_objSheet.Cells[3, 20] = "支付額";

??????? m_objSheet.Cells[4, 20] = "預(yù)付款";

??????? m_objSheet.Cells[5, 20] = "金額";

??????? m_objSheet.Cells[5, 21] = "扣回方式";

??????? m_objSheet.Cells[4, 22] = "工程進(jìn)度款";

??????? m_objSheet.Cells[4, 23] = "結(jié)算辦理時(shí)限";

??????? m_objSheet.Cells[4, 24] = "結(jié)算后收款";

??????? m_objSheet.Cells[2, 25] = "工程開工日期";

??????? m_objSheet.Cells[3, 25] = "類別";

??????? m_objSheet.Cells[3, 26] = "日期";

??????? m_objSheet.Cells[2, 27] = "是否簽訂目標(biāo)責(zé)任書";

??????? m_objSheet.Cells[2, 28] = "是否公示";

??????? m_objSheet.Cells[2, 29] = "備注";

???????
//寫入數(shù)值

??????? 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對(duì)應(yīng)的對(duì)象

??????? 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報(bào)表導(dǎo)入數(shù)據(jù)庫的案例：

（1）先由excel數(shù)據(jù)轉(zhuǎn)為datatable數(shù)據(jù)：

?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;

??????? }

??? }

------------調(diào)用方法通過遍歷數(shù)據(jù)行循環(huán)插入即可；

C# 導(dǎo)入導(dǎo)出excel文件案例