一直想做一款工具软件，筹备了很久，想把所学的一些知识整理出来。
我做成了一个工具软件，想要的留言告诉我。或者关注我获取。
using HcyCommonCore;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace HaoHaiOCore.FrmForm
{
public partial class Frm_Excel_Split : Form
{
public Frm_Excel_Split()
{
InitializeComponent();
}
private void dungeonTextBox1_TextChanged(object sender, EventArgs e)
{
}
/// <summary>
/// 拆分
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_split_Click(object sender, EventArgs e)
{
if (!File.Exists(this.label_excelPath.Text)) {
MessageBox.Show("请选择有效的Excel文件！");
return;
}
DataTable dataTable_excel=NPOIHelper.ExcelToDataTable(this.label_excelPath.Text, "",true);
Console.WriteLine(dataTable_excel);
}
/// <summary>
/// 选择Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void parrotButton1_Click(object sender, EventArgs e)
{
OpenFileDialog op = new OpenFileDialog();
if (op.ShowDialog() == DialogResult.OK)
{
this.label_excelPath.Text = op.FileName;
}
if (!File.Exists(this.label_excelPath.Text))
{
MessageBox.Show("请选择有效的Excel文件！");
return;
}
else {
}
}
}
}
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace HcyCommonCore
{
public class NPOIHelper
{
#region NPOI保存数据到excel
/// <summary>
/// 导出数据到excel中
/// </summary>
/// <param name="dataSet"></param>
/// <param name="filename"></param>
/// <returns></returns>
public static bool TablesToExcel(DataSet dataSet, string filename)
{
MemoryStream ms = new MemoryStream();
using (dataSet)
{
IWorkbook workBook;
//IWorkbook workBook=WorkbookFactory.Create(filename);
string suffix = filename.Substring(filename.LastIndexOf(".") + 1, filename.Length - filename.LastIndexOf(".") - 1);
if (suffix == "xls")
{
workBook = new HSSFWorkbook();
}
else
workBook = new XSSFWorkbook();
for (int i = 0; i < dataSet.Tables.Count; i++)
{
ISheet sheet = workBook.CreateSheet(dataSet.Tables[i].TableName);
CreatSheet(sheet, dataSet.Tables[i]);
}
workBook.Write(ms);
try
{
SaveToFile(ms, filename);
ms.Flush();
return true;
}
catch
{
ms.Flush();
throw;
}
}
}
private static void CreatSheet(ISheet sheet, DataTable table)
{
IRow headerRow = sheet.CreateRow(0);
//表头
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
private static void SaveToFile(MemoryStream ms, string fileName)
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray(); //转为字节数组
fs.Write(data, 0, data.Length); //保存为Excel文件
fs.Flush();
data = null;
}
}
#endregion
#region NPOI将excel中的数据导入到DataTable
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn = true)
{
DataTable data = new DataTable();
try
{
IWorkbook workbook = null; //新建IWorkbook对象
var fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
string suffix = fileName.Substring(fileName.LastIndexOf(".") + 1, fileName.Length - fileName.LastIndexOf(".") - 1);
if (suffix == "xls") //if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook(fileStream); //xlsx数据读入workbook
}
else
{
workbook = new XSSFWorkbook(fileStream); //xls数据读入workbook
}
//var sheet = (sheetName != null) ? workbook.GetSheet(sheetName) : workbook.GetSheetAt(0);//获取sheet
var sheet = workbook.GetSheetAt(0);
if (sheetName != null && sheetName != "")
{
sheet = workbook.GetSheet(sheetName);
}
if (sheet != null)
SheetToDataTable(sheet, isFirstRowColumn, ref data);
else
data = null;
return data;
}
catch (Exception ex)
{
//MessageBox.Show("ExcelToDataTable Exception: " + ex.Message);
return null;
}
}
/// <summary>
/// 将Excel中的工作薄转换为DataTable
/// </summary>
/// <param name="sheet">Excel中的工作薄</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <param name="data">表</param>
private static void SheetToDataTable(ISheet sheet, bool isFirstRowColumn, ref DataTable data)
{
int rowCount = sheet.LastRowNum; //最后一列的标号
IRow firstRow = sheet.GetRow(sheet.FirstRowNum);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
int startRow = 0;
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
if (firstRow.GetCell(i) == null) continue;
//DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
DataColumn column = new DataColumn(firstRow.GetCell(i).ToString());
data.Columns.Add(column);
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行为null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //没有数据的单元格也为null
dataRow[j - row.FirstCellNum] = row.GetCell(j).ToString();//一般情况下row.FirstCellNum为0，但有时excel中的数据并不在A列，所以需减去，否则将导致溢出，出现异常。
}
data.Rows.Add(dataRow);
}
}
#endregion
}
}
