public static void DataGridViewtoExcel_withAsk(DataGridView tmpDataTable, String strDefaultFileName)
{
SaveFileDialog saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
saveFileDialog1.FileName = strDefaultFileName;
saveFileDialog1.DefaultExt = "*.xls";
saveFileDialog1.Filter = "Excel文件|*.xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
HelpTXD.DataGridViewtoExcel(tmpDataTable, saveFileDialog1.FileName);
HelpTXD.ShowInfo("导出完成。" + saveFileDialog1.FileName);
}
}
public static void DataGridViewtoExcel(List <DataGridView> Grids4Excel, List <string> GridText, string strDefaultFileName)
{
if (Grids4Excel == null)
{
return;
}
if (Grids4Excel.Count == 0)
{
return;
}
SaveFileDialog saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
saveFileDialog1.FileName = strDefaultFileName;
saveFileDialog1.DefaultExt = "*.xls";
saveFileDialog1.Filter = "Excel文件|*.xls";
if (saveFileDialog1.ShowDialog() != DialogResult.OK)
{
return;
}
int rowNum;
int columnNum;
int columnIndex = 1;
int maxLineCount;
string lvssss = Application.StartupPath + "\\Microsoft.Office.Interop.Excel.dll";
Assembly ass;
object obj;
ass = Assembly.LoadFile(lvssss);
obj = ass.CreateInstance("Microsoft.Office.Interop.Excel.ApplicationClass");
Microsoft.Office.Interop.Excel.Application xlApp = obj as Microsoft.Office.Interop.Excel.Application;
xlApp.Visible = true;
xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = Grids4Excel.Count;
object m_objOpt = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
DataGridView lvGrid;
for (int lvSheetNo = 0; lvSheetNo < Grids4Excel.Count; lvSheetNo++)
{
lvGrid = Grids4Excel[lvSheetNo];
rowNum = lvGrid.Rows.Count;
columnNum = lvGrid.Columns.Count;
columnIndex = 1;
maxLineCount = rowNum + 1;
object[,] objData = new Object[maxLineCount, columnNum];
columnIndex = 0;
for (int i = 0; i < lvGrid.Columns.Count; i++)
{
DataGridViewColumn dc = lvGrid.Columns[i];
if (dc.Visible == true && dc.GetType().Name != "DataGridViewButtonColumn")
{
objData[0, columnIndex] = dc.HeaderText;
columnIndex++;
}
}
string lvTmpvalue;
decimal lviTmpValue;
for (int r = 0; r < rowNum; r++)
{
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
if (lvGrid.Columns[j].Visible == true && lvGrid.Columns[j].GetType().Name != "DataGridViewButtonColumn")
{
if (lvGrid.Rows[r].Cells[j].Value == null)
{
objData[r + 1, columnIndex] = "";
}
else
{
lvTmpvalue = lvGrid.Rows[r].Cells[j].Value.ToString().Trim();
if (decimal.TryParse(lvTmpvalue, out lviTmpValue))
{
if (lviTmpValue == 0)
{
lvTmpvalue = "";
}
}
objData[r + 1, columnIndex] = lvTmpvalue;
}
columnIndex++;
}
}
}
//写入excel
if (xlBook.Sheets.Count < lvSheetNo + 1)
{
xlBook.Sheets.Add(m_objOpt, m_objOpt, m_objOpt, m_objOpt);
}
//xlBook.Sheets.get_Item(lvSheetNo + 1);
Microsoft.Office.Interop.Excel._Worksheet xlSheet = (Microsoft.Office.Interop.Excel._Worksheet)(xlBook.Sheets.get_Item(1));
//xlSheet.Range["A1"].Select();
xlSheet.Activate();
xlSheet.Name = GridText[lvSheetNo];
Microsoft.Office.Interop.Excel.Range m_objRange = null;
m_objRange = xlSheet.get_Range("A1", m_objOpt);
m_objRange = m_objRange.get_Resize(maxLineCount, columnNum);
m_objRange.Value = objData;
}
try
{
xlBook.SaveCopyAs(System.Web.HttpUtility.UrlDecode(saveFileDialog1.FileName, System.Text.Encoding.UTF8));
xlBook.Saved = true;
xlApp.Quit();
HelpTXD.ShowInfo("导出完成。" + saveFileDialog1.FileName);
}
catch //(Exception ex)
{
xlBook.Saved = true;
xlApp.Quit();
throw;
}
}
