下面咱们看图说话：
1.规范输入。系统默认应输入“星期X”，如果用户输入特殊字符@#￥%！或其他字符，在这里会给个提醒，同时限制只能输入三个字符
string str = txtWeek.Text.Trim();
string[] strchar = { "星期一","星期二","星期三","星期四","星期五","星期六","星期日" };
bool exists = ((IList)strchar).Contains(str);
if (exists == false)
{
MessageBox.Show("只能输入“星期X”这种格式！", "提示");
}
else
{
}
当然，要添加引用
using System.Reflection;
using System.Collections;2.如何实现排班
数据库里建立三张表，分别存放二日班可选人员、周四班可选人员、一三五六班可选人员
D层写三个方法，分别对应三个表，调用哪个方法，就从哪个表随机获取一名同学，并把它插入新值班表
根据界面输入的星期X，依次调用D层方法
(这时发现数据库设计有些不合规范……其实完全可以单表操作)
3.如何将DataTable中的数据直接导出到Excel
测试的时候，程序退出了，但进程里的EXCEL.exe不退出，网上的方法不奏效，暂时找不到其他方法，所以用提示框+杀死所有Excel进程的方式保证下次再运行程序不会弹出多个excel
private void btnExcel_Click(object sender, EventArgs e)
{
if (System.Diagnostics.Process.GetProcessesByName("EXCEL").ToList().Count > 0)
{
//系统进程中存在Excel.exe
MessageBox.Show("请保存并关闭其他已打开的Excel文件，否则[导出]不能成功！");
}
else
{
//不存在
arrangeBAO abao = new arrangeBAO();
DataTable dt = abao.ToExcel();
string strFileName = @"C:\新值班表.xlsx";
DataTabletoExcel(dt, strFileName);//调用函数
EndReport();
killProcessThread();//杀掉不死进程
killExcel();
open();
btnExcel.Enabled = false;
}
}
public Excel.Application m_xlApp = null;
/// <summary>
/// 将DataTable数据导出到Excel表
/// </summary>
/// <param name="tmpDataTable">要导出的DataTable</param>
/// <param name="strFileName">Excel的保存路径及名称</param>
public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
long rowNum = tmpDataTable.Rows.Count;//行数
int columnNum = tmpDataTable.Columns.Count;//列数
Excel.Application m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = false;//不显示更改提示
m_xlApp.Visible = false;
Excel.Workbooks workbooks = m_xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
try
{
if (rowNum > 65536)//单张Excel表格最大行数
{
long pageRows = 65535;//定义每页显示的行数,行数必须小于65536
int scount = (int)(rowNum / pageRows);//导出数据生成的表单数
if (scount * pageRows < rowNum)//当总行数不被pageRows整除时，经过四舍五入可能页数不准
{
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++)
{
if (sc > 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(
missing, missing, missing, missing);//添加一个sheet
}
else
{
worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1
}
string[,] datas = new string[pageRows + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;//表头信息
}
Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
int result;
if (pageRows * sc >= rowNum)
{
result = (int)rowNum;
}
else
{
result = int.Parse((pageRows * sc).ToString());
}
for (r = init; r < result; r++)
{
index = index + 1;
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;//Sheet表最大化
range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
}//排班的应该走这儿，就百条数据
else
{
string[,] datas = new string[rowNum + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;
}
Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int r = 0;
for (r = 0; r < rowNum; r++)
{
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
workbook.Saved = true;
//保存文件
workbook.SaveCopyAs(strFileName);
//workbook.SaveAs(strFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
EndReport();
}
}
/// <summary>
/// 退出报表时关闭Excel和清理垃圾Excel进程
/// </summary>
public void EndReport()
{
object missing = System.Reflection.Missing.Value;
try
{
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
}
catch { }
finally
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
m_xlApp = null;
}
catch { }
try
{
//清理垃圾进程
this.killProcessThread();
}
catch { }
GC.Collect();
}
}
/// <summary>
/// 杀掉不死进程
/// </summary>
public void killProcessThread()
{
ArrayList myProcess = new ArrayList();
for (int i = 0; i < myProcess.Count; i++)
{
try
{
System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
}
catch { }
}
}
//保存后打开值班文件
public void open()
{
//定义一个ProcessStartInfo实例
System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo();
//设置启动进程的初始目录
info.WorkingDirectory = Application.StartupPath;
//设置启动进程的应用程序或文档名
info.FileName = @"C:\新值班表.xlsx";
//设置启动进程的参数
info.Arguments = "";
//启动由包含进程启动信息的进程资源
try
{
System.Diagnostics.Process.Start(info);
}
catch (System.ComponentModel.Win32Exception we)
{
MessageBox.Show(this, we.Message);
return;
}
}
//杀掉任务管理器里的所有excel进程
public static void killExcel()
{
foreach (Process p in Process.GetProcessesByName("EXCEL"))
{
try
{
p.Kill();
}
catch
{
//在杀死非应用程序开启的进程时,会抛出错误,直接抛弃错误,不用处理。
}
}
}
2809
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
