using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Collections; using System.IO; using System.Runtime.InteropServices;
public static string path输出 = Directory.GetCurrentDirectory() + @"\输出"; public static DataTable DT_日立清单_辅助000 = new DataTable();
加载Excel，将内容填充到一个datatable里去，修改后，可以作为datagridview的数据源
public static void ImportDT_日立清单_辅助000() { ... ArrayList filename = GetFileName(excel的路径) //obj.ToString()是excel的完全路径,excel可以有多个，foreach(object obj in filename)... string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + obj.ToString() + ";Extended Properties=" + "\"" + "Excel 8.0;HDR=No;IMEX=1" + "\"";//没有标题，只读 OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); ////返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等 //System.Data.DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); ////包含excel中表名的字符串数组 //string[] strTableNames = new string[dtSheetName.Rows.Count]; //for (int k = 0; k < dtSheetName.Rows.Count; k++) //{ // strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString(); // //MessageBox.Show(strTableNames[k]); //} ////从指定的表明查询数据,可先把所有表明列出来供用户选择 ////string strExcel = "select * from [" + strTableNames[0] + "] "; string strExcel = "select F1,F2,F3,F4,F5 from [Sheet1$] where ( F4<> \"\" and F4 not like \"%工号%\")"; OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(dt); conn.Close(); conn.Dispose(); ... //根据dt，补充内容得到DT_日立清单_辅助000 for (int i = 0; i <= dt.Rows.Count - 1; i++) { DataRow row = DT_日立清单_辅助000.NewRow(); row["ID2"] = (i + 1).ToString(); row["订单号"] = dt.Rows[i][0].ToString(); row["工号"] = dt.Rows[i][1].ToString(); row["数量"] = dt.Rows[i][2].ToString(); row["导入文件"] = Path.GetFileName(obj.ToString()); DT_日立清单_辅助000.Rows.Add(row); } }
创建一个空的datatable，作为datagridview的数据源
public static DataTable CreatNullDT_日立清单_辅助000() { DataTable data = new DataTable(); DataColumn do1 = new DataColumn("ID", typeof(int)); do1.AllowDBNull = false; do1.AutoIncrement = true; do1.AutoIncrementSeed = 1; do1.AutoIncrementStep = 1; data.Columns.Add(do1); do1 = new DataColumn("ID2", typeof(string)); data.Columns.Add(do1); do1 = new DataColumn("订单号", typeof(string)); data.Columns.Add(do1); do1 = new DataColumn("工号", typeof(string)); data.Columns.Add(do1); do1 = new DataColumn("数量", typeof(string)); data.Columns.Add(do1); do1 = new DataColumn("导入文件", typeof(string)); data.Columns.Add(do1); return data; }
//根据文件夹路径得到全部文件路径（完整）数组 public static ArrayList GetFileName(string dirPath) { ArrayList list = new ArrayList(); if (Directory.Exists(dirPath)) { list.AddRange(Directory.GetFiles(dirPath)); } return list; }
删除清空一个目录下所有文件
private void btnClear_Click(object sender, EventArgs e) { ArrayList filename = MDIParent1.GetFileName(MDIParent1.path输出); foreach (object obj in filename) { File.Delete(obj.ToString()); } }
代码里有创建记事本，写入内容，关闭，再追加内容，关闭。FileMode.Create，FileMode.Append。多余的内容很多，只能自己看看。
private void btnOut_Click(object sender, EventArgs e) { this.Text = "正在输出……"; if (MDIParent1.DT_交期变更.Rows.Count < 2) { MessageBox.Show("交期变更没有导入"); return; } if (MDIParent1.DT_日立清单_辅助000.Rows.Count < 2) { MessageBox.Show("日立清单_辅助000没有导入"); return; } if (MDIParent1.DT_工艺卡_辅助00.Rows.Count < 2) { MessageBox.Show("工艺卡_辅助00没有导入"); return; } if (MDIParent1.DT_工艺卡_数据00.Rows.Count < 2) { MessageBox.Show("工艺卡_数据00没有导入"); return; } progressBar1.Maximum = MDIParent1.DT_交期变更.Rows.Count; progressBar1.Value = 0; string str = MDIParent1.path输出 + "\\" + DateTime.Now.Year.ToString().Trim() + DateTime.Now.Month.ToString().Trim() + DateTime.Now.Day.ToString().Trim() + DateTime.Now.Hour.ToString().Trim() + DateTime.Now.Minute.ToString().Trim() + DateTime.Now.Millisecond.ToString().Trim() + ".txt"; FileStream fs = new FileStream(str, FileMode.Create); StreamWriter sw = new StreamWriter(fs); sw.Write("日立清单，只显示“常规支架”和“空白”。没有把工号打掉，即同一订单两个工号下同一个图号，显示两条记录\r\n"); sw.Flush(); sw.Close(); fs.Close(); for (int i = 0; i <= MDIParent1.DT_交期变更.Rows.Count - 1; i++) { progressBar1.Value = i + 1; //要取消的台帐日期、订单号和工号 string taizhangriqi = MDIParent1.DT_交期变更.Rows[i][2].ToString().Trim(); string dingdanhao = MDIParent1.DT_交期变更.Rows[i][1].ToString().Trim(); string gonghao = MDIParent1.DT_交期变更.Rows[i][3].ToString().Trim(); fs = new FileStream(str, FileMode.Append); sw = new StreamWriter(fs); sw.Write("交期变更：台帐日期：" + taizhangriqi + "订单号：" + dingdanhao + "工号：" + gonghao + "\r\n"); sw.Flush(); sw.Close(); fs.Close(); for (int i2 = 0; i2 <= MDIParent1.DT_日立清单_辅助000.Rows.Count - 1; i2++) { //台帐日期、订单号、工号 if (MDIParent1.DT_日立清单_辅助000.Rows[i2][2].ToString().Trim() == taizhangriqi && MDIParent1.DT_日立清单_辅助000.Rows[i2][5].ToString().Trim() == dingdanhao && MDIParent1.DT_日立清单_辅助000.Rows[i2][6].ToString().Trim() == gonghao) { string tuhao = MDIParent1.DT_日立清单_辅助000.Rows[i2][4].ToString().Trim(); string shuliang = MDIParent1.DT_日立清单_辅助000.Rows[i2][7].ToString().Trim(); string daoruwenjian = MDIParent1.DT_日立清单_辅助000.Rows[i2][8].ToString().Trim(); string hang = MDIParent1.DT_日立清单_辅助000.Rows[i2][1].ToString().Trim(); fs = new FileStream(str, FileMode.Append); sw = new StreamWriter(fs); sw.Write("日立清单里第" + (i2 + 1).ToString().Trim() + "条记录找到：台帐日期：" + taizhangriqi + "订单号：" + dingdanhao + "工号：" + gonghao + "图号：" + tuhao + "数量：" + shuliang + "导入文件：" + daoruwenjian + "第" + hang + "条\r\n"); sw.Flush(); sw.Close(); fs.Close(); for (int i3 = 0; i3 <= MDIParent1.DT_工艺卡_辅助00.Rows.Count - 1; i3++) { //台帐日期、订单号、图号 //123456~458:123.1 string str3 = MDIParent1.DT_工艺卡_辅助00.Rows[i3][2].ToString().Trim().Split(':')[0];//单号123456~458 if (MDIParent1.DT_工艺卡_辅助00.Rows[i3][3].ToString().Trim() == "台帐日期:" + taizhangriqi && (MDIParent1.DT_工艺卡_辅助00.Rows[i3][2].ToString().Trim().Contains("单号" + dingdanhao + ":") || (str3.Contains("~") && (str3.Split('~')[0].Substring(0, str3.Split('~')[0].Length - str3.Split('~')[1].Length) + str3.Split('~')[1]) == ("单号" + dingdanhao))) && MDIParent1.DT_工艺卡_辅助00.Rows[i3][4].ToString().Trim().Split('→')[1].Split(':')[1] == tuhao) { string dingdanhao3 = MDIParent1.DT_工艺卡_辅助00.Rows[i3][2].ToString().Trim(); string shuliang3 = MDIParent1.DT_工艺卡_辅助00.Rows[i3][5].ToString().Trim().Split('→')[0].Split('=')[1]; string daoruwenjian3 = MDIParent1.DT_工艺卡_辅助00.Rows[i3][6].ToString().Trim(); string hang3 = MDIParent1.DT_工艺卡_辅助00.Rows[i3][1].ToString().Trim(); string pathAndName3 = MDIParent1.path输出 + "\\" + MDIParent1.DT_工艺卡_辅助00.Rows[i3][6].ToString().Trim(); //if(MDIParent1.DT_工艺卡_数据00.Rows[i3 + 1][7].ToString() !=shuliang3) //{ // MessageBox.Show("工艺卡数据00数量和辅助00数量不一样。数据00的" + MDIParent1.DT_工艺卡_数据00.Rows[i3 + 1][0].ToString()); // return; //} MDIParent1.DT_工艺卡_数据00.Rows[i3 + 1][7] = (int.Parse(shuliang3) - int.Parse(shuliang)).ToString(); fs = new FileStream(str, FileMode.Append); sw = new StreamWriter(fs); sw.Write("工艺卡里第" + (i3 + 1).ToString().Trim() + "条记录找到：台帐日期" + taizhangriqi + "订单号" + dingdanhao3 + "图号" + tuhao + "数量" + shuliang3 + "导入文件" + daoruwenjian3 + "第" + hang3 + "条\r\n"); sw.Flush(); sw.Close(); fs.Close(); } } } } } dGV工艺卡_数据00.DataSource = MDIParent1.DT_工艺卡_数据00; progressBar1.Value = 0; this.Text = "Frm工艺卡_数据00"; MessageBox.Show("ok"); }
private void btnToExcel_Click(object sender, EventArgs e) { if (MDIParent1.DT_工艺卡_数据00.Rows.Count < 5) { MessageBox.Show("没有数据可以导出"); return; } this.Text = "正在导出……"; ToExcel2(MDIParent1.DT_工艺卡_数据00, saveFileDialog1); this.Text = "Frm工艺卡_数据00"; }
将内存中的datatable保存到Excel（批量保存，不是一格一格保存），修改下可以将datagridview保存到Excel
public void ToExcel2(DataTable dt, SaveFileDialog saveFileDialog) { saveFileDialog.Filter = "Execl files (*.xls)|*.xls"; saveFileDialog.FilterIndex = 0; saveFileDialog.RestoreDirectory = true; //saveFileDialog.CreatePrompt = true; saveFileDialog.Title = "导出文件保存路径"; string strName = string.Empty; if (saveFileDialog.ShowDialog() == DialogResult.OK) { strName = saveFileDialog.FileName; } if (strName.Length == 0) { MessageBox.Show("导出文件保存路径为空"); return; } System.Reflection.Missing miss = System.Reflection.Missing.Value; //创建EXCEL对象appExcel,Workbook对象,Worksheet对象,Range对象 Microsoft.Office.Interop.Excel.Application appExcel; appExcel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbookData; Microsoft.Office.Interop.Excel.Worksheet worksheetData; Microsoft.Office.Interop.Excel.Range rangedata; //设置对象不可见 appExcel.Visible = false; /* 在调用Excel应用程序，或创建Excel工作簿之前，记着加上下面的两行代码 * 这是因为Excel有一个Bug，如果你的操作系统的环境不是英文的，而Excel就会在执行下面的代码时，报异常。 */ System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); workbookData = appExcel.Workbooks.Add(miss); worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.Add(miss, miss, miss, miss); //给工作表赋名称 worksheetData.Name = "saved"; // 保存到WorkSheet的表头，你应该看到，是一个Cell一个Cell的存储，这样效率特别低，解决的办法是，使用Rang，一块一块地存储到Excel for (int i = 0; i < dt.Columns.Count; i++) { worksheetData.Cells[1, i + 1] = dt.Columns[i].ColumnName; } //先给Range对象一个范围为A2开始，Range对象可以给一个CELL的范围，也可以给例如A1到H10这样的范围 //因为第一行已经写了表头，所以所有数据都应该从A2开始 rangedata = worksheetData.get_Range("A2", miss); Microsoft.Office.Interop.Excel.Range xlRang = null; //iRowCount为实际行数，最大行 int iRowCount = dt.Rows.Count; int iParstedRow = 0, iCurrSize = 0; //iEachSize为每次写行的数值，可以自己设置，每次写1000行和每次写2000行大家可以自己测试下效率 int iEachSize = 1000; //iColumnAccount为实际列数，最大列数 int iColumnAccount = dt.Columns.Count; //在内存中声明一个iEachSize×iColumnAccount的数组，iEachSize是每次最大存储的行数，iColumnAccount就是存储的实际列数 object[,] objVal = new object[iEachSize, iColumnAccount]; try { progressBar1.Maximum = dt.Rows.Count; iCurrSize = iEachSize; while (iParstedRow < iRowCount) { if ((iRowCount - iParstedRow) < iEachSize) iCurrSize = iRowCount - iParstedRow; //用FOR循环给数组赋值 for (int i = 0; i < iCurrSize; i++) { for (int j = 0; j < iColumnAccount; j++) { objVal[i, j] = dt.Rows[i + iParstedRow][j].ToString(); } progressBar1.Value++; System.Windows.Forms.Application.DoEvents(); } /* * 建议使用设置断点研究下哈 * 例如A1到H10的意思是从A到H，第一行到第十行 * 下句很关键，要保证获取Sheet中对应的Range范围 * 下句实际上是得到这样的一个代码语句xlRang = worksheetData.get_Range("A2","H100"); * 注意看实现的过程 * 'A' + iColumnAccount - 1这儿是获取你的最后列，A的数字码为65，大家可以仔细看下是不是得到最后列的字母 * iParstedRow + iCurrSize + 1获取最后行 * 若WHILE第一次循环的话这应该是A2,最后列字母+最后行数字 * iParstedRow + 2要注意，每次循环这个值不一样，他取决于你每次循环RANGE取了多大，循环了几次，也就是iEachSize设置值的大小哦 */ xlRang = worksheetData.get_Range("A" + ((int)(iParstedRow + 2)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString() + ((int)(iParstedRow + iCurrSize + 1)).ToString()); // 调用Range的Value2属性，把内存中的值赋给Excel xlRang.Value2 = objVal; iParstedRow = iParstedRow + iCurrSize; } //保存工作表 worksheetData.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss); workbookData.Close(); appExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang); xlRang = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetData); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookData); //System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel); progressBar1.Value = 0; //调用方法关闭EXCEL进程，大家可以试下不用的话如果程序不关闭在进程里一直会有EXCEL.EXE这个进程并锁定你的EXCEL表格 KillSpecialExcel(appExcel); GC.Collect(); MessageBox.Show("数据已经成功导出到：" + saveFileDialog.FileName.ToString(), "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show(ex.Message); return; } // 别忘了在结束程序之前恢复你的环境！ System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI; }
public void KillSpecialExcel(Microsoft.Office.Interop.Excel.Application m_objExcel) { try { if (m_objExcel != null) { int lpdwProcessId; GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId); System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
#region 结束EXCEL.EXE进程的方法 /// <summary> /// 结束EXCEL.EXE进程的方法 /// </summary> /// <param name="m_objExcel">EXCEL对象</param> [DllImport("user32.dll", SetLastError = true)] static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId); #endregion
