-文件上传
private void button3_Click(object sender, EventArgs e)
{string paths = textBox1.Text;//上传文件文件路径
//保存路径
string path = Application.StartupPath + "\\Status.xls";
if (paths != "")
{
//创建webClient实例
WebClient mywebclient = new WebClient();
mywebclient.Credentials = CredentialCache.DefaultCredentials;
//上传文件
FileStream fs = new FileStream(paths, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(fs);
byte[] postArray = r.ReadBytes((int)fs.Length);
Stream postStream = mywebclient.OpenWrite(path, "PUT");
try
{
if (postStream.CanWrite)
{
postStream.Write(postArray, 0, postArray.Length);
postStream.Close();
fs.Dispose();
}
else
{
postStream.Close();
fs.Dispose();
}
}
catch (Exception error)
{
postStream.Close();
fs.Dispose();
}
finally
{
postStream.Close();
fs.Dispose();
}
MessageBox.Show("上传成功!正在生成数据...");
Thread thread = new Thread(new ThreadStart(instructions));
thread.Start();
}
else
{
Thread thread = new Thread(new ThreadStart(instructions));
thread.Start();
}
}
//线程+委托 解决winfrom 代码执行过多假死状态和页面同步
{
DataTable table = new DataTable();
string path = Application.StartupPath + "\\Status.xls";//excel所在路径
table = GetExcelDatatable(path);
//..........执行代码
//委托设置winfrom页面控件
this.BeginInvoke(new MethodInvoker(delegate
{
progressBar1.Minimum = 0;
progressBar1.Maximum = excelTable.Rows.Count;
}));
this.BeginInvoke(new MethodInvoker(delegate
{
dataGridView1.DataSource = table;
this.dataGridView1.Refresh();//后台数据更改页面同步
}));
}
/// <summary>
/// Excel数据导入Datable
/// </summary>
/// <param name="fileUrl">文件路径</param>
/// <param name="table"></param>
/// <returns></returns>
public System.Data.DataTable GetExcelDatatable(string fileUrl)
{
//office2007之前 仅支持.xls
//const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
//支持.xls和.xlsx,即包括office2010等版本的 HDR=Yes代表第一行是标题,不是数据;
const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
System.Data.DataTable dt = null;
//建立连接
OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
try
{
//打开连接
if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
{
conn.Open();
}
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第几个Sheet名称
string sheetName = schemaTable.Rows[2]["TABLE_NAME"].ToString().Trim();//获取第三个excel名称
//查询sheet中的数据
string strSql = "select * from [" + sheetName + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
return dt;
}
catch (Exception exc)
{
throw exc;
}
finally
{
conn.Close();
conn.Dispose();
}
}
//DataGridView导出excel
private void button4_Click(object sender, EventArgs e)
{
TableToExcel.DataGridViewNoTitle(dataGridView1, true);
}
public static bool DataGridViewNoTitle(DataGridView dgv, bool isShowExcel)
{
string fileName = "";//保存的excel文件名
int columnIndex = 1;//列索引
if (dgv.Rows.Count == 0)
return false;
fileName = "D:\\" + DateTime.Now.ToString("yyyyMMddhhmmss");
/*建立Excel对象*/
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
MessageBox.Show("无法创建Excel对象,可能您的计算机未安装Excel!");
return false;
}
try
{
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcel;
/*合并标题单元格*/
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
//生成字段名称
columnIndex = 1;
for (int i = 0; i < dgv.ColumnCount; i++)
{
if (dgv.Columns[i].Visible == true)
{
excel.Cells[1, columnIndex] = dgv.Columns[i].HeaderText;
(excel.Cells[1, columnIndex] as Range).HorizontalAlignment = XlHAlign.xlHAlignCenter;//字段字段靠起方式
columnIndex++;
}
}
//填充数据
for (int i = 0; i < dgv.RowCount; i++)
{
columnIndex = 1;
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv.Columns[j].Visible == true)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, columnIndex] = "'" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, columnIndex] = dgv[j, i].Value.ToString();
}
(excel.Cells[i + 2, columnIndex] as Range).HorizontalAlignment = XlHAlign.xlHAlignLeft;//字段考起方式
columnIndex++;
}
}
}
worksheet.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
catch { }
finally
{
excel.Quit();
excel = null;
GC.Collect();
}
return true;
}