组件读写Excel 二、NPOI读写Excel 三、读写入txt"/>
C# 一、Com组件读写Excel 二、NPOI读写Excel 三、读写入txt
一、Com组件方式
这种方式是通过Com组件 Microsoft.Office.Interop.Excel.dll实现Excel文件的操作。
优点：读取Excel数据非常灵活，可以实现Excel具有的各种数据处理功能；
缺点：对数据的访问时基于单元格方式实现的，所以读写数据较慢，特别是当数据量较大时，访问效率问题更为突出。另一点是要求本机安装了Microsoft Office组件。
二、使用NPOI
这种方式是通过NPOI库实现Excel文件操作，可以在没有安装微软Office的情况下使用。
优点：读取Excel数据速度较快，操作方式灵活；
缺点：试了再说！
三、读写txt
一、1.1、创建并写入
添加引用：
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
/// <summary>
/// 如果文件不存在，则新建
/// </summary>
/// <param name="FileName"></param>
public static void CreateExcelFile(string FileName)
{
//create
object Nothing = System.Reflection.Missing.Value;
var app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = false;
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(Nothing);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[1];
worksheet.Name = "第一节";//命名
worksheet.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
workBook.Close(false, Type.Missing, Type.Missing);
app.Quit();
}
/// <summary>
/// 往Excel中存储写数据
/// </summary>
/// <param name="newpath">保存的文件路劲（全名称）</param>
/// <param name="startRow">开始写入的行</param>
/// <param name="dataList">需要写入的数据列表</param>
public static void WriteXls(string newpath, int startRow, List<List<string>> dataList)
{
CreateExcelFile(newpath);
//获得要写入的Excel
Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
m_objBooks.Open(newpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Workbook m_objBook = m_objBooks.get_Item(1);
Microsoft.Office.Interop.Excel.Sheets m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet m_objSheet = (Microsoft.Office.Interop.Excel.Worksheet)(m_objSheets.get_Item(1));
//开始写入东西
string[] columnID = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z" };
for (int rowCount = 0; rowCount < dataList.Count; rowCount++)
{
int columnNum = 0;
if (dataList[rowCount].Count >= columnID.Length)
columnNum = columnID.Length;
else
columnNum = dataList[rowCount].Count;
for (int columnCount = 0; columnCount < columnNum; columnCount++)
{
int rowID = rowCount + 1 + startRow;
string columnName = columnID[columnCount] + rowID;
m_objSheet.get_Range(columnName, Type.Missing).Value2 = dataList[rowCount][columnCount];
}
}
m_objBook.Save();
m_objBook.Close(Type.Missing, Type.Missing, Type.Missing);
m_objBooks.Close();
m_objExcel.Quit();
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
}
#region 写dt数据到excel 其中有调整列宽度
/// <summary>
/// 导出dt数据到指定excel模板中
/// </summary>
/// <param name="strMBPath">模板路径</param>
/// <param name="newPath">新文件位置</param>
/// <param name="dt">要写入的table数据</param>
/// <param name="startLoca">写入开始位置</param>
/// <returns></returns>
public static bool WriteDTab2Excel(string strMBPath,string newPath,DataTable dt,int startLoca)
{
bool writeFlag = false;
if (dt.Rows.Count > 0)
{
File.Copy(strMBPath, newPath, true);//复制模板文件到相应路径
//获得要写入的Excel
Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;
m_objBooks.Open(newPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Workbook m_objBook = m_objBooks.get_Item(1);
Microsoft.Office.Interop.Excel.Sheets m_objSheets = (Microsoft.Office.Interop.Excel.Sheets)m_objBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet m_objSheet = (Microsoft.Office.Interop.Excel.Worksheet)(m_objSheets.get_Item(1));
// m_objSheet.get_Range("F9", Type.Missing).Value2 = splittfh[0];//图号 写入示例
#region 写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
try
{
string tempstr = dr[j].ToString();
m_objSheet.Cells[startLoca,j+1] = tempstr;
if (tempstr.Length > 3)
{
int coli = m_objSheet.Cells[startLoca, j + 1].Column;
if (tempstr.Length > 6)
{
((Microsoft.Office.Interop.Excel.Range)m_objSheet.Columns[coli, System.Type.Missing]).ColumnWidth = 10;
} else {
((Microsoft.Office.Interop.Excel.Range)m_objSheet.Columns[coli, System.Type.Missing]).ColumnWidth = 7;
};
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
startLoca += 1;
}
Microsoft.Office.Interop.Excel.Range cel=m_objSheet.Cells[dt.Rows.Count+4, dt.Columns.Count];
Microsoft.Office.Interop.Excel.Range targetRange = m_objSheet.get_Range("A1", cel);
targetRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
#endregion
m_objBook.Save();
m_objBook.Close(Type.Missing, Type.Missing, Type.Missing);
m_objBooks.Close();
m_objExcel.Quit();
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
}
return writeFlag;
}
#endregion
1.2、读取Excel
使用过程中发现有些单元格读取为“”。通过修改连接字符串的IMEX。OK
"Provider=Microsoft.ACE.OLEDB.12.0;data source=" + strFileName + ";Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1;'";
参考：
/// <summary>
/// 依据路径获取excel中的数据
/// </summary>
/// <param name="strFileName">指定的路径+文件名.xls</param>
public static System.Data.DataTable getDataFromExcel(string strFileName, bool hasTitle = false)
{
using (DataSet ds = new DataSet())
{
string fileType = System.IO.Path.GetExtension(strFileName);
if (string.IsNullOrEmpty(fileType)) return null;
string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + strFileName + ";Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1;'";
string SheetName = "";
using (OleDbConnection conn = new OleDbConnection(strCon))
{
try
{
conn.Open();
System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
SheetName = dt.Rows[0][2].ToString().Trim();
}
catch (Exception e)
{
e.ToString();
}
}
string strCom = " SELECT * FROM [" + SheetName + "]";
using (OleDbConnection myConn = new OleDbConnection(strCon))
using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn))
{
try
{
myConn.Open();
myCommand.Fill(ds);
}
catch (Exception e)
{
e.ToString();
}
}
if (ds == null || ds.Tables.Count <= 0) return null;
return ds.Tables[0];
}
}
==================================================================
二、NPOI
2.1创建excel
/// <summary>
/// 创建excel2007
/// </summary>
/// <param name="FileName">excel路径</param>
public void CreateExcelFile(string FileName)
{
XSSFWorkbook workbook2007 = new XSSFWorkbook(); //新建xlsx工作簿
workbook2007.CreateSheet("Sheet1");
FileStream file2007 = new FileStream(FileName, FileMode.Create);
//FileStream file2007 = new FileStream(@"C:\Users\liuyi\Desktop\NPOI\0测试数据\Excel2007.xlsx", FileMode.Create);
workbook2007.Write(file2007);
file2007.Close();
workbook2007.Close();
}
2.2读
/// <summary>
/// 读取excel到DataTable
/// </summary>
/// <param name="strFileName">excel全路径</param>
/// <param name="hasTitle">第一行是否包含标题</param>
/// <returns></returns>
public DataTable GetDataTableFromExcel(string strFileName, bool hasTitle)
{
DataTable dataTable = new DataTable();
//excel工作表
ISheet sheet = null;
int startRow = 0;//数据开始行(排除标题行)
try
{
if (!File.Exists(strFileName))
{
return null;
}
FileStream fileStream = new FileStream(strFileName, FileMode.Open, FileAccess.Read);//根据指定路径读取文件
IWorkbook workbook = WorkbookFactory.Create(fileStream);//根据文件流创建excel数据结构
if (!string.IsNullOrEmpty("Sheet1"))//如果有指定工作表名称
{
sheet = workbook.GetSheet("Sheet1");
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
int rowCount = sheet.LastRowNum;
IRow firstRow = sheet.GetRow(rowCount);//最后一行的列数，作为总列数。第一行行首可能列数不同
int cellCount = 0;
for (int colNum = 0; colNum < rowCount; colNum++)
{
if (cellCount < sheet.GetRow(colNum).LastCellNum)
cellCount = sheet.GetRow(colNum).LastCellNum;
}
if (hasTitle)
{
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//根据列宽创建列：cellCount
for (int coli = 0; coli < cellCount; coli++) {
dataTable.Columns.Add(coli.ToString(), System.Type.GetType("System.String"));
}
for (int i = startRow; i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dataTable.Rows.Add(dataRow);
}
}
//for (int ii = 0; ii < dataTable.Rows.Count; ii++)
//{
// for (int jj = 0; jj < dataTable.Columns.Count; jj++)
// {
// string ss = dataTable.Rows[ii][jj].ToString();
// }
//}
return dataTable;
}
catch (Exception ex)
{
throw ex;
}
}
2.3写
/// <summary>
/// 写入excel
/// </summary>
/// <param name="newpath">excel路径</param>
/// <param name="startRow">从哪行开始写入</param>
/// <param name="dataList">写入的数据</param>
public void WriteXls(string newpath, int startRow, List<List<string>> dataList)
{
FileStream file2007 = new FileStream(newpath, FileMode.OpenOrCreate);
XSSFWorkbook workbook2007 = new XSSFWorkbook();
workbook2007.CreateSheet("Sheet1");
XSSFSheet SheetOne = (XSSFSheet)workbook2007.GetSheet("Sheet1"); //获取第一个工作表
//dataList 写入数据
for (int i = 0; i < dataList.Count; i++)
{
IRow row = SheetOne.CreateRow(startRow+i);
for (int j = 0; j < dataList[0].Count; j++)
{
string ss = dataList[i][j].ToString();
row.CreateCell(j).SetCellValue(dataList[i][j].ToString());
//mysheet.SetColumnWidth(3, 30 * 256);
}
}
//设置列宽
for (int columnNum = 0; columnNum <1; columnNum++)
{
int columnWidth = SheetOne.GetColumnWidth(columnNum) / 256;//获取当前列宽度
for (int rowNum = 0; rowNum <= SheetOne.LastRowNum; rowNum++)//在这一列上循环行
{
IRow currentRow = SheetOne.GetRow(rowNum);
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}//若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
}
SheetOne.SetColumnWidth(columnNum, columnWidth * 256);
}
workbook2007.Write(file2007);
file2007.Close();
workbook2007.Close();
}
参考：
设置列宽：.html
NPOI文章：
下载NPOI： 使用其中\Net40下的dll文件。
==================================================================
三、读写txt
3.1写入txt
public void txtWrite(string txtFulPath, string WriteContent) {
if (!File.Exists(txtFulPath))
{
FileStream fs = new FileStream(txtFulPath, FileMode.OpenOrCreate);
fs.Close();
}
using (StreamWriter sw = new StreamWriter(txtFulPath, true))
{
sw.Write(WriteContent);
sw.Close();
}
}
3.2读取txt
public string txtRead(string txtFulPath) {
StreamReader sr = new StreamReader();
string result=sr.ReadToEnd();
return result;
}
public string txtRead(string txtFulPath) {
StreamReader sr = new StreamReader(txtFulPath);
while (!sr.EndOfStream)
{
string aline = sr.ReadLine();
}
}
更多推荐
C# 一、Com组件读写Excel 二、NPOI读写Excel 三、读写入txt
发布评论