個人總結導出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;
??????? }
??? }
------------調用方法通過遍歷數據行循環插入即可;
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
