c# 导出EXCEL

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;

namespace MyPracticeConsole
{
    public class RollRealExcel
    {
        static string rowToColumnSql = "declare @s varchar(8000) \r\n" +
            "set @s='select  cast(date as datetime) as Date' \r\n" +
            "select " +
            "@s=@s +',['+a.username+']=max(case when username=''+a.username+'' then  attendancestate else 0 end)' " +
            "from (select username from Userinfo where firm=0 ) a \r\n" +
            "set @s=@s+' from record where Date like '%{0}-{1}%'   group by Date  order by date' \r\n" +
            "exec(@s)";
        //static string sqlConnectionString = "Data Source=SameWayDev02;Database=AttendanceInfo;User ID=sa;Password=sa;";
        static string sqlConnectionString = "Data Source=192.168.1.111;Database=AttendanceInfo;User ID=sa;Password=sa;";
        private static DataTable GetData()
        {
            string sql = string.Format(rowToColumnSql, DateTime.Today.AddMonths(-1).Year, DateTime.Today.AddMonths(-1).Month);
            SqlConnection connection = new SqlConnection(sqlConnectionString);
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
            adapter.SelectCommand.ExecuteNonQuery();
            DataTable table = new DataTable();
            adapter.Fill(table);
            connection.Close();
            DataColumn[] keys = new DataColumn[1];
            keys[0] = table.Columns[0];
            table.PrimaryKey = keys;
            return table;
        }
        private static double GetMark(string State)
        {
            int tempMark = int.Parse(State);
            double acturalMark = -5.00;
            switch (tempMark)
            {
                case 0:
                    acturalMark = -1.00;
                    break;
                case 1:
                    acturalMark = 1.00;
                    break;
                case 21:
                    acturalMark = 0.85;
                    break;
                case 22:
                    acturalMark = 0.70;
                    break;
                case 23:
                    acturalMark = 0.50;
                    break;
                case 41:
                    acturalMark = 0.85;
                    break;
                case 42:
                    acturalMark = 0.70;
                    break;
                case 43:
                    acturalMark = 0.50;
                    break;
                case 51:
                    acturalMark = 0.70;
                    break;
                case 52:
                    acturalMark = 0.55;
                    break;
                case 53:
                    acturalMark = 0.35;
                    break;
                case 54:
                    acturalMark = 0.55;
                    break;
                case 55:
                    acturalMark = 0.40;
                    break;
                case 56:
                    acturalMark = 0.20;
                    break;
                case 57:
                    acturalMark = 0.35;
                    break;
                case 58:
                    acturalMark = 0.20;
                    break;
                default:
                    acturalMark = 0.00;
                    break;
            }
            return acturalMark;
        }
        public static void ExportExcel()
        {
            DataTable table = GetData();
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Execl  files  (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.Title = "导出Excel文件到";

            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                Stream myStream;
                myStream = saveFileDialog.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
                DateTime lastMonth = DateTime.Today.AddMonths(-1);
                int dayCount = DateTime.DaysInMonth(lastMonth.Year, lastMonth.Month);
                int personCount = table.Columns.Count - 1;
                try
                {
                    SqlConnection connection = new SqlConnection(sqlConnectionString);
                    connection.Open();
                    //总天数
                    double[] sumMarks = new double[personCount];
                    string title = string.Format("{0}年{1}月份考勤统计", lastMonth.Year, lastMonth.Month);
                    sw.WriteLine(title);
                    string secondtitle = string.Format("实际考勤天数：{0}", table.Rows.Count);
                    sw.WriteLine(secondtitle);
                    //写标题    
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        if (i == 0)
                        {
                            sw.Write("日期");
                        }
                        else
                        {
                            sw.Write(table.Columns[i].ColumnName);
                        }
                        sw.Write("\t");
                    }
                    sw.WriteLine();

                    //写内容
                    int rowCount = 1;
                    for (int j = 1; j < dayCount + 1; j++)
                    {
                        DateTime time = DateTime.Parse(string.Format("{0}-{1}-{2}", lastMonth.Year, lastMonth.Month, j));
                        DataRow row = table.Rows.Find(time);
                        if (row != null)
                        {
                            for (int k = 0; k < table.Columns.Count; k++)
                            {
                                if (k == 0)
                                {
                                    sw.Write(j);
                                }
                                else
                                {
                                    double mark = GetMark(table.Rows[rowCount - 1][k].ToString());
                                    string markString = mark.ToString("F2");
                                    if (markString == "1.00")
                                    {
                                        sw.Write(markString);
                                        sumMarks[k - 1] += mark;
                                    }
                                    else
                                    {
                                        string sql = string.Format("Select MissReason From Record where date='{0}-{1}-{2}' and UserName='{3}'",
                                            lastMonth.Year, lastMonth.Month, j, table.Columns[k].ColumnName);
                                        SqlCommand command = new SqlCommand(sql, connection);
                                        object a = command.ExecuteScalar();
                                        string missReason = a == null ? string.Empty : a.ToString();
                                        if (missReason == string.Empty)
                                        {
                                            sw.Write("0.00");
                                        }
                                        else
                                        {
                                            sw.Write(markString + missReason);
                                            sumMarks[k - 1] += mark;
                                        }
                                    }
                                }
                                sw.Write("\t");
                            }
                            rowCount++;
                        }
                        else
                        {
                            for (int k = 0; k < table.Columns.Count; k++)
                            {
                                if (k == 0)
                                {
                                    sw.Write(j);
                                }
                                else
                                {
                                    sw.Write("0.00");
                                }
                                sw.Write("\t");
                            }

                        }
                        sw.WriteLine();
                    }
                    sw.Write("汇总\t");
                    for (int i = 0; i < personCount; i++)
                    {
                        sw.Write(sumMarks[i].ToString("F2"));
                        sw.Write("\t");
                    }
                    sw.WriteLine();                    
                    sw.Write("事假加1.0、病假加1.5婚丧假在规定的时间内以及外勤、调休、出差加2.0\t");
                    sw.WriteLine();
                    sw.Close();
                    myStream.Close();

                    connection.Close();

                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.Message);
                    return;
                }
                finally
                {
                    sw.Close();
                    myStream.Close();
                }
            }
        }
    }
}