导出Excel

做统计类的项目是不可比避免的会遇到把数据做成文档

下面是Excel问当的生成

 public class ExportDataHelper
    {
        //路径
        public static string Path
        {
            get;
            set;
        }
        //名称加后缀名
        public static string FileName
        {
            get;
            set;
        }
        public static void ExportAttendance(DataTable dsAtt)
        {
            try
            {
                if (dsAtt != null && dsAtt.Rows.Count > 0)
                {
                    XlsDocument doc = new XlsDocument();
                    Workbook wbk = doc.Workbook;
                    Worksheet sht = wbk.Worksheets.Add("Test Sheet");
                    MergeArea meaA;
                    int rowcount = 1;
                    //cell样式
                    XF cellXF = doc.NewXF();//为xls生成一个XF实例（XF是cell格式对象）
                    cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
                    cellXF.Font.FontName = "宋体";//设定字体
                    cellXF.Font.Height = 15 * 15;//设定字大小（字体大小是以 1/20 point 为单位的）
                    cellXF.UseBorder = true;//使用边框
                    cellXF.BottomLineStyle = 1;//设定边框底线为粗线
                    cellXF.BottomLineColor = Colors.Blue;//设定颜色为暗
                    cellXF.TopLineColor = Colors.Blue;
                    cellXF.TopLineStyle = 1;
                    cellXF.RightLineColor = Colors.Blue;
                    cellXF.RightLineStyle = 1;
                    cellXF.LeftLineColor = Colors.Blue;
                    cellXF.LeftLineStyle = 1;
                    for (int i = 0; i < dsAtt.Columns.Count; i++)
                    {
                        sht.Cells.AddValueCellXF(1, i + 1, dsAtt.Columns[i].ColumnName, cellXF);
                    }
                    for (int j = 0; j < dsAtt.Rows.Count; j++)
                    {
                        rowcount++;
                        for (int c = 0; c < dsAtt.Columns.Count; c++)
                        {
                            //生成格子
                            sht.Cells.AddValueCellXF(rowcount, c + 1, Convert.ToString(dsAtt.Rows[j][dsAtt.Columns[c].ColumnName]), cellXF);
                        }
                    }
                    doc.FileName = FileName;
                    doc.Save(Path, true);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
       
    }


----------------------------------
        //改变列名   改变列名过后再再导出Excel
        public static DataTable CreateNewDiskTable(DataTable data)
        {
            var table = new DataTable();
            GetNewDiskColumn(table);
            foreach (DataRow row in data.Rows)
            {
                var newRow = table.NewRow();
                newRow["邮寄会员"] = row["Name"];
                newRow["邮寄地址"] = row["Address"];
                newRow["邮寄电话"] = row["Phone"];
                //newRow["邮编"] = row["ConfirmZipCode"];
                table.Rows.Add(newRow);
            }
            return table;
        }
        private static void GetNewDiskColumn(DataTable dt)
        {
            DataColumn dcName = new DataColumn();
            dcName.ColumnName = "邮寄会员";
            dt.Columns.Add(dcName);
            DataColumn dcAddress = new DataColumn();
            dcAddress.ColumnName = "邮寄地址";
            dt.Columns.Add(dcAddress);
            DataColumn dcPhone = new DataColumn();
            dcPhone.ColumnName = "邮寄电话";
            dt.Columns.Add(dcPhone);
            //DataColumn dcZipCode = new DataColumn();
            //dcZipCode.ColumnName = "邮编";
            //dt.Columns.Add(dcZipCode);
        }