数据导出总结

1.首先引用ExcelLib.cs这个文件。

2.插入如下代码：

DataTable dt = GetTable(resu);
                ExcelLib excHelper = new ExcelLib();
                //write content
                excHelper.WriteCells(dt, 0, 0, true);
                //set style,optional
                excHelper.SetHeaderRowStyle(0, 0, dt.Columns.Count);
                excHelper.SetAltRowStyle(0, 0, dt, true);
                excHelper.SetColumnsWidth(0, dt.Columns.Count, 30);

                string fileName = string.Format("{0}-{1}.xls", Lincoln3R.Resources.Resource.lbl_Report, Lincoln3R.Resources.Resource.Submenu_Report2);
                byte[] buffer = excHelper.GetMemoryStream();
                TransmitFile(fileName, buffer);

3.GetTable方法：举例说明，就是生成一个DataTable。

private DataTable GetTable(IList<TVehicle> countResult)
        {
            //re-do this job, so that the header is in right language.
            columnHead = new string[16]{
                Lincoln3R.Resources.Resource.Lbl_VIN,    
                Lincoln3R.Resources.Resource.Lbl_Last3RServiceDealerCode,
                Lincoln3R.Resources.Resource.Lbl_Last3rServiceAdvisorNo,    
                Lincoln3R.Resources.Resource.Lbl_SegmentNo,
                Lincoln3R.Resources.Resource.Lbl_SegmentDescription,    
                Lincoln3R.Resources.Resource.Lbl_ModelNo,    
                Lincoln3R.Resources.Resource.Lbl_ModelDescription,    
                Lincoln3R.Resources.Resource.Lbl_LicenseNo,    
                Lincoln3R.Resources.Resource.Lbl_InitRegDate,    
                Lincoln3R.Resources.Resource.Lbl_Mileage,    
                Lincoln3R.Resources.Resource.Lbl_lastwarrentyrepairdate,    
                Lincoln3R.Resources.Resource.Lbl_Last3Rstatusupdatedate,    
                Lincoln3R.Resources.Resource.Lbl_TotalWarrantyRepairs,    
                Lincoln3R.Resources.Resource.Lbl_AccumulativeRepairDays,    
                Lincoln3R.Resources.Resource.Lbl_3RRepairs,    
                Lincoln3R.Resources.Resource.Lbl_3RStatus
            };

            DataTable dt = new DataTable();
            //this part of code is generated by excel
            dt.Columns.Add(new DataColumn(columnHead[0]));
            dt.Columns.Add(new DataColumn(columnHead[1]));
            dt.Columns.Add(new DataColumn(columnHead[2]));
            dt.Columns.Add(new DataColumn(columnHead[3]));
            dt.Columns.Add(new DataColumn(columnHead[4]));
            dt.Columns.Add(new DataColumn(columnHead[5]));
            dt.Columns.Add(new DataColumn(columnHead[6]));
            dt.Columns.Add(new DataColumn(columnHead[7]));
            dt.Columns.Add(new DataColumn(columnHead[8]));
            dt.Columns.Add(new DataColumn(columnHead[9]));
            dt.Columns.Add(new DataColumn(columnHead[10]));
            dt.Columns.Add(new DataColumn(columnHead[11]));
            dt.Columns.Add(new DataColumn(columnHead[12]));
            dt.Columns.Add(new DataColumn(columnHead[13]));
            dt.Columns.Add(new DataColumn(columnHead[14]));
            dt.Columns.Add(new DataColumn(columnHead[15]));

            for (int i = 0; i < countResult.Count; i++)
            {
                
                DataRow dr = dt.NewRow();
                dr[0] = countResult[i].Vin;
                dr[1] = countResult[i].Last3rRepairDealerCode;
                dr[2] = (string.IsNullOrWhiteSpace(countResult[i].Last3rsaNo) && string.IsNullOrWhiteSpace(countResult[i].Last3rsaName))?
                    "":string.Format("{0}-{1}",countResult[i].Last3rsaNo,countResult[i].Last3rsaName);
                dr[3] = countResult[i].SegmentNo;
                dr[4] = countResult[i].SegmentDescription;
                dr[5] = countResult[i].ModelNo;
                dr[6] = countResult[i].ModelDescription;
                dr[7] = countResult[i].LicenseNo;
                if (countResult[i].InitialRegistrationDate.HasValue) dr[8] = countResult[i].InitialRegistrationDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
                dr[9] = countResult[i].Mileage.ToString("F0");
                if (countResult[i].LastWarrantyRepairDate.HasValue) dr[10] = countResult[i].LastWarrantyRepairDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
                if (countResult[i].Last3rRepairStatusUpdateDate.HasValue) dr[11] = countResult[i].Last3rRepairStatusUpdateDate.Value.ToString("yyyy-MM-dd HH:mm:ss");
                dr[12] = countResult[i].TotalWarrantyRepairTimes;
                dr[13] = countResult[i].AccumulativeRepairDays;
                dr[14] = countResult[i].Total3rRepairTimes;
                switch( countResult[i].ThreerStatus)
                {
                    case "R":
                        dr[15] = Lincoln3R.Resources.Resource.Tip_Red;
                        break;
                    case "Y":
                        dr[15] = Lincoln3R.Resources.Resource.Tip_Yellow;
                        break;
                    case "O":
                        dr[15] = Lincoln3R.Resources.Resource.Tip_Orange;
                        break;
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }

4.TransmitFile方法是将数据输出。

private void TransmitFile(string fileName, byte[] buffer)
        {
            Response.ContentType = "application/octet-stream;charset=UTF-8";
            if (true)//UserSession.CurrentUser.CurrentCulture == Lincoln3R.Framework.I18N.CultureHelper.I18N_ZH_CN)
            {
                fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
            }
            Response.AddHeader("Content-Disposition", "attachment;  filename=" + fileName);
            Response.BinaryWrite(buffer);
            Response.Flush();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }