一，CSV转Datable
public DataTable CSVToDatatable(string strFileName)
{
DataTable dt = new DataTable();
FileStream fs = new FileStream(strFileName, System.IO.FileMode.Open, System.IO.FileAccess.Read);
StreamReader sr = new StreamReader(fs, System.Text.Encoding.Default);
//记录每次读取的一行记录
string strLine = "";
//记录每行记录中的各字段内容
string[] aryLine;
//标示列数
int columnCount = 0;
//标示是否是读取的第一行
bool IsFirst = true;
//逐行读取CSV中的数据
while ((strLine = sr.ReadLine()) != null)
{
aryLine = strLine.Split(',');
if (IsFirst == true)
{
IsFirst = false;
columnCount = aryLine.Length;
//创建列
for (int i = 0; i < columnCount; i++)
{
DataColumn dc = new DataColumn(aryLine[i]);
dt.Columns.Add(dc);
}
}
else
{
DataRow dr = dt.NewRow();
for (int j = 0; j < columnCount; j++)
{
dr[j] = aryLine[j];
}
dt.Rows.Add(dr);
}
}
sr.Close();
fs.Close();
return dt;
}
二，Datatble转换成csv文件
public void DatatableToCSV(DataTable dt, string strFileName)
{
int pos = strFileName.LastIndexOf("\\");
if (File.Exists(strFileName))
File.Delete(strFileName);
if (!Directory.Exists(strFileName.Substring(0,pos)))
Directory.CreateDirectory(strFileName.Substring(0, pos));
FileStream fs = new FileStream(strFileName, FileMode.Create, System.IO.FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);
string data = "";
//写出列名称
if (strFileName.Substring(pos + 1).Equals("batch_party_sample.csv"))
data = "部门名称,部门ID,父部门ID,排序";
else if (strFileName.Substring(pos + 1, strFileName.Length - pos - 1).Equals("batch_user_sample.csv"))
data = "姓名,帐号,微信号,手机号,邮箱,所在部门,职位";
else
{
for (int i = 0; i < dt.Columns.Count; i++)
{
data += dt.Columns[i].ColumnName.ToString();
if (i < dt.Columns.Count - 1)
{
data += ",";
}
}
}
sw.WriteLine(data);
//写出各行数据
if (strFileName.Substring(pos + 1).Equals("batch_party_sample.csv"))
{
data = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
data = dt.Rows[i]["name"].ToString().Trim() + "," + dt.Rows[i]["id"].ToString().Trim() + "," + dt.Rows[i]["parentid"].ToString().Trim() + "," + dt.Rows[i]["order"].ToString().Trim();
sw.WriteLine(data);
}
}
else if (strFileName.Substring(pos + 1).Equals("batch_user_sample.csv"))
{
data = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
data = dt.Rows[i]["name"].ToString().Trim() + "," + dt.Rows[i]["userid"].ToString().Trim() + "," + dt.Rows[i]["weixinid"].ToString().Trim() + "," + dt.Rows[i]["mobile"].ToString().Trim()
+ "," + dt.Rows[i]["email"].ToString().Trim() + "," + dt.Rows[i]["department"].ToString().Trim() + "," + dt.Rows[i]["position"].ToString().Trim();
sw.WriteLine(data);
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
data = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
data += dt.Rows[i][j].ToString();
if (j < dt.Columns.Count - 1)
{
data += ",";
}
}
sw.WriteLine(data);
}
}
sw.Close();
fs.Close();
}
三，excel转换成datatble
using Excel = Microsoft.Office.Interop.Excel;
引用：Microsoft.Office.Interop.Excel.dll , Microsoft.CSharp.dll
private DataTable OpenExcel(string strFileName)
{
Excel.Application Excel = new Excel.Application();
Excel.Sheets sheets = null; ;
Excel.Workbook workbook = null;
object oMissiong = System.Reflection.Missing.Value;
DataTable dt = new DataTable();
try
{
if (Excel == null)
return null;
workbook = Excel.Workbooks.Open(strFileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
sheets = workbook.Worksheets;
//将数据读入到DataTable中
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//读取第一张表
if (worksheet == null)
return null;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
//生成列头
for (int i = 0; i < iColCount; i++)
{
if(i==2 || (i>=8 & i<=19))
continue;
var name = "column" + i;
var txt = ((Excel.Range)worksheet.Cells[1, i + 1]).Text.ToString();
if (!string.IsNullOrEmpty(txt)) name = txt;
while (dt.Columns.Contains(name))
name = name + "_1";//重复行名称会报错。
dt.Columns.Add(new DataColumn(name, typeof(string)));
}
//生成行数据
Excel.Range range;
for (int iRow = 2; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
int k = 0;
for (int iCol = 1; iCol <= iColCount; iCol++)
{
if (iCol == 3 || (iCol >= 9 & iCol <= 20))
continue;
range = (Excel.Range)worksheet.Cells[iRow, iCol];
// dr[iCol - 1] = range.Text;
dr[k] = range.Text;
k++;
}
dt.Rows.Add(dr);
}
}
catch (Exception err)
{
MessageBox.Show(err.Message, "Error Information", MessageBoxButtons.OK);
return null;
}
finally
{
workbook.Close(false, oMissiong, oMissiong);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
Excel.Workbooks.Close();
Excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel);
Excel = null;
}
return dt;
}
四，datatable 转换成excel文件
public Boolean ExportExcel(DataTable dt, string strType)
{
Excel.Application appexcel = new Excel.Application();
SaveFileDialog saveFileDialog = new SaveFileDialog();
Missing miss = Missing.Value;
// appexcel = new Excel.Application();
Excel.Workbook workbookData;
Excel.Worksheet worksheetData;
Excel.Range rangeData;
//设置对象不可见
appexcel.Visible = false;
System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");
workbookData = appexcel.Workbooks.Add(miss);
worksheetData = (Excel.Worksheet)workbookData.Worksheets.Add(miss, miss, miss, miss);
try
{
//给工作表赋名称
worksheetData.Name = strType;
//worksheetData.Name = "contactList.xlsx";
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheetData.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
//因为第一行已经写了表头，所以所有数据都应该从a2开始
rangeData = worksheetData.get_Range("a2", miss);
Excel.Range Rang = null;
//irowcount为实际行数，最大行
int iRowCount = dt.Rows.Count;
int iparstedrow = 0, iCurrsize = 0;
//ieachsize为每次写行的数值，可以自己设置
int iEachsize = 4000;
//icolumnaccount为实际列数，最大列数
int iColumnCount = dt.Columns.Count;
//在内存中声明一个ieachsize×icolumnaccount的数组，ieachsize是每次最大存储的行数，icolumnaccount就是存储的实际列数
object[,] objval = new object[iEachsize, iColumnCount];
iCurrsize = iEachsize;
while (iparstedrow < iRowCount)
{
if ((iRowCount - iparstedrow) < iEachsize)
iCurrsize = iRowCount - iparstedrow;
//用for循环给数组赋值
for (int i = 0; i < iCurrsize; i++)
{
for (int j = 0; j < iColumnCount; j++)
{
objval[i, j] = dt.Rows[i + iparstedrow][j].ToString().Trim();
}
}
string X = "A" + ((int)(iparstedrow + 2)).ToString();
string col = "";
if (iColumnCount <= 26)
{
col = ((char)('A' + iColumnCount - 1)).ToString() + ((int)(iparstedrow + iCurrsize + 1)).ToString();
}
else
{
col = ((char)('A' + (iColumnCount / 26 - 1))).ToString() + ((char)('A' + (iColumnCount % 26 - 1))).ToString() + ((int)(iparstedrow + iCurrsize + 1)).ToString();
}
Rang = worksheetData.get_Range(X, col);
// 调用range的value2属性，把内存中的值赋给excel
Rang.Value2 = objval;
iparstedrow = iparstedrow + iCurrsize;
}
//保存工作表
System.Runtime.InteropServices.Marshal.ReleaseComObject(Rang);
Rang = null;
//调用方法关闭excel进程
if (!Directory.Exists(@"D:\WXContanct"))
{
Directory.CreateDirectory(@"D:\WXContanct");
}
worksheetData.SaveAs(@"D:\WXContanct\" + worksheetData.Name);
//appexcel.Visible = true;
KillSpecialExcel(appexcel);
}
catch (Exception err)
{
KillSpecialExcel(appexcel);
MessageBox.Show(string.Format("The error happed in{0}", err.Message), "", MessageBoxButtons.OK);
return false;
}
return true;
}
五，关闭程序打开excel的进程
[DllImport("user32.dll", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
public void KillSpecialExcel(Excel.Application appexcel)
{
try
{
if (appexcel != null)
{
int lpdwProcessId;
GetWindowThreadProcessId(new IntPtr(appexcel.Hwnd), out lpdwProcessId);
System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
}
}
catch (Exception ex)
{
Console.WriteLine("Delete Excel Process Error:" + ex.Message);
}
}
262
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
