也来耍下拆分工资条

首先上界面：

再看拆分功能演示：

上代码：

using DevComponents.DotNetBar;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsApp
{
    public partial class FormSalaryBill : Office2007Form
    {
        public FormSalaryBill()
        {
            InitializeComponent();
        }


        /// <summary>
        /// 文件上传打开
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonX1_Click(object sender, EventArgs e)
        {

            //初始化一个OpenFileDialog类
            OpenFileDialog fileDialog = new OpenFileDialog();

            //判断用户是否正确的选择了文件
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                //获取用户选择文件的后缀名
                string extension = Path.GetExtension(fileDialog.FileName);
                //声明允许的后缀名
                string[] str = new string[] { ".doc", " .docx", ".gif", ".jpge", ".jpg", ".xlsx", ".xls" };
                if (!((IList)str).Contains(extension))
                {
                    MessageBox.Show("仅能上传gif,jpge,jpg,xlsx, xls 格式的图片！");
                }
                else
                {
                    //获取用户选择的文件，并判断文件大小不能超过20K，fileInfo.Length是以字节为单位的
                    FileInfo fileInfo = new FileInfo(fileDialog.FileName);
                    if (fileInfo.Length > 204800)
                    {
                        MessageBox.Show("上传的文件不能大于200K");
                    }
                    else
                    {

                        textBoxX1.Text = fileDialog.FileName;
                        
                    }
                }

            }
        }

        /// <summary>
        /// 导入加载excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonX2_Click(object sender, EventArgs e)
        {
         DataSet ds= ToDataTable(textBoxX1.Text, textBoxX1.Text);
         this.dataGridViewX1.DataSource = ds.Tables[0];
         this.dataGridViewX1.Visible = true;
        }


        private int _ReturnStatus;
        private string _ReturnMessage;

        /// <summary>
        /// 执行返回状态
        /// </summary>
        public int ReturnStatus
        {
            get
            {
                return _ReturnStatus;
            }
        }

        /// <summary>
        /// 执行返回信息
        /// </summary>
        public string ReturnMessage
        {
            get
            {
                return _ReturnMessage;
            }
        }


        /// <summary>
        /// 读取导入EXCEL到DataSet
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public DataSet ToDataTable(string filePath, string fileName)
        {
            //判断是否安装EXCEL
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            //if (xlApp == null)
            //{
            //    _ReturnStatus = -1;
            //    _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";
            //    return null;
            //}

            //判断文件是否被其他进程使用            
            Microsoft.Office.Interop.Excel.Workbook workbook;
            try
            {
                workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
            }
            catch
            {
                _ReturnStatus = -1;
                _ReturnMessage = "Excel文件处于开启状态，请先保存并关闭";
                return null;
            }
            //获得所有Sheet名称
            int n = workbook.Worksheets.Count;
            string[] SheetSet = new string[n];
            System.Collections.ArrayList al = new System.Collections.ArrayList();
            for (int i = 1; i <= n; i++)
            {
                SheetSet[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;
            }

            //释放Excel相关对象
            workbook.Close(null, null, null);
            xlApp.Quit();
            if (workbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
            }
            if (xlApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
            }
            GC.Collect();

            //把EXCEL导入到DataSet
            string connStr = "";

            string fileType = System.IO.Path.GetExtension(fileName);

            if (string.IsNullOrEmpty(fileType)) return null;

            if (fileType == ".xls")
            {
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            }
            else
            {
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
            }
            string sql_F = "Select * FROM [{0}]";//可更改Sheet名称，比如sheet2，等等   

            //OleDbConnection conn = null;

            OleDbDataAdapter da = null;

            DataTable dtSheetName = null;

            DataSet ds = new DataSet();
            // 初始化连接，并打开
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {

                try
                {
                    // conn = new OleDbConnection(connStr);

                    conn.Open();

                    // 获取数据源的表定义元数据                       

                    string SheetName = "";

                    dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    // 初始化适配器

                    da = new OleDbDataAdapter();

                    for (int i = 0; i < dtSheetName.Rows.Count; i++)
                    {

                        SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];

                        if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
                        {
                            continue;
                        }


                        da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName), conn);

                        DataSet dsItem = new DataSet();

                        da.Fill(dsItem, "MyTable");
                        ds.Tables.Add(dsItem.Tables[0].Copy());

                    }

                }

                catch (Exception ex)
                {

                }
                finally
                {
                    // 关闭连接
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        da.Dispose();
                        conn.Dispose();
                    }

                }

                return ds;
            }

        }

        /// <summary>
        /// 拆分工资条操作
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonX3_Click(object sender, EventArgs e)
        {
            try
            {
                DataSet ds = ToDataTable(textBoxX1.Text, textBoxX1.Text);
                this.dataGridViewX1.DataSource = ds.Tables[0];
                ExcelSplit(ds.Tables[0]);
            }
            catch { }
        }

        /// <summary>
        /// 拆分excel工资条数据
        /// </summary>
        /// <param name="excelTable"></param>
        public void ExcelSplit(DataTable excelTable)
        {
            //创建工作表
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            sheet.ProtectSheet("123"); //加密Excel,从而实现只读
            //创建表头
            IRow headerrow = sheet.CreateRow(0);
            for (int i = 0; i < excelTable.Columns.Count; i++)
            {
                headerrow.CreateCell(i).SetCellValue(excelTable.Columns[i].ColumnName);
            }

            int index = 0; //拆分个数
            //创建内容
            IRow datarow = sheet.CreateRow(1);
            FileStream stream = null;
            if (!Directory.Exists(@"D:/SalaryBill"))
            {
                Directory.CreateDirectory(@"D:/SalaryBill");
            }

            for (int i = 0; i < excelTable.Rows.Count; i++)
            {
                for (int j = 0; j < excelTable.Columns.Count; j++)
                {
                    ICell cell = datarow.CreateCell(j);
                    cell.SetCellValue(excelTable.Rows[i][j].ToString());
                }
                string excelname = excelTable.Rows[i]["姓名"].ToString() + "_" + DateTime.Now.ToString("yyyy-MM") + ".xls";
                stream = new FileStream(@"D:/SalaryBill/" + excelname, FileMode.Create);
                workbook.Write(stream);

                index++;
            }
            stream.Close();

            this.toolStripStatusLabel1.Text = "共拆分工资条:" + index + "条账单";
            this.Cursor = Cursors.Default;
        }

       

    }
}

View Code

 要引用 NPOI.DLL