using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using Microsoft.Office; using Microsoft.Office.Interop.Excel; using System.IO; using System.Reflection; using System.Data; using System.Data.OleDb; namespace ExcelDemo { public partial class Form1 : Form { public Form1() { InitializeComponent(); OutLog("**************欢迎使用**************"); } private void button1_Click(object sender, EventArgs e) { OpenFileDialog open = new OpenFileDialog(); //定义一个文件打开控件 OpenFileDialog ofd = new OpenFileDialog(); //设置打开对话框的初始目录，默认目录为exe运行文件所在的路径 //ofd.InitialDirectory = Application.StartupPath; //设置打开对话框的标题 ofd.Title = "请选择要打开的文件"; //设置打开对话框可以多选 ofd.Multiselect = true; //设置对话框打开的文件类型 ofd.Filter = "excel03文件(*.xls)|*.xls|excel07文件(*.xlsx)|*.xlsx"; //设置文件对话框当前选定的筛选器的索引 ofd.FilterIndex = 2; //设置对话框是否记忆之前打开的目录 ofd.RestoreDirectory = true; if (ofd.ShowDialog() == DialogResult.OK) { //获取用户选择的文件完整路径 string[] filePath = ofd.FileNames; //获取对话框中所选文件的文件名和扩展名，文件名不包括路径 string[] fileName = ofd.SafeFileNames; for (int i = 0; i < fileName.Length; i++) { OutLog("用户选择的文件目录为:" + filePath[i]); OutLog("用户选择的文件名称为:" + fileName[i]); checkedListBox1.Items.Add(filePath[i], true); } //OutLog("**************选中文件的内容**************"); //using (FileStream fsRead = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Read)) //{ // //定义二进制数组 // byte[] buffer = new byte[1024 * 1024 * 5]; // //从流中读取字节 // int r = fsRead.Read(buffer, 0, buffer.Length); // OutLog(Encoding.Default.GetString(buffer, 0, r)); //} } } /// <summary> /// 输出日志 /// </summary> /// <param name="strLog"></param> private void OutLog(string strLog) { //如果日志信息长度超过1000行，则自动清空 if (txt_FileInfo.GetLineFromCharIndex(txt_FileInfo.Text.Length) > 1000) { //清空文本 txt_FileInfo.Clear(); } txt_FileInfo.AppendText(DateTime.Now.ToString("HH:mm:ss ") + strLog + " "); } private void linkLabel1_Click(object sender, EventArgs e) { for (int i = 0; i < checkedListBox1.Items.Count; i++) { checkedListBox1.SetItemCheckState(i, CheckState.Checked); } } private void linkLabel2_Click(object sender, EventArgs e) { for (int i = 0; i < checkedListBox1.Items.Count; i++) { checkedListBox1.SetItemCheckState(i, CheckState.Unchecked); } } private void button2_Click(object sender, EventArgs e) { OutLog("准备中..."); #region init string path = @"D:" + DateTime.Now.Second + DateTime.Now.Millisecond + ".xlsx"; System.Data.DataTable MyDataTable = new System.Data.DataTable(); MyDataTable.Columns.Add(new DataColumn("存档编号", typeof(string))); MyDataTable.Columns.Add(new DataColumn("任务描述", typeof(string))); MyDataTable.Columns.Add(new DataColumn("具体位置", typeof(string))); MyDataTable.Columns.Add(new DataColumn("开始时间", typeof(string))); MyDataTable.Columns.Add(new DataColumn("结束时间", typeof(string))); MyDataTable.Columns.Add(new DataColumn("工作情况", typeof(string))); MyDataTable.Columns.Add(new DataColumn("名称1", typeof(string))); MyDataTable.Columns.Add(new DataColumn("型号1", typeof(string))); MyDataTable.Columns.Add(new DataColumn("数量1", typeof(string))); MyDataTable.Columns.Add(new DataColumn("名称2", typeof(string))); MyDataTable.Columns.Add(new DataColumn("型号2", typeof(string))); MyDataTable.Columns.Add(new DataColumn("数量2", typeof(string))); MyDataTable.Columns.Add(new DataColumn("名称3", typeof(string))); MyDataTable.Columns.Add(new DataColumn("型号3", typeof(string))); MyDataTable.Columns.Add(new DataColumn("数量3", typeof(string))); MyDataTable.Columns.Add(new DataColumn("名称4", typeof(string))); MyDataTable.Columns.Add(new DataColumn("型号4", typeof(string))); MyDataTable.Columns.Add(new DataColumn("数量4", typeof(string))); #endregion DataRow dr; List<string[]> lstrs = new List<string[]>(); for (int i = 0; i < checkedListBox1.Items.Count; i++) { string[] str = new string[18]; System.Data.DataTable dt = ExcelToDS(checkedListBox1.Items[i].ToString()); //string IDCard = dt.Rows[0][2].ToString(); //string miaoshu = dt.Rows[3][0].ToString(); //string Position = dt.Rows[4][0].ToString(); //string StartTime = dt.Rows[5][0].ToString(); //string OverTime = dt.Rows[5][2].ToString(); //string Qingkuang = dt.Rows[6][0].ToString(); //string name_1 = dt.Rows[8][0].ToString(); //string Type_1 = dt.Rows[8][1].ToString(); //string Num_1 = dt.Rows[8][4].ToString(); //string name_2 = dt.Rows[9][0].ToString(); //string Type_2 = dt.Rows[9][1].ToString(); //string Num_2 = dt.Rows[9][4].ToString(); //string name_3 = dt.Rows[10][0].ToString(); //string Type_3 = dt.Rows[10][1].ToString(); //string Num_3 = dt.Rows[10][4].ToString(); //string name_4 = dt.Rows[11][0].ToString(); //string Type_4 = dt.Rows[11][1].ToString(); //string Num_4 = dt.Rows[11][4].ToString(); str[0] = dt.Rows[0][2].ToString().Split('：')[1] + "."; str[1] = dt.Rows[3][0].ToString().Split('：')[1]; str[2] = dt.Rows[4][0].ToString().Split('：')[1]; str[3] = dt.Rows[5][0].ToString().Split('：')[1]; str[4] = dt.Rows[5][2].ToString().Split('：')[1]; str[5] = dt.Rows[6][0].ToString().Split('：')[1]; str[6] = dt.Rows[8][0].ToString().Split('：')[1]; str[7] = dt.Rows[8][1].ToString().Split('：')[1]; str[8] = dt.Rows[8][4].ToString().Split('：')[1]; str[9] = dt.Rows[9][0].ToString().Split('：')[1]; str[10] = dt.Rows[9][1].ToString().Split('：')[1]; str[11] = dt.Rows[9][4].ToString().Split('：')[1]; str[12] = dt.Rows[10][0].ToString().Split('：')[1]; str[13] = dt.Rows[10][1].ToString().Split('：')[1]; str[14] = dt.Rows[10][4].ToString().Split('：')[1]; str[15] = dt.Rows[11][0].ToString().Split('：')[1]; str[16] = dt.Rows[11][1].ToString().Split('：')[1]; str[17] = dt.Rows[11][4].ToString().Split('：')[1]; //DataRow dr = new System.Data.DataRow(); //dr = MyDataTable.NewRow(); //dr["存档编号"] = IDCard; //dr["任务描述"] = miaoshu; //dr["具体位置"] = Position; //dr["开始时间"] = StartTime; //dr["结束时间"] = OverTime; //dr["工作情况"] = Qingkuang; //dr["名称1"] = name_1; //dr["型号1"] = Type_1; //dr["数量1"] = Num_1; //dr["名称2"] = name_2; //dr["型号2"] = Type_2; //dr["数量2"] = Num_2; //dr["名称3"] = name_3; //dr["型号3"] = Type_3; //dr["数量3"] = Num_3; //dr["名称4"] = name_4; //dr["型号4"] = Type_4; //dr["数量4"] = Num_4; //MyDataTable.Rows.Add(dr); lstrs.Add(str); } CreateExcelFile(path, lstrs); OutLog("准备完成！开始添加"); DataSet ds = new System.Data.DataSet(); ds.Tables.Add(MyDataTable); OutLog("OK，处理完成，文件路径：" + path); } public System.Data.DataTable ExcelToDS(string Path) { string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; DataSet ds = null; strExcel = "select * from [sheet1$]"; myCommand = new OleDbDataAdapter(strExcel, strConn); ds = new DataSet(); myCommand.Fill(ds, "table1"); return ds.Tables[0]; } public string CreateNewExcel() { string path = "D:/" + DateTime.Now.Second + DateTime.Now.Millisecond + ".xlsx"; if (!File.Exists(path)) { File.Create(path).Close(); } OutLog("创建Excel文件，路径：" + path); return path; } public void DSToExcel(string Path, DataSet oldds) { //先得到汇总EXCEL的DataSet 主要目的是获得EXCEL在DataSet中的结构 string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Path + ";Extended Properties=Excel 8.0"; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = "select * from [Sheet1$]"; myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); System.Data.OleDb.OleDbCommandBuilder builder = new OleDbCommandBuilder(myCommand); //QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。 builder.QuotePrefix = "["; //获取insert语句中保留字符（起始位置） builder.QuoteSuffix = "]"; //获取insert语句中保留字符（结束位置） //DataSet newds = new DataSet(); myCommand.Fill(oldds, "Table1"); //for (int i = 0; i < oldds.Tables[0].Rows.Count; i++) //{ // //在这里不能使用ImportRow方法将一行导入到news中，因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。在使用ImportRow后newds内有值，但不能更新到Excel中因为所有导入行的DataRowState!=Added // DataRow nrow = newds.Tables["Table1"].NewRow(); // for (int j = 0; j < newds.Tables[0].Columns.Count; j++) // { // nrow[j] = oldds.Tables[0].Rows[i][j]; // } // newds.Tables["Table1"].Rows.Add(nrow); //} myCommand.Update(oldds, "Table1"); myConn.Close(); } public static void CreateExcelFile(string FileName, List<string[]> uu) { if (FileName.Split('.')[FileName.Split('.').Length - 1] == "xlsx")//如果是2007版以后 { //create object Nothing = System.Reflection.Missing.Value; var app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(Nothing); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[1]; worksheet.Name = "Sheet1"; //headline //int i = 1; //for (int i = 0; i < uu.Length; i++) //{ // worksheet.Cells[RowNum + 1, i + 1] = uu[i]; //} worksheet.Cells[1, 1] = "存档编号"; worksheet.Cells[1, 2] = "任务描述"; worksheet.Cells[1, 3] = "具体位置"; worksheet.Cells[1, 4] = "开始时间"; worksheet.Cells[1, 5] = "结束时间"; worksheet.Cells[1, 6] = "工作情况"; worksheet.Cells[1, 7] = "名称1"; worksheet.Cells[1, 8] = "型号1"; worksheet.Cells[1, 9] = "数量1"; worksheet.Cells[1, 10] = "名称2"; worksheet.Cells[1, 11] = "型号2"; worksheet.Cells[1, 12] = "数量2"; worksheet.Cells[1, 13] = "名称3"; worksheet.Cells[1, 14] = "型号3"; worksheet.Cells[1, 15] = "数量3"; worksheet.Cells[1, 16] = "名称4"; worksheet.Cells[1, 17] = "型号4"; worksheet.Cells[1, 18] = "数量4"; for (int i = 0; i < uu.Count; i++) { for (int j = 0; j < uu[i].Length; j++) { worksheet.Cells[i + 2, j + 1] = uu[i][j]; } } //foreach (UUser uu in luu) //{ // worksheet.Cells[1, i] = uu.name; // i++; //} worksheet.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); workBook.Close(false, Type.Missing, Type.Missing); app.Quit(); } //else //{ // HSSFWorkbook wk = new HSSFWorkbook(); //创建一个名称为mySheet的表 // ISheet tb = wk.CreateSheet("Sheet1"); // //创建一行，此行为第二行 // IRow row = tb.CreateRow(1); // for (int i = 0; i < luu.Count; i++) // { // ICell cell = row.CreateCell(i); //在第二行中创建单元格 // cell.SetCellValue(luu[i].name);//循环往第二行的单元格中添加数据 } // using (FileStream fs = File.OpenWrite(FileName)) // { // wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。 // } // } //} } } }