也来耍下拆分工资条
首先上界面：
再看拆分功能演示：
上代码：
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; } } }
要引用 NPOI.DLL
