/// <summary>
/// 关闭Excel
/// </summary>
public void Close()
{
myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
myExcel.Quit();
myWorkBook = null;
myExcel = null;
GC.Collect();
}
private void QuitExcel(ref Excel.Application eps, ref Workbook wkCur)
{
try
{
if (wkCur != null)
{
wkCur.Save();
wkCur.Close(false);
}
//无论是否成功，都退出
if (eps != null)
{
PrintLog("Convert Excel file To PDF end");
eps.Quit();
}
}
catch (Exception ex)
{
PrintLog(ex.Message.ToString());
//m_nConvertStatus = EErrorType.OTP_EXCEPTION_FAILED;
}
finally
{
eps = null;
wkCur = null;
}
}
/// <summary>
/// Excel文件打印预览
/// </summary>
/// <param name="ExcelFile">文件路径包含文件名称</param>
public static void ExcelPreview(string ExcelFile)
{
try
{
Excel.Application xlsApp = new Excel.Application();
if (xlsApp == null)
{
throw new Exception("无法创建Excel对象，可能您的计算机未安装Excel");
}
Excel.Workbooks xlsWbs = xlsApp.Workbooks;
Excel.Workbook xlsWb = xlsWbs.Open(
ExcelFile, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value);
xlsApp.Visible = true;
xlsWb.PrintPreview(false);
xlsWb = null;
xlsApp.Quit();
xlsApp = null;
}
catch (Exception ex)
{
throw new Exception(ex.Message + "Excel文件操作失败");
}
}
private void btnTest2_Click(object sender, EventArgs e)
{
Excel.Application m_excel;
Excel.Workbook m_workbook;
m_excel = new Excel.Application();//对象实例化
m_workbook = m_excel.Workbooks.Open("D:\\test.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//m_workbook = m_excel.Workbooks.Open("D:\\test.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
m_excel.Visible = true;//显示Excel内容(为false时，excel.exe进程在后台运行，未找到关闭方法)
Range rng;
object obj;
String str;
rng = (Excel.Range)m_excel.Cells[2, 2];
obj = rng.Value2;
System.Diagnostics.Debug.WriteLine(obj.ToString()); //输出调试信息
str = rng.NumberFormatLocal;
System.Diagnostics.Debug.WriteLine(str); //输出调试信息
this.txtTest2.Text = obj.ToString();
m_excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框
m_excel.AlertBeforeOverwriting = false;
m_excel.Workbooks.Close();
//object oV = System.Reflection.Missing.Value; //反复用到
//System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(m_excel);
//GC.Collect();
m_excel.Quit();
}
public void CreateExcel(IDictionary <string, System.Data.DataTable> iDicDT, string FilePath, bool Visible)
{
Excel.Application excel = new Excel.Application(); //引用Excel对象
Excel.Workbook book = excel.Application.Workbooks.Add(System.Reflection.Missing.Value); //引用
Excel.Worksheet sheet3 = (Excel.Worksheet)book.Worksheets["Sheet3"];//选中
object nothing = System.Reflection.Missing.Value;
book.Worksheets.Add(nothing, sheet3, iDicDT.Count - 3, nothing);
int index = 0;
foreach (KeyValuePair <string, System.Data.DataTable> val in iDicDT)
{
index++;
Excel.Worksheet tempExcel = (Excel.Worksheet)book.Worksheets["Sheet" + index];//选中
tempExcel.Name = val.Key;
System.Data.DataTable tempdt = val.Value;
for (int i = 0; i < tempdt.Columns.Count; i++)
{
tempExcel.Cells[1, i + 1] = tempdt.Columns[i].ColumnName;
}
for (int i = 0; i < tempdt.Rows.Count; i++)
{
for (int j = 0; j < tempdt.Columns.Count; j++)
{
try
{
tempExcel.Cells[i + 2, j + 1] = tempdt.Rows[i][j].ToString();
}
catch { }
}
}
}
//保存
if (!string.IsNullOrEmpty(FilePath))
{
SaveExcelFile(excel, FilePath);
}
//打开显示
excel.Visible = Visible;
excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}
//关闭Excel
public void CloseExcel()
{
wb.Close();
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
//关闭一个Excel对象，销毁对象
public void Close()
{
//wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
private void createExcel()
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet newSheet = null;
try
{
FileInfo file = new FileInfo(fileLoc);
if (file.Exists)
{
excelApp = new Excel.Application();
workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",
false, XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
sheets = workbook.Sheets;
//check columns exist
foreach (Excel.Worksheet sheet in sheets)
{
Console.WriteLine(sheet.Name);
sheet.Select(Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
}
newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);
newSheet.Name = "My New Sheet";
newSheet.Cells[1, 1] = "BOO!";
workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();
}
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
newSheet = null;
sheets = null;
workbook = null;
excelApp = null;
GC.Collect();
}
}
/// <summary>
/// 直接创建Excel表
/// </summary>
/// <param name="dtSource">数据源DataTable</param>
/// <param name="filePath">保存路径</param>
/// <param name="drTitle">标题DataRow</param>
/// <param name="TableName">Excel表头</param>
/// <returns></returns>
public static bool Save(System.Data.DataTable dtSource, string filePath, System.Data.DataRow drTitle, string TableName)
{
Excel.Application app = new Excel.Application();//EXCEL实例
try
{
app.Visible = false;
Workbook wBook = app.Workbooks.Add(true);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (dtSource.Rows.Count > 0)
{
int row = dtSource.Rows.Count + 1;
int col = dtSource.Columns.Count + 1;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
string str = dtSource.Rows[i][j].ToString();
wSheet.Cells[i + 3, j + 1] = str;
}
}
}
int size = dtSource.Columns.Count;
for (int i = 0; i < size; i++)
{
wSheet.Cells[2, 1 + i] = drTitle[i].ToString();
}
wSheet.get_Range(wSheet.Cells[1, 0], wSheet.Cells[1, size]).Merge(Type.Missing);
wSheet.Cells[1, 1] = TableName;
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wBook.Save();
//保存excel文件
app.Save(filePath);
app.SaveWorkspace(filePath);
app.Quit();
app = null;
return(true);
}
catch
{
return(false);
}
finally
{
}
}
/// <summary>
/// 关闭当前Excel文档
/// </summary>
public void CloseControl()
{
if (workbook != null)
{
deactivateevents = true;
object dummy = null;
object dummyFalse = (object)false;
((Excel._Workbook)workbook).Close(dummy, dummy, dummy);
xlApp.Quit();
Kill();
deactivateevents = false;
}
}
//字母-〉列号 Z-〉26
//public void SaveAsExcel()
//{
// if (xlsSaveFileName == "")
// {
// throw new Exception("未指定要保存的文件名");
// }
// try
// {
// xlsApp.Visible = true;
// xlsWb.PrintPreview(false);
// xlsWb = null;
// xlsApp.Quit();
// xlsApp = null;
// //xlsWs.SaveAs(xlsSaveFileName, Excel.XlFileFormat.xlExcel7,
// // Type.Missing, Type.Missing,
// // Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
// // Type.Missing, Type.Missing);
// }
// catch (Exception e)
// {
// CloseExcelApplication();
// throw new Exception(e.Message);
// }
//}
public void SaveAsExcel(bool IsView)
{
try
{
if (File.Exists(xlsSaveFileName))
{
File.Delete(xlsSaveFileName);
}
if (IsView)
{
xlsApp.Visible = true;
xlsWb.PrintPreview(false);
}
xlsWb.Close(true, xlsSaveFileName, Type.Missing);
xlsWb = null;
xlsApp.Quit();
xlsApp = null;
}
catch (Exception e)
{
CloseExcelApplication();
throw new Exception(e.Message);
}
}
public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)
{
Excel.Application app = new Excel.Application();//对象实例
try
{
app.Visible = true;
Workbook wBook = app.Workbooks.Add(true);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (excelTable.Rows.Count > 0)
{
int row = 0;
row = excelTable.Rows.Count;
int col = excelTable.Columns.Count;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
string str = excelTable.Rows[i][j].ToString();
wSheet.Cells[i + 2, j + 1] = str;
}
}
}
int size = excelTable.Columns.Count;
for (int i = 0; i < size; i++)
{
wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
}
app.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框
app.AlertBeforeOverwriting = false;
wBook.SaveAs(filePath); //保存工作簿(当不指定FileFormat时，保存为默认格式，新建的文档默认格式为xlsx，即使filePath后缀为xls，内部数据格式仍为xlsx)
wBook.Close(); //关闭工作簿
//app.Save(filePath);//保存excel文件
//app.SaveWorkspace(filePath);
app.Quit();
return(true);
}
catch (Exception err)
{
MessageBox.Show("导出Excel出错！错误原因：" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
return(false);
}
}
/// <summary>
/// 关闭当前Excel文档
/// </summary>
public void CloseControl()
{
if (workbook != null)
{
deactivateevents = true;
object dummy = null;
object dummyFalse = (object)false;
xlApp.WorkbookBeforeClose -= new Excel.AppEvents_WorkbookBeforeCloseEventHandler(OnClose);
xlApp.NewWorkbook -= new Excel.AppEvents_NewWorkbookEventHandler(OnNewXls);
xlApp.WorkbookOpen -= new Excel.AppEvents_WorkbookOpenEventHandler(OnOpenXls);
((Excel._Workbook)workbook).Close(dummyFalse, dummy, dummy);
xlApp.Quit();
Kill();
deactivateevents = false;
workbook = null;
}
}
//Exports a DataView to Excel. The following steps are carried out
//in order to export the DataView to Excel
//Create Excel Objects And Open Template File
//Select All Used Cells
//Create Headers/Footers
//Set Status Finished
//Save workbook & Tidy up all objects
//@param path : The path to save/open the EXCEL file to/from
public void UseTemplate(string path, string templatePath, string[,] myTemplateValues)
{
try
{
this.myTemplateValues = myTemplateValues;
//create new EXCEL application
EXL = new Excel.ApplicationClass();
//Yes file exists, so open the file
workbook = EXL.Workbooks.Open(templatePath,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
//get the workbook sheets collection
sheets = workbook.Sheets;
//get the new sheet
worksheet = (Worksheet)sheets.get_Item(1);
//Change its name to that requested
worksheet.Name = "ATemplate";
//Fills the Excel Template File Selected With A 2D Test Array
fillTemplate_WithTestValues();
//Select all used cells within current worksheet
SelectAllUsedCells();
try
{
workbook.Close(true, path, Type.Missing);
EXL.UserControl = false;
EXL.Quit();
EXL = null;
//kill the EXCEL process as a safety measure
killExcel();
// Show that processing is finished
ProgressEventArgs pe = new ProgressEventArgs(100);
OnProgressChange(pe);
MessageBox.Show("Finished adding test values to Template", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (COMException)
{
Console.WriteLine("User closed Excel manually, so we don't have to do that");
}
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
}
public static System.Data.DataTable LoadDataFromExcel(string filePath)
{
Excel.Application m_excel;
Excel.Workbook m_workbook;
Excel.Worksheet worksheet;
m_excel = new Excel.Application();//对象实例化
m_workbook = m_excel.Workbooks.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
worksheet = (Worksheet)m_workbook.Worksheets[1];
m_excel.Visible = true;//显示Excel内容(为false时，excel.exe进程在后台运行，未找到关闭方法)
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;
Excel.Range range1;
System.Data.DataTable dt = new System.Data.DataTable();
for (int i = 0; i < colCount; i++)
{
//range1 = worksheet.get_Range(worksheet.Cells[1, i + 1], worksheet.Cells[1, i + 1]);
range1 = (Excel.Range)m_excel.Cells[1, i + 1];
dt.Columns.Add(range1.Value2.ToString());
}
for (int j = 1; j < rowCount; j++)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < colCount; i++)
{
//range1 = worksheet.get_Range(worksheet.Cells[j + 1, i + 1], worksheet.Cells[j + 1, i + 1]);
range1 = (Excel.Range)m_excel.Cells[j + 1, i + 1];
dr[i] = range1.Value2.ToString();
}
dt.Rows.Add(dr);
}
m_excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框
m_excel.AlertBeforeOverwriting = false;
m_excel.Workbooks.Close();
m_excel.Quit();
return(dt);
}
public static bool SaveDataToExcel(System.Data.DataTable dt, string filePath) //将System.Data.DataTable内容存储到指定路径的Excel中(存储默认格式xlsx)
{
Excel.Application excel = new Excel.Application(); //对象实例化;
try
{
excel.Visible = false;//不显示Excel内容
Workbook workbook = excel.Workbooks.Add(true);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
if (dt.Rows.Count > 0)
{
int row = row = dt.Rows.Count;
int col = dt.Columns.Count;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
string str = dt.Rows[i][j].ToString();
worksheet.Cells[i + 2, j + 1] = str;
}
}
}
int size = dt.Columns.Count;
for (int i = 0; i < size; i++)
{
worksheet.Cells[1, 1 + i] = dt.Columns[i].ColumnName;
}
excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框
excel.AlertBeforeOverwriting = false;
workbook.SaveAs(filePath); //保存工作簿(当不指定FileFormat时，保存为默认格式，新建的文档默认格式为xlsx，即使filePath后缀为xls，内部数据格式仍为xlsx)
workbook.Close(); //关闭工作簿
excel.Quit(); //退出Excel程序
return(true);
}
catch (Exception ex)//导出Excel出错
{
//throw new Exception(ex.Message);
//MessageBox.Show(ex.Message);
return(false);
}
}
public static System.Data.DataTable LoadDataFromExcel(string filePath) //从指定文件路径中的Excel读取所有内容到System.Data.DataTable(xls和xlsx格式均可以打开)
{
Excel.Application excel = new Excel.Application(); //对象实例化;
try
{
Excel.Workbook workbook = excel.Workbooks.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
excel.Visible = false; //不显示Excel内容
int rowCount = worksheet.UsedRange.Rows.Count; //定义Excel文件中的行数
int colCount = worksheet.UsedRange.Columns.Count; //定义Excel文件中的列数
Excel.Range range;
System.Data.DataTable dt = new System.Data.DataTable();
for (int i = 0; i < colCount; i++)//循环列
{
range = (Excel.Range)excel.Cells[1, i + 1];
dt.Columns.Add(range.Value2.ToString());
}
for (int j = 1; j < rowCount; j++)//循环行
{
DataRow dr = dt.NewRow();
for (int i = 0; i < colCount; i++)
{
range = (Excel.Range)excel.Cells[j + 1, i + 1];
dr[i] = range.Value2.ToString();
}
dt.Rows.Add(dr);
}
excel.DisplayAlerts = false; //设置禁止弹出保存和覆盖的询问提示框
excel.AlertBeforeOverwriting = false;
excel.Workbooks.Close(); //关闭工作簿
excel.Quit(); //退出Excel程序
return(dt);
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
//MessageBox.Show(ex.Message);
return(null);
}
}
public static void Main()
{
Excel.Application ex = new Excel.Application();
ex.Visible = true;
Excel.Workbook wb = ex.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Range rg = ex.ActiveCell;
rg.set_Item(1, 1, "Competência");
rg.set_Item(1, 2, "Nota");
rg.set_Item(2, 1, "C++");
rg.set_Item(2, 2, "5");
rg.set_Item(3, 1, "UML");
rg.set_Item(3, 2, "6");
rg.set_Item(4, 1, "OOP");
rg.set_Item(4, 2, "5");
rg.set_Item(5, 1, "DB");
rg.set_Item(5, 2, "9");
rg.set_Item(6, 1, ".NET");
rg.set_Item(6, 2, "4");
rg.set_Item(7, 1, "XML");
rg.set_Item(7, 2, "4");
rg.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic3, true, true, true, true, true, true);
Excel.Chart ch = (Excel.Chart)ex.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
ch.ChartType = Excel.XlChartType.xlRadarFilled;
Excel.Worksheet ws = (Excel.Worksheet)ex.Worksheets["Plan1"];
Excel.Range lrg = ws.get_Range("A1", "B7");
ch.SetSourceData(lrg, Excel.XlRowCol.xlColumns);
ch.Location(Excel.XlChartLocation.xlLocationAsObject, "Plan1");
ex.ActiveChart.HasLegend = false;
ex.ActiveChart.HasTitle = true;
ex.ActiveChart.ChartTitle.Text = "Gráfico de Competência";
Console.Write("Pressionar ENTER para sair do EXCEL"); Console.ReadLine();
ex.Quit();
}
/// <summary>
/// Excel转PDF
/// </summary>
/// <param name="wpsFilename">文件名</param>
/// <param name="pdfFilename"></param>
public void ExcelToPdf(string wpsFilename, string pdfFilename = null)
{
Excel.Application eps = new Excel.Application();
try
{
if (wpsFilename == null)
{
throw new ArgumentNullException("wpsFilename");
}
//保存路径为空时，保存在原始文件目录
if (pdfFilename == null)
{
pdfFilename = Path.ChangeExtension(wpsFilename, "pdf");
}
//忽略警告提示
eps.Visible = false;
eps.DisplayAlerts = false;
eps.Workbooks.Open(wpsFilename);
eps.ActiveWorkbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, pdfFilename, null, null, true);
eps.ActiveWorkbook.Close();
}
catch (Exception ex)
{
}
finally
{
//无论是否成功，都退出
if (eps != null)
{
eps.Quit();
//强制关闭所有et进程
System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process prtemp in process)
{
prtemp.Kill();
}
}
}
}
private void button1_Click(object sender, EventArgs e)
{
saveFileDialog1.Title = "序列号生成工具----百思威科技";
saveFileDialog1.Filter = "Excel(*.xls)|*.xls";
saveFileDialog1.FileName = string.Format("序列号_{0}", DateTime.Now.ToString("yyyyMMdd"));
DialogResult result = saveFileDialog1.ShowDialog();
Excel._Application xlapp = new Excel.Application();
Excel.Workbook xlbook = xlapp.Workbooks.Add(true);
Excel.Worksheet xlsheet = (Excel.Worksheet)xlbook.Worksheets[1];
int RowCount = Convert.ToInt32(len.Text);
int RowIndex = 0;
for (int i = 0; i < RowCount; i++)
{
RowIndex++;
xlsheet.Cells[RowIndex, 1] = "SN:" + (Convert.ToInt32(sarial.Text) + i).ToString();
}
xlbook.Saved = true;
xlbook.SaveCopyAs(saveFileDialog1.FileName);
xlapp.Quit();
MessageBox.Show("导出成功！", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public static void Main(string[] argv)
{
string command = argv[0];
ArrayList filenames = new ArrayList();
for (int i = 1; i < argv.Length; i++)
{
string excelname = argv[i];
if (!System.IO.Path.IsPathRooted(excelname))
{
string cwd = System.Environment.CurrentDirectory;
excelname = System.IO.Path.Combine(cwd, excelname);
}
filenames.Add(excelname);
}
Excel.Application app = new Excel.Application();
doit(app, filenames, command);
app.Quit();
}
public void ExportData(DataGridView srcDgv, string fileName) //导出数据,传入一个datagridview和一个文件路径
{
string type = fileName.Substring(fileName.IndexOf(".") + 1); //获得数据类型
if (type.Equals("xls", StringComparison.CurrentCultureIgnoreCase)) //Excel文档
{
Excel.Application excel = new Excel.Application();
try
{
excel.DisplayAlerts = false;
excel.Workbooks.Add(true);
excel.Visible = false;
for (int i = 0; i < srcDgv.Columns.Count; i++)//设置标题
{
excel.Cells[2, i + 1] = srcDgv.Columns[i].HeaderText;
}
for (int i = 0; i < srcDgv.Rows.Count; i++)//填充数据
{
for (int j = 0; j < srcDgv.Columns.Count; j++)
{
if (srcDgv[j, i].ValueType.ToString() == "System.Byte[]")
{
excel.Cells[i + 3, j + 1] = "System.Byte[]";
}
else
{
excel.Cells[i + 3, j + 1] = srcDgv[j, i].Value;
}
}
}
excel.Workbooks[1].SaveCopyAs(fileName);//保存
}
finally
{
excel.Quit();
}
return;
}
//保存Word文件
if (type.Equals("doc", StringComparison.CurrentCultureIgnoreCase))
{
object path = fileName;
Object none = System.Reflection.Missing.Value;
Word.Application wordApp = new Word.Application();
Word.Document document = wordApp.Documents.Add(ref none, ref none, ref none, ref none);
//建立表格
Word.Table table = document.Tables.Add(document.Paragraphs.Last.Range, srcDgv.Rows.Count + 1, srcDgv.Columns.Count, ref none, ref none);
try
{
for (int i = 0; i < srcDgv.Columns.Count; i++)//设置标题
{
table.Cell(1, i + 1).Range.Text = srcDgv.Columns[i].HeaderText;
}
for (int i = 0; i < srcDgv.Rows.Count; i++)//填充数据
{
for (int j = 0; j < srcDgv.Columns.Count; j++)
{
string a = srcDgv[j, i].ValueType.ToString();
if (a == "System.Byte[]")
{
PictureBox pp = new PictureBox();
byte[] pic = (byte[])(srcDgv[j, i].Value); //将数据库中的图片转换成二进制流
MemoryStream ms = new MemoryStream(pic); //将字节数组存入到二进制流中
pp.Image = Image.FromStream(ms); //二进制流Image控件中显示
pp.Image.Save(@"C:\22.bmp"); //将图片存入到指定的路径
object aaa = table.Cell(i + 2, j + 1).Range;
wordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;
wordApp.Selection.InlineShapes.AddPicture(@"C:\22.bmp", ref none, ref none, ref aaa);
pp.Dispose();
}
else
{
table.Cell(i + 2, j + 1).Range.Text = srcDgv[j, i].Value.ToString();
}
}
}
document.SaveAs(ref path, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none, ref none);
document.Close(ref none, ref none, ref none);
if (File.Exists(@"C:\22.bmp"))
{
File.Delete(@"C:\22.bmp");
}
}
finally
{
wordApp.Quit(ref none, ref none, ref none);
}
}
}
/// <summary>
/// 读取并保存Excel中的信息到数据库中
/// </summary>
private void ReadAndSaveExcelInfo()
{
//Workbook wBook = excel.Workbooks.Add(true);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
//Worksheet wSheet = (Excel._Worksheet)wBook.ActiveSheet;
//Worksheet worksheet = (Excel.Worksheet)wBook.ActiveSheet;
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
string strTmpTable, strTmpField, strTmpValue;
string val1 = "";
string val2 = "";
string val3 = "";
string val4 = "";
string val5 = "";
string val6 = "";
string val7 = "";
string val8 = "";
string val9 = "";
string val10 = "";
string val11 = "";
int num = 0;
Excel.Range range = worksheet.get_Range("A2", "P1000");//A2、O1000表示到Excel从第A列的第2行到第O列的第1000-1行 A2、P65535表示到Excel从第A列的第2行到第P列的第65535-1行
System.Array values = (System.Array)range.Formula;
num = values.GetLength(0);
for (int i = 1; i <= num; i++)
{
if (values.GetValue(i, 1).ToString().Trim() == "" || values.GetValue(i, 2).ToString().Trim() == "")
{
break;
}
else
{
val1 = values.GetValue(i, 1).ToString().Trim(); //记帐日期
val2 = values.GetValue(i, 2).ToString().Trim(); //订单号
val3 = values.GetValue(i, 3).ToString().Trim(); //订单行项目号
val4 = values.GetValue(i, 4).ToString().Trim(); //物料编码
val5 = values.GetValue(i, 5).ToString().Trim(); //物料名称
val6 = values.GetValue(i, 6).ToString().Trim(); //批号
val7 = values.GetValue(i, 7).ToString().Trim(); //收货数量
val8 = values.GetValue(i, 8).ToString().Trim(); //计量单位
val9 = values.GetValue(i, 9).ToString().Trim(); //工厂
val10 = values.GetValue(i, 10).ToString().Trim(); //库存地点
val11 = values.GetValue(i, 11).ToString().Trim(); //抬头文本
string strLRR = CoreFS.SA06.CoreUserInfo.UserInfo.GetUserName();
string strLRD = CoreFS.SA06.CoreUserInfo.UserInfo.GetDepartment();
strTmpTable = "DT_SAP261";
strTmpField = "FS_WEIGHTNO,FS_ACCOUNTDATE,FS_PRODUCTNO,FS_ITEMNO,FS_MATERIAL,FS_MATERIALNAME,FS_STOVENO,"
+ "FN_NETWEIGHT,FS_PLANT,FS_SAPSTORE,FS_HEADER,FS_WEIGHTTYPE,FS_DRDW,FS_AUDITOR";
strTmpValue = "'" + Guid.NewGuid().ToString() + "','" + val1 + "','" + val2 + "','" + val3 + "','"
+ val4 + "','" + val5 + "','" + val6 + "'," + val7 + ",'" + val9 + "','" + val10
+ "','" + val11 + "'," + "'261',";
/* if ((CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC1001") || (CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC1002"))
* {
* strTmpValue += "'棒线厂一作',";
* }
* if (CoreFS.SA06.CoreUserInfo.UserInfo.GetRole() == "BXC2001")
* {
* strTmpValue += "'棒线厂二作',";
* }*/
strTmpValue += "'" + strLRD + "','" + strLRR + "'";
CoreClientParam ccp = new CoreClientParam();
ccp.ServerName = "ygjzjl.base.QueryData";
ccp.MethodName = "insertDataInfo";
ccp.ServerParams = new object[] { strTmpTable, strTmpField, strTmpValue };
try
{
this.ExecuteNonQuery(ccp, CoreInvokeType.Internal);
}
catch (Exception ex1)
{
lstHint1.Items.Add(ex1.Message + "第" + i.ToString() + "行信息导入成功！");
}
}
}
app.Quit();
app = null;
lstHint1.Items.Add("信息导入成功！");
System.Diagnostics.Process[] myProcesses;
myProcesses = System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process myProcess in myProcesses)
{
if (myProcess.ProcessName == "EXCEL")
{
myProcess.Kill();
}
}
}
/// <summary>
/// 把DataTable中的数据导出到Excel
/// </summary>
/// <param name="dt"></param>
public static void DataTableToExcel(System.Data.DataTable srcDt, string savename)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt = srcDt;
if (dt == null)
{
return;
}
string saveFileName = "";
bool fileSaved = false;
/*SaveFileDialog saveDialog = new SaveFileDialog();
* saveDialog.DefaultExt = "xlsx";
* saveDialog.Filter = "Excel文件|*.xlsx";
* saveDialog.FileName = "导出文件";
* saveDialog.ShowDialog();
* saveFileName = saveDialog.FileName;
* if (saveFileName.IndexOf(":") < 0) return; //被点了取消 */
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).Font.ColorIndex = 9;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).ColumnWidth = 40;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 1]).RowHeight = 50;
//worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 6]).Interior.ColorIndex = 6;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).WrapText = true;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
for (int i = 2; i < 12; i++)
{
if (dt.Rows[i - 2][3].ToString() == "E")
{
worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 3;
}
if (dt.Rows[i - 2][3].ToString() == "W")
{
worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 6;
}
}
//写入字段
worksheet.Cells[1, 1] = "参数名称";
worksheet.Cells[1, 2] = "参数值";
worksheet.Cells[1, 3] = "推荐值";
worksheet.Cells[1, 4] = "报警值";
worksheet.Cells[1, 5] = "详情";
worksheet.Cells[1, 6] = "参数类型";
worksheet.Cells[1, 7] = "重要级别";
worksheet.Cells[1, 8] = "历史变化";
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
//worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
/*string md5 = "";
* for (int r = 0; r < 5; r++)
* {
* for (int i = 0; i < 8; i++)
* {
* md5 += dt.Rows[r][i].ToString();
* worksheet.Cells[2, 9] = md5;
* }
*
* }
*/
//worksheet.Cells.Width = 39;
//if (saveFileName != "")
//{
try
{
workbook.Saved = true;
saveFileName = savename;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
MessageBox.Show("导出完成！");
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
//}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
}
/// <summary>
/// 把DataTable中的数据导出到Excel
/// </summary>
/// <param name="dt"></param>
public static void DataTableToExcel(System.Data.DataTable srcDt,string savename)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt = srcDt;
if (dt == null) return;
string saveFileName = "";
bool fileSaved = false;
/*SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件|*.xlsx";
saveDialog.FileName = "导出文件";
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消 */
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).Font.ColorIndex = 9;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]).ColumnWidth = 40;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 1]).RowHeight = 50;
//worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 6]).Interior.ColorIndex = 6;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).WrapText = true;
worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[11, 8]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
//worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
for (int i = 2; i < 12; i++)
{
if (dt.Rows[i-2][3].ToString() == "E")
{
worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 3;
}
if (dt.Rows[i - 2][3].ToString() == "W")
{
worksheet.get_Range(worksheet.Cells[i, 1], worksheet.Cells[i, 8]).Interior.ColorIndex = 6;
}
}
//写入字段
worksheet.Cells[1, 1] = "参数名称";
worksheet.Cells[1, 2] = "参数值";
worksheet.Cells[1, 3] = "推荐值";
worksheet.Cells[1, 4] = "报警值";
worksheet.Cells[1, 5] = "详情";
worksheet.Cells[1, 6] = "参数类型";
worksheet.Cells[1, 7] = "重要级别";
worksheet.Cells[1, 8] = "历史变化";
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
//worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
/*string md5 = "";
for (int r = 0; r < 5; r++)
{
for (int i = 0; i < 8; i++)
{
md5 += dt.Rows[r][i].ToString();
worksheet.Cells[2, 9] = md5;
}
}
*/
//worksheet.Cells.Width = 39;
//if (saveFileName != "")
//{
try
{
workbook.Saved = true;
saveFileName = savename;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
MessageBox.Show("导出完成！");
}
catch (Exception ex)
{
fileSaved = false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
//}
//else
//{
// fileSaved = false;
//}
xlApp.Quit();
GC.Collect();//强行销毁
if (fileSaved && System.IO.File.Exists(saveFileName))
{
System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
}
public List <StatisticalGraph> BuildGraphList(string file, bool gif)
{
List <StatisticalGraph> sg_collection = new List <StatisticalGraph>();
Excel.Workbook xl_workbook;
try
{
xl_workbook = this.xl_app.Workbooks.Open(file, 0, false, 5,
"", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false,
false);
} catch
{
return(null); /*returning the null list; handled in iGraphMain*/
}
/** Loop over every worksheet and every ChartObject in the worksheet.
* The ChartObject object acts as a container for a Chart object.
* Properties and methods for the ChartObject object control the
* appearance and size of the embedded chart on the worksheet,
* see: http://msdn.microsoft.com/en-us/library/aa173258(office.11).aspx
*/
foreach (Worksheet sheet in xl_workbook.Worksheets)
{
foreach (ChartObject curr_chart in
(ChartObjects)sheet.ChartObjects(Type.Missing))
{
sg = new StatisticalGraph();
problem = false;
Chart c = curr_chart.Chart; // c is the chart in ChartObject
if ((int)c.ChartType == 68 || (int)c.ChartType == 71)
{
log.Warn("iGraph does not process pie charts... Skipping.");
continue;
}
sg.Prologue = new GraphPrologue(file,
"MS Excel",
curr_chart.Index,
sheet.Index,
(int)c.ChartType,
curr_chart.Height,
curr_chart.Width);
log.Debug("Graph ID: " + sg.Prologue.GetGraphName());
sg.PlotArea = GetXLPlotArea(c);
sg.Textboxes = GetXLTextBoxes(c);
sg.Series = GetXLSeries(c);
sg.MainTitle = GetXLChartTitle(c);
sg.ValueAxis = GetXLValueAxis(c);
sg.CategoryAxis = GetXLCategoryAxis(c);
// add to the returned statgraph collection
if (!problem)
{
sg_collection.Add(sg);
// if flagged, export graph as gif in same argument directory
if (gif)
{
c.Export(sg.Prologue.GetGifName(), "GIF", false);
log.Info("Saved " + sg.Prologue.GetGifName());
}
}
}
}
// Excel has to be closed.
xl_app.Workbooks.Close();
xl_app.Quit();
// the return value if all goes well...
return(sg_collection);
}
private void button1_Click(object sender, EventArgs e)
{
string strSum = "", strColName, strImmediateOneUp = "", strImmediateTwoUp = "";
int NumRows = 1000;
int NumColumns = 150;
int startsum = 0;
int currow = 0;
bool firstTimeSum = true;
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet = null;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
for (int row = 0; row < NumRows; row++)
{
for (int col = 0; col < NumColumns; col++)
{
if (row < 2)
{
xlWorkSheet.Cells[row + 1, col + 1] = new Random().Next(1, NumRows).ToString();
}
else
{
if (firstTimeSum)
{
if (row - currow == 2)
{
currow = row;
startsum = 0;
firstTimeSum = false;
}
else
{
startsum = 1;
}
}
else
{
if (row - currow == 3)
{
currow = row;
startsum = 0;
}
}
if (startsum == 0)
{
strColName = GenerateColumnText(col);
strImmediateOneUp = strColName + ((row + 1) - 1).ToString();
strImmediateTwoUp = strColName + ((row + 1) - 2).ToString();
strSum = string.Format("=SUM({0}:{1})", strImmediateTwoUp, strImmediateOneUp);
xlWorkSheet.Cells[row + 1, col + 1] = strSum;
}
else
{
xlWorkSheet.Cells[row + 1, col + 1] = new Random().Next(1, NumRows).ToString();
}
}
}
startsum = 1;
}
if (System.IO.File.Exists(@"d:\pop.xls"))
{
System.IO.File.Delete(@"d:\pop.xls");
}
xlWorkBook.SaveAs(@"d:\pop.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue,
misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
xlApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();
stopwatch.Stop();
TimeSpan timeSpan = stopwatch.Elapsed;
MessageBox.Show(string.Format("Time elapsed: {0}h {1}m {2}s {3}ms", timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds, timeSpan.Milliseconds));
}
public void reportForm()
{
int rowCount = dataGridView1.Rows.Count; //获取dataGridView1的行数
int columnsCount = dataGridView1.Columns.Count; //获取dataGridView1的列数
//判断是否存在值
if (rowCount == 0)
{
MessageBox.Show("没有数据可供导出。。。", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
//定义个变量接收文件名字
string fileName = string.Empty;
//new 一个对象用于提示用户选择保存文件位置
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "导出Excel (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0; //筛选器的索引
saveFileDialog.RestoreDirectory = true; //对话框关闭前是否还原当前目录
saveFileDialog.CreatePrompt = true; //指定文件不存在，提示用户是否允许创建该文件
saveFileDialog.Title = "导出文件保存路径"; //设置对话框标题
//当前时间
DateTime now = DateTime.Now;
saveFileDialog.FileName = now.Year.ToString().PadLeft(2)
+ now.Month.ToString().PadLeft(2, '0')
+ now.Day.ToString().PadLeft(2, '0') + "-"
+ now.Hour.ToString().PadLeft(2, '0')
+ now.Minute.ToString().PadLeft(2, '0')
+ now.Second.ToString().PadLeft(2, '0');
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
fileName = saveFileDialog.FileName;
Missing miss = Missing.Value;
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
if (excel == null)
{
MessageBox.Show("Excel无法启动", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet worksheet = (Worksheet)workbook.Worksheets[1]; //取得sheet1
//写入标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
#region 导出一页
////写入数值
//for (int r = 0; r < dataGridView1.Rows.Count; r++)
//{
// for (int i = 0; i < dataGridView1.ColumnCount; i++)
// {
// worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
// }
// System.Windows.Forms.Application.DoEvents();
//}
#endregion
ResultModelOfArrayOfModel_Waybill_Based4FqxSXX waybill = cs.GetWaybillList(waybillNumber, null, startTime, endTime, customerId, true, 3, true);
if (waybill.Code != 0)
{
MessageBox.Show(waybill.Message);
}
else
{
//写入数值
for (int r = 0; r < waybill.Data.Length; r++)
{
//在字段前加单引号是为了防止自动转化格式，如057410007009导出后转化格式前面的0不会消失
worksheet.Cells[r + 2, 1] = "'" + waybill.Data[r].Numberk__BackingField;
worksheet.Cells[r + 2, 2] = waybill.Data[r].ReceiverOrgk__BackingField;
worksheet.Cells[r + 2, 3] = waybill.Data[r].ReceiverPersonk__BackingField;
worksheet.Cells[r + 2, 4] = waybill.Data[r].ReceiverTelk__BackingField;
worksheet.Cells[r + 2, 5] = waybill.Data[r].ReceiverAddressk__BackingField;
worksheet.Cells[r + 2, 6] = waybill.Data[r].BeginAtk__BackingField;
worksheet.Cells[r + 2, 7] = waybill.Data[r].BillingCountk__BackingField;
System.Windows.Forms.Application.DoEvents();
}
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (fileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(fileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
excel.Quit();
GC.Collect();//强行销毁
MessageBox.Show(fileName + "的简明资料保存成功", "提示", MessageBoxButtons.OK);
}
}
public void Export2DatasetDSPhongThi(System.Data.DataSet i_DataSet, string i_TableName, int i_iSheetStartRow)
{
try
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
m_objExcelApp = new Excel.Application();
m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
m_objExcelApp.Workbooks[1].Worksheets.Select(1);
m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]);
int i_iExcelRow = 0;
bool v_bol_stop = false;
while (!v_bol_stop)
{
int i_iExcelCol = 0;
System.Data.DataRow v_iDataRow = default(System.Data.DataRow);
v_iDataRow = i_DataSet.Tables[i_DataSet.Tables[i_TableName].TableName].NewRow();
v_iDataRow[i_iExcelCol] = i_iExcelCol + 1;
for (i_iExcelCol = 0; i_iExcelCol <= i_DataSet.Tables[i_TableName].Columns.Count - 2; i_iExcelCol++)
{
if (!object.ReferenceEquals(m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, 4]/*.Value()*/, null))
{
if (!(m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]/*.Value()*/ == null))
{
v_iDataRow[i_iExcelCol + 1] = m_objExcelWorksheet.Cells[i_iExcelRow + i_iSheetStartRow, i_iExcelCol + 1]/*.Value()*/;
}
}
else
{
v_bol_stop = true;
}
}
if (!v_bol_stop)
{
i_DataSet.Tables[i_TableName].Rows.InsertAt(v_iDataRow, i_iExcelRow);
i_iExcelRow++;
}
}
m_objExcelApp.DisplayAlerts = false;
m_objExcelApp.Workbooks.Close();
m_objExcelApp.DisplayAlerts = true;
m_objExcelApp.Quit();
Unmount();
}
catch (Exception v_e)
{
m_objExcelApp.DisplayAlerts = false;
m_objExcelApp.Workbooks.Close();
m_objExcelApp.DisplayAlerts = true;
m_objExcelApp.Quit();
Unmount();
throw (v_e);
}
}
/// <summary>
/// 将DataTable的数据导出显示为报表
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="strTitle">导出报表的标题</param>
/// <param name="FilePath">保存文件的路径</param>
/// <returns></returns>
public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath)
{
beforeTime = DateTime.Now;
Excel.Application excel;
Excel._Workbook xBk;
Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Excel.Application();
xBk = excel.Workbooks.Add(true);
xSt = (Excel._Worksheet)xBk.ActiveSheet;
//取得列标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//设置标题格式为居中对齐
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = titleColorindex;//19;//设置为浅黄色，共计有56种
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//加载一个合计行
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//设置选中的部分的颜色
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种
//取得整个报表的标题
excel.Cells[2, 2] = strTitle;
//设置整个报表的标题格式
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//设置报表表格为最适应宽度
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//设置整个报表的标题为跨列居中
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//绘制边框
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;//设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;//设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;//设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;//设置下边线加粗
afterTime = DateTime.Now;
//显示效果
//excel.Visible=true;
//excel.Sheets[0] = "sss";
ClearFile(FilePath);
string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
excel.ActiveWorkbook.SaveAs(FilePath + filename, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
//excel.Save(strExcelFileName);
#region 结束Excel进程
//需要对Excel的DCOM对象进行配置:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//垃圾回收
#endregion
return filename;
}
/// <summary>
/// 将datatable导出到excel
/// </summary>
/// <param name="dt"></param>
/// <param name="saveasfilepath"></param>
/// <param name="WorkbookName"></param>
public void ExportExcel(System.Data.DataTable dt, string saveasfilepath, string WorkbookName)
{
if (dt == null)
{
return;
}
string saveFileName = saveasfilepath; // "d:\\333.xlsx";
bool fileSaved = false;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
throw new Exception("无法创建Excel对象，可能您的机子未安装Excel");
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
if (!string.IsNullOrEmpty(WorkbookName))
{
Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item(1);
ws.Name = WorkbookName;
}
Excel.Range range;
//string oldCaption = DateTime.Today.ToString("yy-MM-dd");
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
// worksheet.Cells[1, 1] = DateTime.Today.ToString("yy-MM-dd");
//写入字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
//this.lbl_process.Text = "正在导出数据[" + percent.ToString("0.00") + "%]..."; //这里可以自己做一个label用来显示进度.
System.Windows.Forms.Application.DoEvents();
}
//this.lbl_process.Visible = false;
range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
if (saveFileName != "")
{
try
{
//xlApp.Visible = true;
//workbook.PrintPreview(false);
workbook.Close(true, saveFileName, Type.Missing);
workbook = null;
//SaveAs方法会截断超过255个字符的字符串
//workbook.Saved = true;
//workbook.SaveAs(saveFileName, Excel.XlFileFormat.xlExcel7, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing,
// Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing);
//fileSaved = true;
}
catch (Exception ex)
{
xlApp.Quit();
GC.Collect();
fileSaved = false;
throw new Exception("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
else
{
fileSaved = false;
}
xlApp.Quit();
xlApp = null;
GC.Collect();//强行销毁
if (fileSaved && File.Exists(saveFileName))
{
//System.Diagnostics.Process.Start(saveFileName);
}
}
/*<summary>
* 将DataTable中的数据导出到Excel中，并在服务器端AppData文件夹中生成xls文件
* </summary>
* <param name="dt">要导出数据的DataTable</param>
* <param name="head">题头数据</param>
* <param name="absFileName">文件的绝对路径</param>
* <returns></returns>
*/
public static void ExportToExcelDiagramfor9(System.Data.DataTable dt, string[] head, string absFileName)
{
//设置多少行为一个Sheet
int RowsToDivideSheet = 65535;
//计算Sheet数
int sheetCount = (dt.Rows.Count - 1) / RowsToDivideSheet + 1;
GC.Collect();
string excelTemplateDPath; //EXCEL模板默认服务器物理存放路径
string tempEFilePath = "\\TempFiles"; //EXCEL临时文件保存服务器物理存放路径
string tempEFileXPath = "/TempFiles"; //EXCEL临时文件保存服务器虚拟存放路径
Excel.Application excel = null;
Excel._Workbook xBk = null;
Excel._Worksheet xSt = null;
Excel.Workbooks workbooks; //工作簿集合
Excel.Sheets sheets; //SHEET页集合
#region
//读取配置文件中路线模板路径及名称并验证是否存在 //获取配置文件中路线模板路径及名称
excelTemplateDPath = System.Web.HttpContext.Current.Server.MapPath("~/moban");
tempEFileXPath = "~/moban" + tempEFileXPath;
tempEFilePath = excelTemplateDPath + tempEFilePath;
excelTemplateDPath += "\\TaskTemplet9.xls";
//验证EXCEL临时文件夹是否存在
if (!File.Exists(tempEFilePath))
{
Directory.CreateDirectory(tempEFilePath);
}
#endregion
try
{
#region
//启动excel进程并加载模板
//启动EXCEL进程
excel = new Excel.Application();
excel.Visible = false;
excel.UserControl = true;
excel.DisplayAlerts = false;
//加载读取模板
workbooks = excel.Workbooks;
xBk = workbooks.Add(excelTemplateDPath);
sheets = xBk.Worksheets;
xSt = (Excel._Worksheet)sheets.get_Item(1);
#endregion
//循环中要使用的变量
int dvRowStart;
int dvRowEnd;
//对全部Sheet进行操作
for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
//计算起始行
dvRowStart = sheetIndex * RowsToDivideSheet;
dvRowEnd = dvRowStart + RowsToDivideSheet - 1;
if (dvRowEnd > dt.Rows.Count - 1)
{
dvRowEnd = dt.Rows.Count - 1;
}
//创建一个Sheet
if (null == xSt)
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
}
else
{
xSt = (Excel._Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
}
//设置SheetName
xSt.Name = "Excel";
if (sheetCount > 1)
{
xSt.Name += ((int)(sheetIndex + 1)).ToString();
}
//题头导出
int rowCount = head.Length;
int colCount = 1;
object[,] dataArray = new object[colCount, rowCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[j, i] = head[i];
}
}
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).NumberFormatLocal = "@"; //设置单元格格式为文本
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Value2 = dataArray;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[colCount, rowCount]).Font.Size = 10;
//数据导出
System.Data.DataTable SheetTable = new System.Data.DataTable();
foreach (DataColumn dc in dt.Columns)
{
DataColumn newdc = new DataColumn();
newdc.ColumnName = dc.ColumnName;
newdc.DataType = dc.DataType;
SheetTable.Columns.Add(newdc);
}
for (int drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
{
SheetTable.ImportRow(dt.Rows[drvIndex]);
}
//保存数据
rowCount = SheetTable.Rows.Count;
colCount = SheetTable.Columns.Count;
object[,] dataArray1 = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray1[i, j] = SheetTable.Rows[i][j];
}
}
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).NumberFormatLocal = "0.00_ "; //保留小数位数为2;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Value2 = dataArray1;
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Name = "Arial";
xSt.get_Range(xSt.Cells[2, 1], xSt.Cells[rowCount + 1, colCount]).Font.Size = 10;
//单元格边框
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).Borders.LineStyle = 1;
xSt.get_Range(xSt.Cells[1, 1], xSt.Cells[rowCount + 1, colCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //设置居中对齐
xSt.Columns.AutoFit(); //自适应宽度
//设置图标的标题
string Charttitle = dt.Rows[0][0].ToString();
}
//删除Sheet1
excel.DisplayAlerts = false; //注意一定要加上这句
((Microsoft.Office.Interop.Excel.Worksheet)xBk.Worksheets["Sheet1"]).Delete();
excel.DisplayAlerts = true; //注意一定要加上这句
object objOpt = System.Reflection.Missing.Value;
excel.Visible = false;
xBk.SaveCopyAs(absFileName);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
catch (Exception e)
{
//throw (e);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
}
}
static void Main(string[] args)
{
Console.WriteLine("Interop Performance Test - 10.000 Cells.");
/*
* start excel and disable messageboxes and screen updating
*/
Excel.Application excelApplication = new Excel.Application();
excelApplication.DisplayAlerts = false;
excelApplication.ScreenUpdating = false;
//excelApplication.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);
/*
* create new empty worksheet
*/
Excel.Workbooks books = excelApplication.Workbooks;
Excel.Workbook book = books.Add(Missing.Value);
Excel.Sheets sheets = book.Worksheets;
Excel.Worksheet sheet = (Excel.Worksheet)sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
/*
* do the test
* we collect all references and release after time recording
* the 2 latebind libs release the references at end automaticly and we want a fair test
*/
List <object> comReferenesList = new List <object>();
DateTime timeStart = DateTime.Now;
for (int i = 1; i <= 10000; i++)
{
string rangeAdress = "$A" + i.ToString();
Range cellRange = (Range)sheet.Range[rangeAdress];
cellRange.Value = "value";
cellRange.Font.Name = "Verdana";
cellRange.NumberFormat = "@";
cellRange.WrapText = false;
Comment sampleComment = cellRange.AddComment("Sample Comment");
comReferenesList.Add(cellRange);
comReferenesList.Add(sampleComment);
}
DateTime timeEnd = DateTime.Now;
TimeSpan timeElapsed = timeEnd - timeStart;
foreach (var item in comReferenesList)
{
Marshal.ReleaseComObject(item);
}
/*
* display for user
*/
string outputConsole = string.Format("Time Elapsed: {0}{1}Press any Key.", timeElapsed, Environment.NewLine);
Console.WriteLine(outputConsole);
Console.Read();
/*
* write result in logfile
*/
string logFile = Path.Combine(Environment.CurrentDirectory, "Interop.log");
string logFileAppend = timeElapsed.ToString() + Environment.NewLine;
File.AppendAllText(logFile, logFileAppend, Encoding.UTF8);
/*
* release & quit
*/
Marshal.ReleaseComObject(sheet);
Marshal.ReleaseComObject(sheets);
Marshal.ReleaseComObject(book);
Marshal.ReleaseComObject(books);
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
}
public void Export2Grid(C1FlexGrid i_fg, int i_iSheetStartRow, int i_iSheetCol, int i_iGridCol)
{
try
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
m_objExcelApp = new Excel.Application();
m_objExcelApp.Workbooks.Open(m_strTemplateFileNameWithPath, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
m_objExcelApp.Workbooks[1].Worksheets.Select(1);
m_objExcelWorksheet = (Excel.Worksheet)(m_objExcelApp.Workbooks[1].Worksheets[1]);
int v_iGridRow = 0;
for (v_iGridRow = i_fg.Rows.Fixed; v_iGridRow <= i_fg.Rows.Count - 1; v_iGridRow++)
{
i_fg[v_iGridRow, i_iGridCol] = m_objExcelWorksheet.Cells[i_iSheetStartRow + v_iGridRow - i_fg.Rows.Fixed, i_iSheetCol];//.Value
}
m_objExcelApp.Workbooks.Close();
m_objExcelApp.Quit();
Unmount();
}
catch (Exception v_e)
{
m_objExcelApp.Workbooks.Close();
m_objExcelApp.Quit();
Unmount();
throw (v_e);
}
}
