c# mysql交互
ExcelExcel导入
public void daoru(DevExpress.XtraGrid.GridControl gridControl1, string Table)
{
try
{
OpenFileDialog fd = new OpenFileDialog();
fd.Filter = "Excel(97-2003)文件|*.xlsx|所以文件|*.*";
fd.Title = "打开文件夹";
string path = "";
fd.InitialDirectory = "c:\\";
fd.FilterIndex = 1;
if (fd.ShowDialog() == DialogResult.OK)
{
path = fd.FileName;
}
string tableName = GetExcelFirstTaableName(path);
string Tsql = "SELECT * FROM[" + tableName + "]";
DataTable table = ExcelToDataset(path, Tsql).Tables[0];
my_sql.import(table, Table);
gridControl1.DataSource = table;
}
catch (Exception)
{
}
}
GetExcelFirstTaableName方法
public string GetExcelFirstTaableName(string fullpath)
{
string tableName = null;
if (File.Exists(fullpath))
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fullpath + ";" + "Extended Properties=Excel 12.0;"))
{
conn.Open();
tableName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][2].ToString().Trim();
}
}
return tableName;
}
ExcelToDataset方法
public DataSet ExcelToDataset(string filename, string Tsql)
{
DataSet ds;
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 12.0;data source=" + filename;
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = Tsql;
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
ds = new DataSet();
myCommand.Fill(ds);
myConn.Close();
return ds;
}
datatable导出 全新ExcelExcel表
调用方法
private void toolStripMenuItem2_Click(object sender, EventArgs e)
{
DataTable DtContent = (DataTable)gridControl2.DataSource;
ExportToExcel dtTable = new ExportToExcel();
//dt为数据 str是路径
dtTable.ExportToExcel1(DtContent );
}
导出执行方法
using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Threading;
using System.Windows.Forms;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;
namespace th_core
{
public class ExportToExcel
{
public Excel.Application m_xlApp = null;
/// <summary>
/// 导出数据到Excel中
/// </summary>
/// <param name="dt">DataTable数据源</param>
/// <returns></returns>
public void ExportToExcel1(DataTable dt)
{
if (dt == null) return;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的电脑未安装Excel", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();
saveDia.Filter = "Excel|*.xls";
saveDia.Title = "导出为Excel文件";
if (saveDia.ShowDialog() == System.Windows.Forms.DialogResult.OK && !string.Empty.Equals(saveDia.FileName))
{
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
string fileName = saveDia.FileName;
//写入标题
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
//range.Interior.ColorIndex = 15;//背景颜色
range.Font.Bold = true;//粗体
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
//range.ColumnWidth = 4.63;//设置列宽
//range.EntireColumn.AutoFit();//自动调整列宽
//r1.EntireRow.AutoFit();//自动调整行高
}
//写入内容
for (int r = 0; r < dt.DefaultView.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];
range.Font.Size = 9;//字体大小
//加边框
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.EntireColumn.AutoFit();//自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
System.Windows.Forms.Application.DoEvents();
}
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
try
{
workbook.Saved = true;
workbook.SaveCopyAs(fileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message, "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
workbooks.Close();
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
int generation = System.GC.GetGeneration(xlApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
System.GC.Collect(generation);
}
GC.Collect();//强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killId = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killId = m;
}
}
if (excelProc[killId].HasExited == false)
{
excelProc[killId].Kill();
}
#endregion
MessageBox.Show("导出成功", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public void OutputAsExcelFile(DataTable dtTable, String numStr)
{
string filePath = "";
SaveFileDialog s = new SaveFileDialog();
s.Title = "保存Excel文件";
s.Filter = "Excel文件(*.xls)|*.xls";
s.FilterIndex = 1;
if (s.ShowDialog() == DialogResult.OK)
filePath = s.FileName;
else
return;
//导出dataTable到Excel
//dtTable.Columns.Add("Taskid");
long rowNum = dtTable.Rows.Count;//行数
int columnNum = dtTable.Columns.Count;//列数
String[] numArr = numStr.Split(',');
Excel.Application m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = false;//不显示更改提示
m_xlApp.Visible = false;
Excel.Workbooks workbooks = m_xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
try
{
string[,] datas = new string[rowNum + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
datas[0, i] = dtTable.Columns[i].Caption;
//Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
Excel.Range range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 10;
int r = 0;
for (r = 0; r < numArr.Length - 1; r++)
{
int numRow = int.Parse(numArr[r]) - 1;
for (int i = 0; i < columnNum; i++)
{
object obj;
if (i == columnNum - 1)
{
obj = "价格格式不符合要求。";
}
else
{
obj = dtTable.Rows[numRow][dtTable.Columns[i].ToString()];
}
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
Console.WriteLine(datas[r + 1, i]);
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
//Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
Excel.Range fchR = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
//worksheet.Name = "dd";
//m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
m_xlApp.Visible = false;
// = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
workbook.Saved = true;
workbook.SaveCopyAs(filePath);
}
catch (Exception ex)
{
MessageBox.Show("导出异常:" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
EndReport();
}
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
MessageBox.Show("导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void EndReport()
{
object missing = System.Reflection.Missing.Value;
try
{ }
catch { }
finally
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
m_xlApp = null;
}
catch { }
try
{
//清理垃圾进程
this.killProcessThread();
}
catch { }
GC.Collect();
}
}
private void killProcessThread()
{
ArrayList myProcess = new ArrayList();
for (int i = 0; i < myProcess.Count; i++)
{
try
{
System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
}
catch { }
}
}
}
}
链接: 可学习mysql基础.