一、DataTable与Excel的文件读写
1、Excel导出到DataTable
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WindowsFormsApplication1.UserClass
{
class FileHelper
{
/// <summary>
/// 读取excel 默认第一行为标头
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <returns></returns>
public static DataTable ImportExceltoDt(string strFileName)
{
DataTable dt = new DataTable();
IWorkbook wb;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
wb = WorkbookFactory.Create(file);
}
ISheet sheet = wb.GetSheetAt(0);
dt = ImportDt(sheet, 0, true);
return dt;
}
/// <summary>
/// 将制定sheet中的数据导出到datatable中
/// </summary>
/// <param name="sheet">需要导出的sheet</param>
/// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
/// <returns></returns>
///
static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader)
{
DataTable table = new DataTable();
IRow headerRow;
int cellCount;
try
{
if (HeaderRowIndex < 0 || !needHeader)
{
headerRow = sheet.GetRow(0);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else
{
headerRow = sheet.GetRow(HeaderRowIndex);
cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
{
if (headerRow.GetCell(i) == null)
{
if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(Convert.ToString(i));
table.Columns.Add(column);
}
}
else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
{
DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
table.Columns.Add(column);
}
else
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
}
}
int rowCount = sheet.LastRowNum;
for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
{
try
{
IRow row;
if (sheet.GetRow(i) == null)
{
row = sheet.CreateRow(i);
}
else
{
row = sheet.GetRow(i);
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j <= cellCount; j++)
{
try
{
if (row.GetCell(j) != null)
{
switch (row.GetCell(j).CellType)
{
case CellType.String:
string str = row.GetCell(j).StringCellValue;
if (str != null && str.Length > 0)
{
dataRow[j] = str.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
{
dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
}
else
{
dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
}
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
case CellType.Formula:
switch (row.GetCell(j).CachedFormulaResultType)
{
case CellType.String:
string strFORMULA = row.GetCell(j).StringCellValue;
if (strFORMULA != null && strFORMULA.Length > 0)
{
dataRow[j] = strFORMULA.ToString();
}
else
{
dataRow[j] = null;
}
break;
case CellType.Numeric:
dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
break;
case CellType.Boolean:
dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
break;
case CellType.Error:
dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
break;
default:
dataRow[j] = "";
break;
}
break;
default:
dataRow[j] = "";
break;
}
}
}
catch (Exception exception)
{
}
}
table.Rows.Add(dataRow);
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
}
}
catch (Exception exception)
{
//wl.WriteLogs(exception.ToString());
}
return table;
}
}
}
2、将DataSet数据的保存为Excel
/// <summary>
/// 将DataSet数据保存为EXCEL文件
/// </summary>
/// <param name="Path">将要保存的文件路径</param>
/// <param name="ds">将要保存的DataSet数据表可多页面</param>
public void DataSetToExcel(string Path,System.Data.DataSet ds)
{
string strCon = string.Empty;
FileInfo file = new FileInfo(Path);
string extension = file.Extension;
switch (extension)
{
case ".xls":
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=Excel 8.0;";
break;
case ".xlsx":
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=0;'";
break;
default:
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0;'";
break;
}
try
{
using (System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strCon))
{
con.Open();
StringBuilder strSQL = new StringBuilder();
System.Data.OleDb.OleDbCommand cmd;
try
{
for (int i=0; i < ds.Tables.Count; i++)
{
cmd = new System.Data.OleDb.OleDbCommand(string.Format("drop table {0}", ds.Tables[i].TableName), con); //覆盖文件时可能会出现Table 'Sheet1' already exists.所以这里先删除了一下
cmd.ExecuteNonQuery();
}
}
catch { }
//创建表格字段
for(int i = 0; i < ds.Tables.Count; i++)
{
strSQL.Clear();
strSQL.Append("CREATE TABLE ").Append("[" + ds.Tables[i].TableName + "]");
strSQL.Append("(");
for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
{
strSQL.Append("[" + ds.Tables[i].Columns[j].ColumnName + "] text,");
}
strSQL = strSQL.Remove(strSQL.Length - 1, 1);
strSQL.Append(")");
cmd = new System.Data.OleDb.OleDbCommand(strSQL.ToString(), con);
cmd.ExecuteNonQuery();
//添加数据
for (int k = 0; k < ds.Tables[i].Rows.Count; k++)
{
strSQL.Clear();
StringBuilder strvalue = new StringBuilder();
for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
{
strvalue.Append("'" + ds.Tables[i].Rows[i][j].ToString() + "'");
if (j != ds.Tables[i].Columns.Count - 1)
{
strvalue.Append(",");
}
else
{
}
}
cmd.CommandText = strSQL.Append(" insert into [" + ds.Tables[i].TableName + "] values (").Append(strvalue).Append(")").ToString();
cmd.ExecuteNonQuery();
}
}
con.Close();
}
}
catch { }
}
/// <summary>
/// 将EXCLE文件读取到DataSet表中。支持多工作表读取
/// </summary>
/// <param name="pathName">要读取的文件路径</param>
/// <returns>返回DataSet表</returns>
public System.Data.DataSet ExcelToDataSet(string pathName,string sheetName="")
{
System.Data.DataSet ds = new System.Data.DataSet();
string ConnectionString = string.Empty;
FileInfo file = new FileInfo(pathName);
if (!file.Exists)
{
throw new Exception("文件不存在");
}
string extension = file.Extension;
switch (extension) // 连接字符串
{
case ".xls":
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
break;
case ".xlsx":
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=no;IMEX=1;'";
break;
default:
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";
break;
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(ConnectionString);
try
{
con.Open();
if (sheetName != "") //若指定了工作表名
{
//读Excel的过程中，发现dt末尾有些行是空的，所以在sql语句中加了Where 条件筛选符合要求的数据。OLEDB会自动生成列名F1,F2……Fn
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + sheetName + "$] where F1 is not null ", con);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
try
{
apt.Fill(ds);
}
catch (Exception ex)
{
throw new Exception("该Excel文件中未找到指定工作表名," + ex.Message);//抛出异常接收
}
ds.Tables.Clear();
ds.Tables.Add(sheetName);
}
else
{ //默认读取第一个有数据的工作表
var tables = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[]
{ });
if (tables.Rows.Count == 0)
{
throw new Exception("Excel必须包含一个表");
}
ds.Tables.Clear();
foreach (System.Data.DataRow row in tables.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString(); //过滤无效SheetName
if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))//分析文件名Contains 是否有$有为真，Replace字符替换，EndsWith看字串尾是否有指定字符
{
System.Data.DataTable tableColumns = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,
new object[] { null, null, strSheetTableName, null });
if (tableColumns.Rows.Count < 2) //工作表列数
continue;
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from [" + strSheetTableName + "] where F1 is not null", con);
System.Data.OleDb.OleDbDataAdapter apt = new System.Data.OleDb.OleDbDataAdapter(cmd);
System.Data.DataTable dt = new System.Data.DataTable();
apt.Fill(dt);
dt.TableName = strSheetTableName.Replace("'", "").Replace("$", "");
ds.Tables.Add(dt);
}
}
}
for(int i = 0; i < ds.Tables.Count; i++)
{
if (ds.Tables[i].Rows.Count < 0)
throw new Exception("表必须包含数据"); //重构字段名
System.Data.DataRow headRow = ds.Tables[i].Rows[0];
foreach (System.Data.DataColumn c in ds.Tables[i].Columns)
{
string headValue = (headRow[c.ColumnName] == DBNull.Value || headRow[c.ColumnName] == null) ? "" : headRow[c.ColumnName].ToString().Trim();
if (headValue.Length == 0)
{
throw new Exception("必须输入列标题");
}
if (ds.Tables[i].Columns.Contains(headValue))
{
throw new Exception("不能用重复的列标题：" + headValue);
}
c.ColumnName = headValue;
}
ds.Tables[i].Rows.RemoveAt(0);
}
return ds;
}
catch (Exception ee)
{
throw ee;
}
finally
{
con.Close();
}
}
https://blog.csdn.net/jkhmf/article/details/82946965
二、保存与读取Excel
https://my.oschina.net/u/4399215/blog/3435686
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
/// <summary>
/// Excel导入成Datable
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file)
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// Datable导出成Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="file">导出路径(包括文件名与扩展名)</param>
public static void TableToExcel(DataTable dt, string file)
{
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
if (workbook == null) { return; }
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
三、在同一个Excel文件中创建多个sheet
private void buttonTest_Click(object sender, EventArgs e)
{
HSSFWorkbook workBook = new HSSFWorkbook();
//ISheet sheetA = workBook.CreateSheet("sheetA");
//ISheet sheetB = workBook.CreateSheet("sheetB");
createSheet(workBook,"SheetA");
createSheet(workBook,"SheetB");
createSheet(workBook,"SheetC");
string path = Application.StartupPath + @"\test.xls";
if (File.Exists(path))
{
File.Delete(path);
}
using (FileStream file = new FileStream(path, FileMode.Create))
{
workBook.Write(file); //创建Excel文件。
file.Close();
}
MessageBox.Show("OK");
}
private ISheet createSheet(HSSFWorkbook workBook, string sheetName)
{
ISheet sheet = workBook.CreateSheet(sheetName);
IRow RowHead = sheet.CreateRow(0);
for (int iColumnIndex = 0; iColumnIndex < 10; iColumnIndex++)
{
RowHead.CreateCell(iColumnIndex).SetCellValue(Guid.NewGuid().ToString());
}
for (int iRowIndex = 0; iRowIndex < 20; iRowIndex++)
{
IRow RowBody = sheet.CreateRow(iRowIndex + 1);
for (int iColumnIndex = 0; iColumnIndex < 10; iColumnIndex++)
{
RowBody.CreateCell(iColumnIndex).SetCellValue(DateTime.Now.Millisecond);
sheet.AutoSizeColumn(iColumnIndex);
}
}
return sheet;
}
https://www.cnblogs.com/dyllove98/archive/2013/08/06/3241515.html
四、设置EXCEL单元格数字格式
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;
using System.IO;
using NPOI.SS.Util;
using System.Drawing;
using NPOI.HSSF.Util;
namespace NPOI
{
class Program7
{
static void Main(string[] args)
{
//说明：设置数字格式
//1.创建EXCEL中的Workbook
IWorkbook myworkbook = new XSSFWorkbook();
//2.创建Workbook中的Sheet
ISheet mysheet = myworkbook.CreateSheet("sheet1");
mysheet.SetColumnWidth(0, 20 * 256);
mysheet.SetColumnWidth(1, 20 * 256);
//3.创建Row中的Cell并赋值
IRow row0 = mysheet.CreateRow(0); row0.CreateCell(0).SetCellValue(2013.143); row0.CreateCell(1).SetCellValue("转化为汉字大写");
IRow row1 = mysheet.CreateRow(1); row1.CreateCell(0).SetCellValue(123152013.143); row1.CreateCell(1).SetCellValue("改变小数精度");
IRow row2 = mysheet.CreateRow(2); row2.CreateCell(0).SetCellValue(123152013.143); row2.CreateCell(1).SetCellValue("分段添加，号");
IRow row3 = mysheet.CreateRow(3); row3.CreateCell(0).SetCellValue(123152013.143); row3.CreateCell(1).SetCellValue("科学计数法");
IRow row4 = mysheet.CreateRow(4); row4.CreateCell(0).SetCellValue(-123152013.143); row4.CreateCell(1).SetCellValue("正数与负数的区分(负数红色)");
IRow row5 = mysheet.CreateRow(5); row5.CreateCell(0).SetCellValue(123152013.77); row5.CreateCell(1).SetCellValue("整数部分+分数");
IRow row6 = mysheet.CreateRow(6); row6.CreateCell(0).SetCellValue(123152013.77); row6.CreateCell(1).SetCellValue("分数");
IRow row7 = mysheet.CreateRow(7); row7.CreateCell(0).SetCellValue(0.333); row7.CreateCell(1).SetCellValue("百分数");
//4.创建CellStyle与DataFormat并加载格式样式
IDataFormat dataformat = myworkbook.CreateDataFormat();
ICellStyle style0 = myworkbook.CreateCellStyle();
style0.DataFormat = dataformat.GetFormat("[DbNum2][$-804]General");//转化为汉字大写
ICellStyle style1 = myworkbook.CreateCellStyle();
style1.DataFormat = dataformat.GetFormat("0.0"); //改变小数精度【小数点后有几个0表示精确到小数点后几位】
ICellStyle style2 = myworkbook.CreateCellStyle();
style2.DataFormat = dataformat.GetFormat("#,##0.0");//分段添加，号
ICellStyle style3 = myworkbook.CreateCellStyle();
style3.DataFormat = dataformat.GetFormat("0.00E+00");//科学计数法
ICellStyle style4 = myworkbook.CreateCellStyle();
style4.DataFormat = dataformat.GetFormat("0.00;[Red]-0.00");//正数与负数的区分
ICellStyle style5 = myworkbook.CreateCellStyle();
style5.DataFormat = dataformat.GetFormat("# ??/??");//整数部分+分数
ICellStyle style6 = myworkbook.CreateCellStyle();
style6.DataFormat = dataformat.GetFormat("??/??");//分数
ICellStyle style7 = myworkbook.CreateCellStyle();
style7.DataFormat = dataformat.GetFormat("0.00%");//百分数【小数点后有几个0表示精确到显示小数点后几位】
//5.将CellStyle应用于具体单元格
row0.GetCell(0).CellStyle = style0;
row1.GetCell(0).CellStyle = style1;
row2.GetCell(0).CellStyle = style2;
row3.GetCell(0).CellStyle = style3;
row4.GetCell(0).CellStyle = style4;
row5.GetCell(0).CellStyle = style5;
row6.GetCell(0).CellStyle = style6;
row7.GetCell(0).CellStyle = style7;
//6.保存
FileStream file = new FileStream(@"E:\myworkbook7.xlsx", FileMode.Create);
myworkbook.Write(file);
file.Close();
}
}
}
https://blog.csdn.net/xxs77ch/article/details/50237017
五、设置EXCEL单元格背景填充色
https://bbs.csdn.net/topics/390203526
标签：string,C#,Excel,System,using,new,Data,ds,NPIO From： https://www.cnblogs.com/Mars-0603/p/16729834.html