C# 读Excel
之前一直使用C++开发,近期工作原因要使用C#语言,学习了一个月,感觉C#入门还是比C++容易些,而且网上
实例也相比C++多很多,但是在开发读写Excel功能时还是遇到很多坑。。。。
在此总结一下,以便大家跳过此坑!
本Demo在NPOI基础上开发,其特点是读写速度快!!!不受255列限制!!!
我们在开发的过程中可能会碰到这样的需求:读取.xlsx文件中的数据,经过一系列计算,再导出至.xls或者.xlsx中,或者在已经存在的文件中的某个位置追加数据,本文主要讲解此读,写,追加数据三个部分!
先贴代码:
/// <summary>
/// 打印调试信息
/// </summary>
/// <param name="str"> 打印的内容</param>
/// <param name="debug"> 是否是debug模式</param>
/// <returns></returns>
private void Trace(string str, bool debug = true)
{
if (debug)
System.Diagnostics.Debug.WriteLine(str);
else
System.Diagnostics.Trace.WriteLine(str);
}
/// <summary>
/// 将excel导入到Datatable
/// </summary>
/// <param name="file_path"> excel完整路径</param>
/// <param name="start_row"> 从第几行开始读</param>
/// <param name="sheet_num"> 第几个Sheet,0表示第一个</param>
/// <returns>Datatable</returns>
public DataTable Excel_To_DataTable(string file_path, int start_row = 0, int sheet_num = 0)
{
DataTable data_table = new DataTable();
FileStream fs = null;
IWorkbook work_book = null;
IRow row = null;
ICell cell = null;
try
{
using (fs = File.OpenRead(file_path))
{
string[] temp = file_path.Split('.');
if (temp[temp.Count() - 1].Equals("xlsx")) // 2007版本
work_book = new XSSFWorkbook(fs);
else if (temp[temp.Count() - 1].Equals("xls")) // 2003版本
work_book = new HSSFWorkbook(fs);
else
return null;
ISheet sheet = work_book.GetSheetAt(sheet_num); //读取sheet_num+1个Sheet
if (sheet != null)
{
int rowCount = sheet.LastRowNum; //获取总行数
if (0 == rowCount)
{
return null;
}
else
{
IRow first_row = sheet.GetRow(0); //获取第一行信息
int cell_count = first_row.LastCellNum; //通过第一行信息获取总列数
DataColumn data_col = null; //填充列
for (int i = first_row.FirstCellNum; i < cell_count; ++i)
{
data_col = new DataColumn("data_col" + (i + 1));
data_table.Columns.Add(data_col);
}
DataRow data_row = null; //填充行
for (int i = start_row; i <= rowCount; ++i)
{
row = sheet.GetRow(i);
if (row == null) continue;
data_row = data_table.NewRow();
for (int j = row.FirstCellNum; j < cell_count; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
data_row[j] = "";
}
else
{//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
case CellType.Blank:
data_row[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
if (format == 14 || format == 31 || format == 57 || format == 58)//对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理
data_row[j] = cell.DateCellValue;
else
data_row[j] = cell.NumericCellValue;
break;
case CellType.String:
data_row[j] = cell.StringCellValue;
break;
default:
break;
}
}
}
data_table.Rows.Add(data_row);
}
}
}
}
return data_table;
}
catch (Exception e)
{
if (fs != null)
fs.Close();
Trace(e.Message);
return null;
}
}
/// <summary>
/// 将DataTable转为二维数组
/// </summary>
/// <param name="dt"></param>
/// <returns>二维数组</returns>
public string[,] DataTable_To_Array(DataTable dt)
{
int i = 0;
int row_counts = dt.Rows.Count;
int cols_counts = dt.Columns.Count;
string[,] r = new string[row_counts, cols_counts];
foreach (System.Data.DataRow row in dt.Rows)
{
int j = 0;
foreach (System.Data.DataColumn column in dt.Columns)
{
r[i, j] = row[column.ColumnName].ToString();
j = j + 1;
}
i = i + 1;
}
return r;
}
private void button_读Excel_Click(object sender, EventArgs e)
{
DataTable dt = Excel_To_DataTable("D:\\test.xlsx");
string[,] content = DataTable_To_Array(dt);
for (int i = 1; i < content.GetLength(1) + 1; i++)
{
listView_show.Columns.Add(content[0,i-1], 50, System.Windows.Forms.HorizontalAlignment.Center);
}
for (int i = 1; i < content.GetLength(0); i++)
{
ListViewItem lvi = new ListViewItem();
lvi.Text = (i).ToString();
for (int j = 1; j < content.GetLength(1); j++)
{
lvi.SubItems.Add(content[i, j]);
}
listView_show.Items.Add(lvi);
}
}
private void Form1_Load(object sender, EventArgs e)
{
listView_show.View = System.Windows.Forms.View.Details; //设置Style
listView_show.FullRowSelect = true;
listView_show.GridLines = true; //设置网格线
}
读Excel是操作Excel中最简单的一步,因为读操作只是将表中的数据取出,并不需要考虑写操作中的各种格式,颜色等问题。