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();
}
}
}
}
}