DataTableToExcel 和 ExcelToDataTable
using System;
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using cfg = System.Configuration;
using System.Runtime.InteropServices;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
namespace OIC.Common.Util
{
/// <summary>
/// Summary description for clsExcel.
/// </summary>
public class clsExcel
{
protected string templetFile = null;
protected string outputFile = null;
protected object missing = Missing.Value;
//// <summary>
/// 构造函數，需指定模板文件和導出文件完整路徑
/// </summary>
/// <param name="templetFilePath">Excel模板文件路?</param>
/// <param name="outputFilePath">?出Excel文件路?</param>
// add by jingping.zhang 20130910
public clsExcel(string templetFilePath, string outputFilePath)
{
if (templetFilePath == null)
throw new Exception("Excel模板文件路徑不能為空！");
if (outputFilePath == null)
throw new Exception("導出Excel文件路徑不能為空！");
if (!File.Exists(templetFilePath))
throw new Exception("指定路徑的Excel模板文件不存在！");
this.templetFile = templetFilePath;
this.outputFile = outputFilePath;
}
//// <summary>
/// ?DataTable數據導入Excel文件（套用模板并分?）
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="rows">每個WorkSheet輸入多少行數据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="sheetPrefixName">WorkSheet前?名，比如：前?名?“Sheet”，那么WorkSheet名?依次?“Sheet-1，Sheet-2”</param>
public void DataTableToExcel(System.Data.DataTable dt, int rows, int top, int left, string sheetPrefixName)
{
int rowCount = dt.Rows.Count; //源DataTable行?
int colCount = dt.Columns.Count; //源DataTable列?
int sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet??
DateTime beforeTime;
DateTime afterTime;
if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
sheetPrefixName = "Sheet";
//創建一個Application對象并使其可用
beforeTime = DateTime.Now;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
//打開模板文件，得到WorkBook對象
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet對象
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
//复制sheetCount-1個WorkSheet對象
for (int i = 1; i < sheetCount; i++)
{
((Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //??起始行索引
int endRow = i * rows; //???束行索引
//若是最后一個WorkSheet，那么???束行索引?源DataTable行?
if (i == sheetCount)
endRow = rowCount;
//?取要?入?据的WorkSheet?象，并重命名
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(i);
sheet.Name = sheetPrefixName + "-" + i.ToString();
//?dt中的?据?入WorkSheet
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
sheet.Cells[top + j, left + k] = dt.Rows[startRow + j][k].ToString();
}
}
//?文本框?据
// Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
// Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
// Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
//
// txtAuthor.Text = "KLY.NET的Blog";
// txtDate.Text = DateTime.Now.ToShortDateString();
// txtVersion.Text = "1.0.0.0";
}
//?出Excel文件并退出
try
{
string sspath = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
sspath = sspath + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
outputFile = outputFile + @"\" + sspath;
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workSheet = null;
workBook = null;
app = null;
GC.Collect();
}
catch (Exception e)
{
throw e;
}
finally
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel?程ID，??只能判??程????
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
}
//// <summary>
/// ?取WorkSheet?量
/// </summary>
/// <param name="rowCount">???行?</param>
/// <param name="rows">每WorkSheet行?</param>
private int GetSheetCount(int rowCount, int rows)
{
int n = rowCount % rows; //余?
if (n == 0)
return rowCount / rows;
else
return Convert.ToInt32(rowCount / rows) + 1;
}
//// <summary>
/// ?二????据?入Excel文件（套用模板并分?）
/// </summary>
/// <param name="arr">二維數組</param>
/// <param name="rows">每個WorkSheet插入多少行數据</param>
/// <param name="top">行索引</param>
/// <param name="left">列索引</param>
/// <param name="sheetPrefixName">WorkSheet前?名，比如：前?名?“Sheet”，那么WorkSheet名?依次?“Sheet-1，Sheet-2”</param>
public void ArrayToExcel(string[,] arr, int rows, int top, int left, string sheetPrefixName)
{
int rowCount = arr.GetLength(0); //二維數組行數（一維長度）
int colCount = arr.GetLength(1); //二維數据列的長度（二維長度）
int sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet個數
DateTime beforeTime;
DateTime afterTime;
if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
sheetPrefixName = "Sheet";
//創建一個Application對象并使其可用
beforeTime = DateTime.Now;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;
/// ?二????据?入Excel文件（套用模板并分?）
Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
//得到WorkSheet?象
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);
//复制sheetCount-1個WorkSheet對象
for (int i = 1; i < sheetCount; i++)
{
((Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
}
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //??起始行索引
int endRow = i * rows; //???束行索引
//若是最后一?WorkSheet，那么???束行索引?源DataTable行?
if (i == sheetCount)
endRow = rowCount;
//?取要?入?据的WorkSheet?象，并重命名
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets.get_Item(i);
sheet.Name = sheetPrefixName + "-" + i.ToString();
//?二???中的?据?入WorkSheet
for (int j = 0; j < endRow - startRow; j++)
{
for (int k = 0; k < colCount; k++)
{
sheet.Cells[top + j, left + k] = arr[startRow + j, k];
}
}
// Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
// Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
// Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
//
// txtAuthor.Text = "KLY.NET的Blog";
// txtDate.Text = DateTime.Now.ToShortDateString();
// txtVersion.Text = "1.0.0.0";
}
//導出Excel文件并退出
try
{
string sspath = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
sspath = sspath + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString();
outputFile = outputFile + @"\" + sspath;
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
workSheet = null;
workBook = null;
app = null;
GC.Collect();
}
catch (Exception e)
{
throw e;
}
finally
{
Process[] myProcesses;
DateTime startTime;
myProcesses = Process.GetProcessesByName("Excel");
//得不到Excel?程ID，??只能判??程????
foreach (Process myProcess in myProcesses)
{
startTime = myProcess.StartTime;
if (startTime > beforeTime && startTime < afterTime)
{
myProcess.Kill();
}
}
}
}
/// <summary>
/// 將Excel中的數據導入到DataTable
/// </summary>
/// <param name="filePath">文件路徑地址 </param>
/// <param name="isColumnName">是否存在導入列名</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
{
DataTable dataTable = null;
FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
int startRow = 0;
try
{
using (fs = File.OpenRead(filePath))
{
// 2007版本
if (filePath.IndexOf(".xlsx") > 0)
workbook = new XSSFWorkbook(fs);
// 2003版本
else if (filePath.IndexOf(".xls") > 0)
workbook = new HSSFWorkbook(fs);
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
dataTable = new DataTable();
if (sheet != null)
{
int rowCount = sheet.LastRowNum;//总行数
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0);//第一行
int cellCount = firstRow.LastCellNum;//列数
//构建datatable的列
if (isColumnName)
{
startRow = 1;//如果第一行是列名，则从第二行开始读取
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
}
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
//填充行
for (int i = startRow; i <= rowCount; ++i)
{
row = sheet.GetRow(i);
if (row == null) continue;
dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
if (format == 14 || format == 31 || format == 57 || format == 58)
dataRow[j] = cell.DateCellValue;
else
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
}
}
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
return dataTable;
}
catch (Exception)
{
if (fs != null)
{
fs.Close();
}
return null;
}
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using EIC.Common.DB;
using System.IO;
using System.Net;
using OIC.Common.Util;
namespace EIC.Forms.Mat
{
public partial class Mat_test : Form
{
private static string ftpAdress = "ftp://10.XX.XX.XXX/";
private static string username = "XXX";
private static string password = "XXX";
private static string sPath = @"D:\TEST" + "\\";
private string[][] sComlum = new string[1][];
public Material_CNTFTINFO()
{
InitializeComponent();
this.tb_LocalPath.Text = sPath;
sComlum[0] = new string[7];
sComlum[0][0] = "名字-1";
sComlum[0][1] = "名字-1";
sComlum[0][2] = "名字-1";
sComlum[0][3] = "名字-1";
sComlum[0][4] = "名字-1";
sComlum[0][5] = "名字-1";
sComlum[0][6] = "名字-1";
}
private delegate void AddMessageDelegate(string sType, string sMessage, bool isClear);
//手動條碼上傳TFT
private void tb_TFTID_KeyUp(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Enter) {
if(tb_TFTID.Equals("")) return;
string TFTID = this.tb_TFTID.Text.ToUpper().Trim();
string sGerChip = " ";
string[][] clsGetChip = clsSql.GetAll(sGerChip);
if (clsGetChip != null && clsGetChip.Length > 0)
{
DataTable dt = Convert(sComlum,clsGetChip);
string fullPath = sPath + TFTID +".csv";
SaveCSV1(dt, fullPath);
Invoke(new AddMessageDelegate(ShowMessage), new object[] { "Info", TFTID + "文件轉換為CSV成功", false });
//將文件上傳到FTP
//創建FTP文件夾
string DataTime = DateTime.Now.ToString("yyyyMMdd");
//bool aflag = MakeDir(ftpAdress + "/" + DataTime, username, password);
//bool aflag = MakeDir(ftpAdress, username, password);
//if (aflag)
//{
bool sFtp = ftpUp(TFTID + ".csv", fullPath);
if (sFtp)
{
Invoke(new AddMessageDelegate(ShowMessage), new object[] { "Info", TFTID + "文件上傳成功", false });
tb_TFTID.Text = "";
File.Delete(fullPath);
}
else
{
Invoke(new AddMessageDelegate(ShowMessage), new object[] { "Info", TFTID + "文件上傳失敗", false });
}
//}
/*else {
Invoke(new AddMessageDelegate(ShowMessage), new object[] { "Error", DataTime + "文件夾創建失敗", false });
}*/
}
}
}
//將文件保存為CSV文件
private static void SaveCSV1(DataTable dt, string fullPath)
{
var fi = new FileInfo(fullPath);
if (!fi.Directory.Exists)
{
fi.Directory.Create();
}
var fs = new FileStream(fullPath, FileMode.Create, FileAccess.Write);
//StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);
var sw = new StreamWriter(fs, Encoding.UTF8);
var data = "";
//写出列名称
for (var i = 0; i < dt.Columns.Count; i++)
{
data += dt.Columns[i].ColumnName;
if (i < dt.Columns.Count - 1)
{
data += ",";
}
}
sw.WriteLine(data);
//写出各行数据
for (var i = 0; i < dt.Rows.Count; i++)
{
data = "";
for (var j = 0; j < dt.Columns.Count; j++)
{
var str = dt.Rows[i][j].ToString();
str = str.Replace("\"", "\"\""); //替换英文冒号 英文冒号需要换成两个冒号
if (str.Contains(',') || str.Contains('"')
|| str.Contains('\r') || str.Contains('\n')) //含逗号 冒号 换行符的需要放到引号中
{
str = string.Format("\"{0}\"", str);
}
data += str;
if (j < dt.Columns.Count - 1)
{
data += ",";
}
}
sw.WriteLine(data);
}
sw.Close();
fs.Close();
}
//將二維數組轉換為DataTable
public static DataTable Convert(string[][] comArrays, string[][] Arrays)
{
DataTable dt = new DataTable();
int a = Arrays.GetLength(0);
for (int i = 0; i < comArrays[0].Length; i++)
{
dt.Columns.Add(comArrays[0][i].ToString(), typeof(string));
}
for (int i1 = 0; i1 < a; i1++)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < Arrays[i1].Length; i++)
{
dr[i] = Arrays[i1][i].ToString();
}
dt.Rows.Add(dr);
}
return dt;
}
private bool ftpUp(string newfileName, string localFilePath)
{
System.Net.WebClient aw = new WebClient();
string DataTime = DateTime.Now.ToString("yyyyMMdd");
aw.Credentials = new NetworkCredential(username, password);
aw.Proxy = null;//電腦使用代理需添加
int Readcount = 0;
bool upFlag = false;
while (Readcount < 5)
{
try
{
//aw.UploadFile(ftpAdress + @"/" + DataTime +"//"+ newfileName, localFilePath);
aw.UploadFile(ftpAdress + "//" + newfileName, localFilePath);
upFlag = true;
Readcount = 10;
}
catch (Exception ex)
{
Readcount++;
Invoke(new AddMessageDelegate(ShowMessage), new object[] { "Error", localFilePath + "文件上傳FTP失敗", false });
}
}
return upFlag;
}
/// <summary>
/// 顯示機台數據信息
/// </summary>
/// <param name="sType"></param>
/// <param name="sMessage"></param>
/// <param name="isClear"></param>
private void ShowMessage(string sType, string sMessage, bool isClear)
{
lock (dgv_msg1)
{
try
{
dgv_msg1.Rows.Insert(0, 1);
dgv_msg1.Rows[0].Cells[0].Value = dgv_msg1.Rows.Count;
dgv_msg1.Rows[0].Cells[1].Value = sMessage;
switch (sType)
{
case "Info":
dgv_msg1.Rows[0].DefaultCellStyle.ForeColor = Color.Blue;
break;
case "Error":
dgv_msg1.Rows[0].DefaultCellStyle.ForeColor = Color.Red;
break;
default:
dgv_msg1.Rows[0].DefaultCellStyle.ForeColor = Color.Blue;
break;
}
}
catch (Exception ex)
{
}
}
}
//By excel上傳CSV文件
private void bt_UpldTFT_Click(object sender, EventArgs e)
{
string fileName = null;
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Microsoft Excel files(*.xls)|*.xls;*.xlsx";//过滤一下，只要表格格式的
ofd.InitialDirectory = "c:\\";
ofd.RestoreDirectory = true;
ofd.FilterIndex = 1;
ofd.AddExtension = true;
ofd.CheckFileExists = true;
ofd.CheckPathExists = true;
ofd.ShowHelp = true;//是否显示帮助按钮
if (ofd.ShowDialog() == DialogResult.OK)
{
fileName = ofd.FileName.ToString();
DataTable dtExcel = clsExcel.ExcelToDataTable(fileName, true);
DirectoryInfo dirs = new DirectoryInfo(sPath);
foreach (DataRow ds in dtExcel.Rows)
{
string TFTID = ds["TFTID"].ToString();
string sGerChip = " ";
string[][] clsGetChip = clsSql.GetAll(sGerChip);
if (clsGetChip != null && clsGetChip.Length > 0)
{
DataTable dt = Convert(sComlum, clsGetChip);
string fullPath = sPath + TFTID + ".csv";
SaveCSV1(dt, fullPath);
Invoke(new AddMessageDelegate(ShowMessage), new object[] { "Info", TFTID + "文件轉換為CSV成功", false });
}
}
//創建FTP文件夾
string DataTime = DateTime.Now.ToString("yyyyMMdd");
//bool aflag = MakeDir(ftpAdress + "//" + DataTime, username, password);
//bool aflag = MakeDir(ftpAdress + "//", username, password);
//if (aflag)
//{
foreach (FileInfo file in dirs.GetFiles())
{
bool sFTPUP = ftpUp(file.Name, sPath + file.Name);
if (sFTPUP)
{
Invoke(new AddMessageDelegate(ShowMessage), new object[] { "Info", file.Name + "文件上傳FTP成功", false });
File.Delete(sPath + file.Name);
}
else
{
Invoke(new AddMessageDelegate(ShowMessage), new object[] { "Error", file.Name + "文件上傳FTP失敗", false });
}
}
//}
/*else {
Invoke(new AddMessageDelegate(ShowMessage), new object[] { "Error", DataTime + "文件夾創建失敗", false });
}*/
}
}
//ftp創建文件夾
public bool MakeDir(string FTPCONSTR, string username, string password)
{
try
{
if (RemoteFtpDirExists(FTPCONSTR, username, password))
{
return true;
}
string url = FTPCONSTR;
FtpWebRequest reqFtp = (FtpWebRequest)FtpWebRequest.Create(new Uri(url));
reqFtp.Proxy = null;
reqFtp.UseBinary = true;
// reqFtp.KeepAlive = false;
reqFtp.Method = WebRequestMethods.Ftp.MakeDirectory;
reqFtp.Credentials = new NetworkCredential(username, password);
FtpWebResponse response = (FtpWebResponse)reqFtp.GetResponse();
response.Close();
return true;
}
catch (Exception ex)
{
//errorinfo = string.Format("因{0},无法下载", ex.Message);
Invoke(new AddMessageDelegate(ShowMessage), new object[] { "Error", ex.Message, false });
return false;
}
}
public bool RemoteFtpDirExists( string FTPCONSTR, string username, string password)
{
FtpWebRequest reqFtp = (FtpWebRequest)FtpWebRequest.Create(new Uri(FTPCONSTR));
reqFtp.UseBinary = true;
reqFtp.Credentials = new NetworkCredential(username, password);
reqFtp.Method = WebRequestMethods.Ftp.ListDirectory;
FtpWebResponse resFtp = null;
reqFtp.Proxy = null;
try
{
resFtp = (FtpWebResponse)reqFtp.GetResponse();
FtpStatusCode code = resFtp.StatusCode;//OpeningData
resFtp.Close();
return true;
}
catch
{
if (resFtp != null)
{
resFtp.Close();
}
return false;
}
}
}
}
扫一扫
22万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
