查看excel文件和导出
public System.Data.DataTable dsmdata()//查看
{
public static string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =d:\\csd.xls;Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(strCon);
string sql = "select * from [Sheet1$]";
conn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strCon);
ds = new DataSet();
myCommand.Fill(ds, "[Sheet1$]");
conn.Close();
return ds.Tables["[Sheet1$]"];
//datagrid1.DataMember = "[Sheet1$]";
//datagrid1.DataSource = ds;
//datagrid1.DataBind();
}
public static bool ExportForDataGridview(DataGridView gridView, string fileName, bool isShowExcle)
//此方法需要加excel引用
{
//建立Ｅｘｃｅｌ对象
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
try
{
if (app == null)
{
return false;
}
app.Visible = isShowExcle;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
return false;
}
string sLen = "";
//取得最后一列列名
char H = (char)(64 + gridView.ColumnCount / 26);
char L = (char)(64 + gridView.ColumnCount % 26);
if (gridView.ColumnCount < 26)
{
sLen = L.ToString();
}
else
{
sLen = H.ToString() + L.ToString();
}
//标题
string sTmp = sLen + "1";
Range ranCaption = worksheet.get_Range(sTmp, "A1");
string[] asCaption = new string[gridView.ColumnCount];
for (int i = 0; i < gridView.ColumnCount; i++)
{
asCaption[i] = gridView.Columns[i].HeaderText;
}
ranCaption.Value2 = asCaption;
//数据
object[] obj = new object[gridView.Columns.Count];
for (int r = 0; r < gridView.RowCount - 1; r++)
{
for (int l = 0; l < gridView.Columns.Count; l++)
{
if (gridView[l, r].ValueType == typeof(DateTime))
{
obj[l] = gridView[l, r].Value.ToString();
}
else
{
obj[l] = gridView[l, r].Value;
}
}
string cell1 = sLen + ((int)(r + 2)).ToString();
string cell2 = "A" + ((int)(r + 2)).ToString();
Range ran = worksheet.get_Range(cell1, cell2);
ran.Value2 = obj;
}
//保存
workbook.SaveCopyAs(fileName);
workbook.Saved = true;
}
finally
{
//关闭
app.UserControl = false;
app.Quit();
}
return true;
}
public void SaveAs(DataGridView dgvAgeWeekSex)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Excel File To";
saveFileDialog.ShowDialog();
Stream myStream;
myStream = saveFileDialog.OpenFile();
//StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string str = "";
try
{
//写标题
for (int i = 0; i < dgvAgeWeekSex.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += dgvAgeWeekSex.Columns[i].HeaderText;
}
sw.WriteLine(str);
//写内容
for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < dgvAgeWeekSex.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dgvAgeWeekSex.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
public void SaveAsdata(System.Data.DataTable dgvAgeWeekSex)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Excel File To";
saveFileDialog.ShowDialog();
Stream myStream;
myStream = saveFileDialog.OpenFile();
//StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string str = "";
try
{
//写标题
for (int i = 0; i < dgvAgeWeekSex.Columns.Count; i++)
{
if (i > 0)
{
str += "\t";
}
str += dgvAgeWeekSex.Columns[i].ColumnName;
}
sw.WriteLine(str);
//写内容
for (int j = 0; j < dgvAgeWeekSex.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < dgvAgeWeekSex.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dgvAgeWeekSex.Rows[j][k].ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
