c# 导出EXCEL
来源：互联网 发布：淘宝 评价 期限 编辑：程序博客网 时间：2024/06/05 05:16
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(); } } } }}- C#读取Excel，导出Excel
- C#读取Excel，导出Excel
- C# 导出excel 生成excel
- C# 导出excel
- C#导出EXCEL
- C#导出Excel总结
- C#导出Excel总结
- C#导出Excel总结
- C#导出到EXCEL
- C#导出到EXCEL
- C#导出Excel汇总
- winForm c#导出Excel
- C# 导出Excel代码
- c# datagridview 导出excel
- C#导出到EXCEL
- c# datagridview 导出excel
- C#日记--导出Excel
- C#导出到EXCEL
- 有这样一些故事。。。
- document.viewport.getHeight()返回为0
- 恐慌
- :nth-child选择器
- 生产者消费者模型与双缓冲队列
- c# 导出EXCEL
- Java 注解编程
- 烦人的java.util.concurrent锁
- TD分布式基站技术探讨
- 关于界面底侧或者左右侧出侧一条空白（statusbar的高度问题） 20 pixel blank at bottom of screen
- 消息队列（Message Queue）简介及其使用
- onInterceptTouchEvent和onTouchEvent调用时序
- 求一个数包含多少个1
- C# 5.0 - not quite there yet!
