public static bool GenExcel <T>(string filepath, string sheetName, string[] headers, string[] pathes, List <T> items)
{
Application excel = new Application();
excel.Application.Workbooks.Add(true);
excel.Visible = false;
excel.DisplayAlerts = false;
Workbooks books = excel.Workbooks;
_Workbook book = books.Add(XlWBATemplate.xlWBATWorksheet);
_Worksheet sheet = book.ActiveSheet;
try
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
sheet.Name = sheetName;
for (int i = 0; i < headers.Length; i++)
{
excel.Cells[1, i + 1] = headers[i];
}
Type t = typeof(T);
for (int i = 0; i < items.Count; i++)
{
for (int j = 0; j < pathes.Length; j++)
{
object v = t.GetProperty(pathes[j]).GetValue(items[i], null);
excel.Cells[2 + i, j + 1] = v == null ? "" : v.ToString();
}
}
Range range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[items.Count + 1, headers.Length + 1]];
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheet.SaveAs(filepath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
}
catch (Exception e)
{
return(false);
}
finally
{
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
Marshal.ReleaseComObject(excel);
GC.Collect();
}
return(true);
}
public void SetRuntimeValue(string name, object value)
{
PropertyValueLinker pvl;
if (_properties.TryGetValue(name, out pvl))
{
System.Reflection.Missing mv = value as System.Reflection.Missing;
if (mv != null)
{
pvl.UserRuntimeValue = false;
}
else
{
pvl.UserRuntimeValue = true;
pvl.RuntimeValue = value;
}
}
}
public void printAll(DataTable dt)
{
if (dt != null)
{
try
{
//保存文件
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = false;
saveFileDialog.Title = "导出文件保存路径";
saveFileDialog.ShowDialog();
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
//实例化一个excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = false;
if (excel == null)
{
MessageBox.Show("Excel无法启动!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = "sheet1";
//int m = 0, n = 0;
//生成表头
for (int i = 0; i < dt.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数据
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
string str = dt.Rows[i][j].ToString();
excel.Cells[i + 2, j + 1] = str;
}
}
}
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
MessageBox.Show("数据已成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
System.Diagnostics.Process.Start(strName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误提示");
}
}
}
internal static void GetUnitySerializationInfo(SerializationInfo info, Missing missing)
{
info.SetType(typeof(UnitySerializationHolder));
info.AddValue("UnityType", MissingUnity);
}
public void Download(int ImageColNum, string filePathName, string tempFileName)
{
//设置response
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.BufferOutput = true;
System.Web.HttpContext.Current.Response.Charset = System.Text.Encoding.UTF8.HeaderName;
System.Web.HttpContext.Current.Response.BufferOutput = true;
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + DownloadFileName + ".xls");
System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
System.Web.HttpContext.Current.Response.HeaderEncoding = System.Text.Encoding.UTF8;
//设置HTML头
System.Reflection.Missing MissingValue = System.Reflection.Missing.Value;
Application app = new Application();
Workbooks wks = app.Workbooks;
Workbook wk = wks.Add(MissingValue);
Sheets wss = wk.Worksheets;
Worksheet ws = (Worksheet)wss.get_Item(1);
app.Visible = false;
app.DisplayAlerts = false;
int i = 1, j = 2;
//循环Columns 设置列名
if (exportDataSource != null)
{
((Range)ws.Cells[1, 1]).Value2 = "Picture";
foreach (DataColumn dc in exportDataSource.Columns)
{
if (dc.Caption != "Picture")
{
//((Range)ws.Cells[i, j]).Value2 = dc.Caption;
if (dc.Caption == "Name")
{
((Range)ws.Cells[i, j]).ColumnWidth = 25;
((Range)ws.Cells[i, j]).Value2 = "Name";
}
else if (dc.Caption == "Style")
{
((Range)ws.Cells[i, j]).ColumnWidth = 20;
((Range)ws.Cells[i, j]).Value2 = "Model Number";
}
else if (dc.Caption == "Barcode")
{
((Range)ws.Cells[i, j]).ColumnWidth = 14;
((Range)ws.Cells[i, j]).Value2 = "Barcode";
}
else if (dc.Caption == "Type_Name")
{
((Range)ws.Cells[i, j]).ColumnWidth = 14;
((Range)ws.Cells[i, j]).Value2 = "Category";
}
else if (dc.Caption == "SType_Name")
{
((Range)ws.Cells[i, j]).ColumnWidth = 14;
((Range)ws.Cells[i, j]).Value2 = "Sub Category";
}
else if (dc.Caption == "Color")
{
((Range)ws.Cells[i, j]).ColumnWidth = 12;
((Range)ws.Cells[i, j]).Value2 = "Color";
}
else if (dc.Caption == "Size")
{
((Range)ws.Cells[i, j]).ColumnWidth = 6;
((Range)ws.Cells[i, j]).Value2 = "Size";
}
else if (dc.Caption == "Order_Number")
{
((Range)ws.Cells[i, j]).ColumnWidth = 15;
((Range)ws.Cells[i, j]).Value2 = "Order quantity";
}
else if (dc.Caption == "Real_Number")
{
((Range)ws.Cells[i, j]).ColumnWidth = 15;
((Range)ws.Cells[i, j]).Value2 = "Real quantity";
}
else if (dc.Caption == "Descriptions")
{
((Range)ws.Cells[i, j]).ColumnWidth = 30;
((Range)ws.Cells[i, j]).Value2 = "Descriptions";
}
j++;
}
//sb.AppendFormat("<td>{0}</td>", dc.Caption);
}
i++;
}
((Range)ws.Cells[1, 1]).RowHeight = 22;
string imgPath; // 图片地址
int tempRowIndex; // 当前图片所在行
//循环TABLE设置数据行
if (exportDataSource != null && exportDataSource.Rows.Count > 0)
{
// 初始化图片地址和当前图片行
imgPath = filePathName + exportDataSource.Rows[0]["Picture"].ToString();
tempRowIndex = 2;
Pictures pics = (Pictures)ws.Pictures(MissingValue);
float PictureWidth, PictureHeight;
PictureWidth = 135;
PictureHeight = 80;
((Range)ws.Cells[1, 1]).ColumnWidth = 30;
// 第一行插入图片
if (imgPath != string.Empty)
{
if (File.Exists(imgPath))
{
Bitmap bp = new Bitmap(imgPath); //todo:
PictureHeight = bp.Height * PictureWidth / bp.Width;
Range r = (Range)ws.Cells[2, ImageColNum];
r.Select();
//ws.Shapes.AddPicture(imgPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToInt32(r.Left), Convert.ToInt32(r.Top) + 1, PictureWidth, PictureHeight);
}
//ws.Shapes.AddPicture(imgPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 10, 10, PictureWidth, PictureHeight);
}
foreach (DataRow dr in exportDataSource.Rows)
{
// 填充单元格
j = 2;
foreach (DataColumn dc in exportDataSource.Columns)
{
if (j != ImageColNum && dc.Caption != "Picture")
{
((Range)ws.Cells[i, j]).Value2 = "'" + dr[dc.Caption];
}
//sb.AppendFormat("<td>{0}</td>", dr[dc.Caption]);
j++;
}
//填充图片
if (filePathName + dr["Picture"].ToString() != imgPath)
{
if (imgPath != filePathName)
{
//设置高度和宽度
int rowCount = i - tempRowIndex;
if (rowCount * 20 < PictureHeight)
{
for (int tempi = tempRowIndex; tempi < i; tempi++)
{
((Range)ws.Cells[tempi, ImageColNum]).RowHeight = PictureHeight / rowCount + 3;
}
}
//合并单元格
if (i > 2)
{
Range tempr = ws.get_Range(ws.Cells[tempRowIndex, ImageColNum], ws.Cells[i - 1, ImageColNum]);
tempr.MergeCells = true;
}
else
{
Range tempr = ws.get_Range(ws.Cells[tempRowIndex, ImageColNum], ws.Cells[i, ImageColNum]);
tempr.MergeCells = true;
}
}
//重新设置临时变量
imgPath = filePathName + dr["Picture"].ToString();
tempRowIndex = i;
if (File.Exists(imgPath))
{
Bitmap bp = new Bitmap(imgPath); //todo:
PictureHeight = bp.Height * PictureWidth / bp.Width;
Range r = (Range)ws.Cells[i, ImageColNum];
r.Select();
//ws.Shapes.AddPicture(imgPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, (float)r.Left, (float)r.Top, PictureWidth, PictureHeight);
//ws.Shapes.AddPicture(imgPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToInt32(r.Left), Convert.ToInt32(r.Top) + 1, PictureWidth, PictureHeight);
//ws.Shapes.AddPicture(imgPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 10, 10, 150, 150);
}
}
i++;
}
//设置最后一个图片的高度和宽度
int rowCount2 = i - tempRowIndex;
if (rowCount2 * 20 < PictureHeight)
{
for (int tempi = tempRowIndex; tempi < i; tempi++)
{
((Range)ws.Cells[tempi, ImageColNum]).RowHeight = PictureHeight / rowCount2 + 3;
}
}
//合并单元格
Range tempr2 = ws.get_Range(ws.Cells[tempRowIndex, ImageColNum], ws.Cells[i - 1, ImageColNum]);
tempr2.MergeCells = true;
}
//导出数据
wk.SaveAs(tempFileName, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, XlSaveAsAccessMode.xlNoChange, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue);
//关闭文件，退出EXCEL
//System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(wk);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
//GC.Collect(); //显式调用GC
wk.Close(false, MissingValue, MissingValue);
app.Quit();
//释放Excel资源
if (wks != null && app != null)
{
foreach (_Workbook book in wks)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(wks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
}
app = null;
wks = null;
wk = null;
ws = null;
GC.Collect();
//app.Visible = true;
//Kill(app);
//下载文件
System.Web.HttpContext.Current.Response.WriteFile(tempFileName);
System.Web.HttpContext.Current.Response.End();
}
public void ToExcel1(DataGridView gridView)
{
//try
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.InitialDirectory = System.Windows.Forms.Application.StartupPath;
if (gridView.Rows.Count == 0)
{
MessageBox.Show("没有数据可供导出！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
/*
* 获取保存EXCEL的路径
*/
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出文件保存路径";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
//strName储存保存EXCEL路径
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);;
//若是true，则在导出的时候会显示EXcel界面。
excel.Visible = false;
if (excel == null)
{
MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = "test";
//给进度条赋最大值，为gridView的行数
//progressBar1.Maximum = gridView.RowCount;
//清零计数并开始计数
//TimeP = new System.DateTime(0);
//timer1.Start();
//label1.Text = TimeP.ToString("HH:mm:ss");
//生成字段名称，逐条写，无效率
for (int i = 0; i < gridView.ColumnCount; i++)
{
excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText.ToString();
}
//以下为填充数据关键代码，逐条写，无效率
for (int i = 0; i < gridView.RowCount; i++)
{
for (int j = 0; j < gridView.ColumnCount; j++)
{
if (gridView[j, i] != null && gridView[j, i].Value != null)
{
if (gridView[j, i].Value == typeof(string))
{
excel.Cells[i + 2, j + 1] = "" + gridView[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
}
}
else
{
excel.Cells[i + 2, j + 1] = "";
}
}
//进度条加1
//progressBar1.Value++;
/*
* 注意此Application.DoEvents()，如果无此句，当切换窗口后回到本程序无法重绘窗体会出现假死状态
* 此处我试过用委托和线程异步调用的方法，但效果没有这句效果好
*/
System.Windows.Forms.Application.DoEvents();
}
//保存EXCEL并释放资源
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
//timer1.Stop();
MessageBox.Show("数据已经成功导出到：" + saveFileDialog.FileName.ToString(), "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
//catch (Exception er)
{
// MessageBox.Show(er.Message);
//timer1.Stop();
return;
}
}
public static void CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title)
{
//文件存在时先删除文件后再进行下一步操作
FileInfo file = new FileInfo(strFullFilePath);
if (file.Exists)
{
file.Delete();
}
int rowIndex = 3; //开始写入数据的单元格行
int colIndex = 0; //开始写入数据的单元格列
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Excel.ApplicationClass mExcel = new Excel.ApplicationClass();
mExcel.Visible = false;
Excel.Workbooks mBooks = (Excel.Workbooks)mExcel.Workbooks;
Excel.Workbook mBook = (Excel.Workbook)(mBooks.Add(miss));
Excel.Worksheet mSheet = (Excel.Worksheet)mBook.ActiveSheet;
Excel.Range er = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本
er.Value2 = title;
try
{
foreach (DataColumn col in table.Columns) //将所得到的表的列名,赋值给单元格
{
colIndex++;
mSheet.Cells[3, colIndex] = col.ColumnName;
}
foreach (DataRow row in table.Rows) //同样方法处理数据
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
if (colIndex == 2)
{
mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失
}
else
{
mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//保存工作已写入数据的工作表
mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
// return true;
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
finally //finally中的代码主要用来释放内存和中止进程()
{
mBook.Close(false, miss, miss);
mBooks.Close();
mExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(er);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
GC.Collect();
}
//return false;
}
/// <summary>
/// 导出Excel文件
/// </summary>
public static int DataTableToExcel(DataTable dt, string title, string columns, string sheetName, string fileName)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.Title = "导出文件保存路径";
saveFileDialog.FileName = "LZ_" + fileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
excel.Application.Workbooks.Add(true);;
excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
if (excel == null)
{
Logger.Error("Excel文件保存失败。", null);
return(CConstant.EXPORT_FAILURE);
}
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = sheetName;
int m = 0, n = 0;
//生成列名称 这里i是从1开始的 因为我第0列是个隐藏列ID 没必要写进去
string[] strHeader = title.Split(',');
string[] strColumns = columns.Split(',');
for (int i = 0; i < strHeader.Length; i++)
{
excel.Cells[1, i + 1] = strHeader[i].ToString();
}
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
//j也是从1开始 原因如上 每个人需求不一样
int j = 1;
foreach (string str in strColumns)
{
try
{
if (dr[str.Trim()].GetType() == typeof(string))
{
excel.Cells[i + 2, j] = "'" + dr[str.Trim()].ToString();
j++;
}
else
{
excel.Cells[i + 2, j] = dr[str.Trim()].ToString();
j++;
}
}
catch
{
j++;
continue;
}
}
}
try
{
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
//MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//System.Diagnostics.Process.Start(strName);
}
catch (Exception ex)
{
Logger.Error("Excel文件保存失败。", ex);
return(CConstant.EXPORT_FAILURE);
}
}
return(CConstant.EXPORT_SUCCESS);
}
else
{
return(CConstant.EXPORT_CANCEL);
}
return(CConstant.EXPORT_SUCCESS);
}
/// <summary>
/// DataSet转出为Excel
/// </summary>
/// <param name="ds"></param>
/// <param name="sheetName"></param>
/// <param name="strFileName"></param>
public static void DataSetToExcel(DataSet ds, string sheetName, string strFileName)
{
if (ds == null || ds.Tables.Count == 0)
{
return;
}
System.Reflection.Missing miss = System.Reflection.Missing.Value;
//创建excel对象appExcel，Workbook对象，WorkSheet对象，Range对象
Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbookData;
Microsoft.Office.Interop.Excel.Worksheet worksheetData;
Microsoft.Office.Interop.Excel.Range rangeData;
appExcel.Visible = false;
System.Globalization.CultureInfo currentCi = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
workbookData = appExcel.Workbooks.Add(miss);
try
{
for (int k = 0; k < ds.Tables.Count; k++)
{
worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
worksheetData.Name = ds.Tables[k].TableName;
worksheetData.Columns.EntireColumn.AutoFit();
if (ds.Tables[k] != null)
{
for (int i = 0; i < ds.Tables[k].Columns.Count; i++)
{
worksheetData.Cells[i + 1] = ds.Tables[k].Columns[i].ColumnName.ToString();
}
//先给range对象一个范围从A2开始，range对象可以给一个cell范围，也可以给例如A1-H10这样的范围
//因为第一行已经写了表头，所以所有数据都从A2开始
rangeData = worksheetData.get_Range("A2", miss);
int iRowCount = ds.Tables[k].Rows.Count;
int iColumnAccount = ds.Tables[k].Columns.Count;
//在内存中声明一个iEachSize*iColumnAccount的数组，iEachSize是每次最大存储的行数，iColumnAccount就是最大存储的实际列数
object[,] objVal = new object[iRowCount, iColumnAccount];
//方法一
//for(int i=0;i<ds.Tables[k].Rows.Count;i++)
//{
// for (int j = 0; j < iColumnAccount; j++)
// {
// objVal[i, j] = ds.Tables[k].Rows[i][j].ToString();
// worksheetData.Cells[i+2,j+1]=objVal[i,j];
// }
// System.Windows.Forms.Application.DoEvents();
//}
//方法2
Microsoft.Office.Interop.Excel.Range xlRange = null;
for (int i = 0; i < iRowCount; i++)
{
for (int j = 0; j < iColumnAccount; j++)
{
objVal[i, j] = ds.Tables[k].Rows[i][j].ToString();
}
if (i % 1000 == 0)
{
System.Windows.Forms.Application.DoEvents();
}
}
xlRange = worksheetData.get_Range(appExcel.Cells[2, 1], appExcel.Cells[iRowCount + 1, iColumnAccount]);
//调用Range的Value2属性，把内存中的值赋给Excel
xlRange.Value2 = objVal;
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
xlRange = null;
}
}
workbookData.Saved = true;
appExcel.DisplayAlerts = false;
workbookData.SaveAs(strFileName + "", miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
appExcel.DisplayAlerts = true;
appExcel.Quit();
//结束之前恢复环境
System.Threading.Thread.CurrentThread.CurrentCulture = currentCi;
GC.Collect();
}
catch (Exception ex)
{
appExcel.Quit();
Kill(appExcel);
System.Threading.Thread.CurrentThread.CurrentCulture = currentCi;
GC.Collect();
}
}
public static XLSWorker Create(string filepath, bool overwrite = true, /* XlFileFormat format = XlFileFormat.xlWorkbookNormal ,*/ Missing passwd = null)
{
if (string.IsNullOrEmpty(filepath))
throw new ArgumentException("Bad argumenst");
passwd = passwd ?? Missing.Value;
object missing = Missing.Value;
Application xlapp = new Application();
xlapp.DisplayAlerts = false;
xlapp.ScreenUpdating = false;
Workbook workBook;
if (xlapp.Workbooks.Count == 0)
{
workBook = xlapp.Workbooks.Add(missing);
}
else
{
workBook = xlapp.ActiveWorkbook;
}
if (File.Exists(filepath))
{
if (overwrite)
{
File.Delete(filepath);
}
else
{
throw new ArgumentException("File already exists and 'overwrite' flag is FALSE");
}
}
workBook.SaveAs(filepath, XlFileFormat.xlWorkbookNormal /* format */ , passwd, missing, false, false, XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
return new XLSWorker(xlapp);
}
public void ExcelOut(List <MatchedData> lst, string sFileName)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
MatchedData matchecdData = new MatchedData();
Excel.Application excelApplication = new Excel.Application();
Excel.Workbook excelWorkbook = excelApplication.Workbooks.Add();
excelApplication.UserControl = true;
excelApplication.Application.DisplayAlerts = false;
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(miss, miss, miss, miss);
excelWorksheet.Name = "Protein";
int iRow = 0;
try
{
for (int i = excelWorkbook.Sheets.Count; i >= 1; i--)
{
Excel.Worksheet excelWorksheetTemp = (Excel.Worksheet)excelWorkbook.Sheets[i];
if (excelWorksheetTemp.Name != "Protein")
{
excelWorksheetTemp.Delete();
}
}
//Header
iRow = 1;
excelWorksheet.Cells[iRow, 1] = "Index";
excelWorksheet.Cells[iRow, 2] = "ID";
excelWorksheet.Cells[iRow, 3] = "MOD_RES";
excelWorksheet.Cells[iRow, 4] = "PTM_Score";
excelWorksheet.Cells[iRow, 5] = "TotalPTM_Score";
excelWorksheet.Cells[iRow, 6] = "Z";
excelWorksheet.Cells[iRow, 7] = "DatasetFile";
excelWorksheet.Cells[iRow, 8] = "Database";
excelWorksheet.Cells[iRow, 9] = "Repository";
excelWorksheet.Cells[iRow, 10] = "ScreeningApproach";
excelWorksheet.Cells[iRow, 11] = "ExpMZ";
excelWorksheet.Cells[iRow, 12] = "TheoMZ";
excelWorksheet.Cells[iRow, 13] = "Error_PPM";
excelWorksheet.Cells[iRow, 14] = "AccessionNumber";
excelWorksheet.Cells[iRow, 15] = "ProteinName";
excelWorksheet.Cells[iRow, 16] = "Length";
excelWorksheet.Cells[iRow, 17] = "Function";
excelWorksheet.Cells[iRow, 18] = "Sequence";
excelWorksheet.Cells[iRow, 19] = "NMFs";
excelWorksheet.Cells[iRow, 20] = "Actual_PMFs";
//Value
iRow = 2;
for (int i = 0; i < lst.Count; i++)
{
matchecdData = lst[i];
excelWorksheet.Cells[iRow, 1] = matchecdData.Index;
excelWorksheet.Cells[iRow, 2] = matchecdData.ID;
excelWorksheet.Cells[iRow, 3] = matchecdData.MOD_RES.Replace(";", "").Replace(",", "");
excelWorksheet.Cells[iRow, 4] = matchecdData.DataBase_PTM_SCORE;
excelWorksheet.Cells[iRow, 5] = matchecdData.Total_PTM_Score;
excelWorksheet.Cells[iRow, 6] = matchecdData.Z;
excelWorksheet.Cells[iRow, 7] = matchecdData.DatasetFile;
excelWorksheet.Cells[iRow, 8] = matchecdData.Database;
excelWorksheet.Cells[iRow, 9] = matchecdData.Repository;
excelWorksheet.Cells[iRow, 10] = matchecdData.ScreeningApproach;
excelWorksheet.Cells[iRow, 11] = matchecdData.ExpMZ;
excelWorksheet.Cells[iRow, 12] = matchecdData.TheoMZ;
excelWorksheet.Cells[iRow, 13] = matchecdData.Error_PPM;
excelWorksheet.Cells[iRow, 14] = matchecdData.AccessionNumber;
excelWorksheet.Cells[iRow, 15] = matchecdData.ProteinName;
excelWorksheet.Cells[iRow, 16] = matchecdData.Length;
excelWorksheet.Cells[iRow, 17] = matchecdData.Function;
excelWorksheet.Cells[iRow, 18] = matchecdData.Sequence;
excelWorksheet.Cells[iRow, 19] = matchecdData.NMFS;
excelWorksheet.Cells[iRow, 20] = matchecdData.Actualpmfs;
iRow++;
}
excelWorkbook.SaveAs(sFileName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
excelWorkbook.Close(false, miss, miss);
}
catch (Exception ex)
{
throw ex;
}
finally
{
matchecdData = null;
if (excelApplication != null)
{
excelApplication.Workbooks.Close();
excelApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication);
System.GC.Collect();
}
if (excelApplication != null)
{
IntPtr t = new IntPtr(excelApplication.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
}
excelApplication = null;
GC.Collect();
}
}
/// <summary>
/// DataSet導出到Excel，可以指定SheetName、文件名和是否要保存，也可以指定保存后是否自動退出
/// </summary>
/// <param name="ds">要導出的DataSet</param>
/// <param name="sheetNames">字符串數組，用來存放SheetName，數量需要和DataSet中的Table數一致，輸入null則自動給sheet編號</param>
/// <param name="fileName">指定的文件名，不帶路徑</param>
/// <param name="save">是否要保存，默認存放在桌面上</param>
/// <param name="savePath">文件存放的路徑，最後不要帶\\</param>
/// <param name="quitAfterSave">導出完成后是否要自動退出</param>
public static void ExportToExcel(DataSet ds, string[] sheetNames, string fileName, bool save, string savePath, bool quitAfterSave)
{
if (ds == null)
{
return;
}
if (sheetNames == null)
{
sheetNames = new string[256];
}
System.Reflection.Missing Miss = System.Reflection.Missing.Value;
string SheetName = "";
if (string.IsNullOrEmpty(fileName))
{
fileName = "Result.xlsx";
}
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlBook = xlApp.Application.Workbooks.Add(true);
Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.ActiveSheet;
Excel.Range ra;
xlApp.Visible = true;
xlApp.DisplayAlerts = true;
xlApp.Caption = fileName;
SheetName = (sheetNames[0] != null) ? sheetNames[0].ToString() : "Sheet1";
xlSheet.Name = SheetName;
//寫第一個DataTable
WriteExcelModule(xlSheet, ds.Tables[0]);
//如果DataTable數量大於1，循環寫
if (ds.Tables.Count > 1)
{
for (int z = 1; z < ds.Tables.Count; z++)
{
xlBook.Worksheets.Add(Miss, xlBook.ActiveSheet, Miss, Miss);
xlSheet = (Excel.Worksheet)xlBook.Worksheets[z + 1];
SheetName = (sheetNames[z] != null) ? sheetNames[z].ToString() : "Sheet" + Convert.ToString(z + 1);
xlSheet.Name = SheetName;
xlSheet.Activate();
WriteExcelModule(xlSheet, ds.Tables[z]);
}
}
xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
xlSheet.Activate();
if (save)
{
if (savePath != "")
{
if (System.IO.Directory.Exists(savePath) == false)
{
System.IO.Directory.CreateDirectory(savePath);
}
}
else
{
savePath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop);
}
xlBook.SaveAs(savePath + "\\" + fileName, Miss, null, null, null, null, Excel.XlSaveAsAccessMode.xlNoChange,
null, null, null, null, null);
if (quitAfterSave)
{
xlApp.Quit();
}
}
xlSheet = null;
xlBook = null;
xlApp = null;
GC.Collect();
}
public void coutExcel(DataGridView dataGridView1)
{
try
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出文件保存路径";
saveFileDialog.ShowDialog();
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
//无数据则不再执行
if (dataGridView1.Rows.Count == 0)
{
return;
}
//实例化Excel.Application对象
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = false;//为TRUE在导出时显示EXCEL界面
if (excel == null)
{
MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Microsoft.Office.Interop.Excel.Workbooks books = excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = "test";
//int m = 0, n = 0;
//生成EXCEL列名
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名
}
//存储数据
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
if (dataGridView1[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
}
}
}
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
System.Diagnostics.Process.Start(strName);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "错误提示");
}
}
private bool XLSConvertToPDF(string sourcePath, string targetPath)
{
bool result = false;
Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF;
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = null;
Microsoft.Office.Interop.Excel._Workbook ExcelBook = null;
try
{
object target = targetPath;
object type = targetType;
//workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
// missing, missing, missing, missing, missing, missing, missing, missing, missing);
// sourcePath = "C:\\Users\\IBM_ADMIN\\Desktop\\newadd.xlsx";
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
ExcelBook = ExcelApp.Workbooks.Open(sourcePath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
//ExcelApp.Visible = true;
//ExcelApp.ScreenUpdating = true;
////ActiveWindow.SmallScroll Down:=6;
//ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
////ExcelApp.ActiveWindow.SmallScroll = Excel.;
//ExcelApp.ActiveWindow.Zoom = 80;
////ActiveWindow.SmallScroll Down:=-3
//// excelRange.WrapText = true;
////ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
//ActiveWindow.SmallScroll Down:=30
//Set ActiveSheet.HPageBreaks(1).Location = Range("A67")
//ActiveWindow.SmallScroll Down:=-75
//Microsoft.Office.Interop.Excel.Worksheet WS2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[2];
////上边距
//double top = 0;
////左边距
//double left = 0;
////右边距
//double right = 0;
////下边距
//double footer = 0;
//WS2.DisplayAutomaticPageBreaks = false;//显示分页线
//WS2.PageSetup.CenterFooter = "第 &P 页，共 &N 页";
//WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(top / 2.54);//上
//WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(footer / 15.54);//下
//WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(left / 2.54);//左
//WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(right / 2.54);//右
//WS2.PageSetup.CenterHorizontally = true;//水平居中 xlSheet.PageSetup.PrintTitleRows = "$1:$3";//顶端标题行
//WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3;//A3纸张大小 xlSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//纸张方向.横向
//Excel.Range excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[64, 24]);
//自动调整列宽
//// excelRange.EntireColumn.AutoFit();
//// excelRange.WrapText = false; //文本自动换行
//excelRange.ShrinkToFit = false;
////设置字体在单元格内的对其方式
//excelRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//// 文本水平居中方式
//excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//////设置为横向打印
////WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
////ExcelApp.ActiveWindow.FreezePanes = true;
////excelRange.EntireColumn.AutoFit();
////WS2.PageSetup.Orientation = 2;
//WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
//WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.HeaderMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.FooterMargin = ExcelApp.InchesToPoints(0.0);
//WS2.PageSetup.CenterHorizontally = true;
//// WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
//WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
//excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[2, 20]);
//WS2.PageSetup.PrintTitleRows = excelRange.get_Address(excelRange.Row, excelRange.Column, Excel.XlReferenceStyle.xlA1, 1, 1);
ExcelBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
result = true;
}
catch
{
result = false;
}
finally
{
if (ExcelBook != null)
{
ExcelBook.Close(true, missing, missing);
ExcelBook = null;
}
if (ExcelApp != null)
{
ExcelApp.Quit();
ExcelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(result);
}
private string DownLoadExcel_Crosscheckfile(List <clsFAinfo> Result)
{
try
{
ProcessLogger.Fatal("8948 Down Initial" + DateTime.Now.ToString());
#region 获取模板路径
string fullPath = AppDomain.CurrentDomain.BaseDirectory + "System\\confing.xls";
SaveFileDialog sfdDownFile = new SaveFileDialog();
sfdDownFile.OverwritePrompt = false;
string DesktopPath = Convert.ToString(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop));
sfdDownFile.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx";
string fileinfo = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources\\");
sfdDownFile.FileName = Path.Combine(fileinfo, "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls");
string strExcelFileName = string.Empty;
string ResaveName = AppDomain.CurrentDomain.BaseDirectory + "Resources\\" + "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls";
#endregion
#region 导出前校验模板信息
if (string.IsNullOrEmpty(sfdDownFile.FileName))
{
MessageBox.Show("File name can't be empty, please Check, thanks!");
return("");
}
if (!File.Exists(fullPath))
{
MessageBox.Show("Template file does not exist, please Check, thanks!");
return("");
}
else
{
strExcelFileName = sfdDownFile.FileName;
}
#endregion
#region Excel 初始化
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Microsoft.Office.Interop.Excel.Range rng;
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel._Workbook ExcelBook =
ExcelApp.Workbooks.Open(fullPath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
#endregion
ProcessLogger.Fatal("8949 Input Initial" + DateTime.Now.ToString());
#region 导入
try
{
#region Sheet 初始化
Microsoft.Office.Interop.Excel._Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];
#region 填充数据
int RowIndex = 6;
string fapiaoleixingin = "";
string guidangren = "";
string danghao = "";
string guidangshijian = "";
foreach (clsFAinfo item in Result)
{
fapiaoleixingin = item.fapiaoleixing;
guidangren = item.guidangrenzhanghao;
danghao = item.danganhao;
if (item.Input_Date != null && item.Input_Date.Length > 8)
{
guidangshijian = item.Input_Date.Substring(0, 8);
}
RowIndex++;
ExcelSheet.Cells[RowIndex, 1] = "'" + item.fapiaohao;
ExcelSheet.Cells[RowIndex, 2] = "'" + item.bianhao;
//ExcelSheet.Cells[RowIndex, 3] = item.fapiaohao;
}
//ExcelApp.Visible = true;
//ExcelApp.ScreenUpdating = true;
ExcelSheet.Cells[1, 1] = "总件数：" + Result.Count.ToString();
ExcelSheet.Cells[2, 1] = "发票类型：" + fapiaoleixingin;
ExcelSheet.Cells[3, 1] = "归档人：" + guidangren;
ExcelSheet.Cells[4, 1] = "档号：" + danghao;
ExcelSheet.Cells[5, 1] = "归档时间：" + guidangshijian;
//直接打印
//ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
//ExcelSheet.PageSetup.PaperSize =Excel.XlPaperSize.xlPaperA4;
//ExcelBook.PrintOut();
#region 写入文件
ProcessLogger.Fatal("8950 Output Initial" + DateTime.Now.ToString());
ExcelApp.DisplayAlerts = false;
ExcelApp.ScreenUpdating = true;
ExcelBook.SaveAs(ResaveName, missingValue, missingValue, missingValue, missingValue, missingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missingValue, missingValue, missingValue, missingValue, missingValue);
ExcelApp.DisplayAlerts = false;
ExcelBook.Close(false, missingValue, missingValue);
ExcelBook = null;
#endregion
//转换成PDF 文件
//if (XLSConvertToPDF(ResaveName, ResaveName.Replace("xlsx", "pdf")))
//{
// // FilePath.Add(ResaveName.Replace("xlsx", "pdf"));
//}
return(ResaveName);
#endregion
#endregion
}
#endregion
#region 异常处理
catch (Exception ex)
{
ExcelApp.DisplayAlerts = false;
ExcelApp.Quit();
ExcelBook = null;
ExcelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
throw ex;
}
#endregion
#region Finally垃圾回收
finally
{
//ExcelBook.Close(false, missingValue, missingValue);
//ExcelBook = null;
ExcelApp.DisplayAlerts = true;
ExcelApp.Quit();
clsKeyMyExcelProcess.Kill(ExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
public void ExportDataSetToExcel(DataSet ds, string strPath)
{
string ruta = "c:\\DATA";
if (!Directory.Exists(ruta))
{
Directory.CreateDirectory(ruta);
}
int CONTADOR = 0;
int inHeaderLength = 3, inColumn = 0, inRow = 0;
System.Reflection.Missing Default = System.Reflection.Missing.Value;
//Create Excel File
//strPath += @"\Excel" + DateTime.Now.ToString().Replace(':', '-') + ".xlsx";
OfficeExcel.Application excelApp = new OfficeExcel.Application();
OfficeExcel.Workbook excelWorkBook = excelApp.Workbooks.Add(1);
foreach (DataTable dtbl in ds.Tables)
{
//Create Excel WorkSheet
OfficeExcel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
excelWorkSheet.Name = dtbl.TableName; //Name worksheet
//Write Column Name
for (int i = 0; i < dtbl.Columns.Count; i++)
{
excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i].ColumnName.ToUpper();
}
//Write Rows
for (int m = 0; m < dtbl.Rows.Count; m++)
{
for (int n = 0; n < dtbl.Columns.Count; n++)
{
inColumn = n + 1;
inRow = inHeaderLength + 2 + m;
excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n].ToString();
if (m % 2 == 0)
{
excelWorkSheet.get_Range("A" + inRow.ToString(), "AK" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6");
}
}
CONTADOR = CONTADOR + 1;
Console.WriteLine("Filas N°" + Convert.ToString(CONTADOR));
mensajeexport = "Filas N°" + Convert.ToString(CONTADOR);
}
//Excel Header
OfficeExcel.Range cellRang = excelWorkSheet.get_Range("A1", "AK3");
cellRang.Merge(false);
cellRang.Interior.Color = System.Drawing.Color.White;
cellRang.Font.Color = System.Drawing.Color.Gray;
cellRang.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
cellRang.VerticalAlignment = OfficeExcel.XlVAlign.xlVAlignCenter;
cellRang.Font.Size = 26;
excelWorkSheet.Cells[1, 1] = "DETALLE DE PRESUPUESTO POR LOTE";
//Style table column names
cellRang = excelWorkSheet.get_Range("A4", "AK4");
cellRang.Font.Bold = true;
cellRang.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
//excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignRight;
//Formate price column
//excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00";
//Auto fit columns
excelWorkSheet.Columns.AutoFit();
}
//Delete First Page
excelApp.DisplayAlerts = false;
Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
lastWorkSheet.Delete();
excelApp.DisplayAlerts = true;
//Set Defualt Page
(excelWorkBook.Sheets[1] as OfficeExcel._Worksheet).Activate();
excelWorkBook.SaveAs(strPath);
excelWorkBook.Close();
excelApp.Quit();
ProcessStartInfo startInfo = new ProcessStartInfo();
startInfo.FileName = "EXCEL.EXE";
startInfo.Arguments = strPath;
Process.Start(startInfo);
MessageBox.Show("Se genero el Arvicho en : \n As " + strPath);
}
