C#读写Excel

于 2019-02-13 10:11:00 发布
阅读量110 收藏
点赞数
文章标签： 操作系统
原文链接：http://www.cnblogs.com/caijiabao/p/10368351.html
版权 
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 + "\r\n");
        }

        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表并保存。

            //        }
            //    }





            //}
        }
    }

}

 

转载于:https://www.cnblogs.com/caijiabao/p/10368351.html

anwuzhi1990
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值