public void InformeGeneralObras()
{
oExcel = new Application();
oBooks = oExcel.Workbooks;
oBook = oBooks.Add(1);
oSheets = (Sheets)oBook.Worksheets;
oSheet = oSheets.get_Item(1);
this.oSheet.Cells[1,1] = "Consecutivo";
this.oSheet.Cells[1,2] = "Título";
this.oSheet.Cells[1,3] = "Núm. de Material";
this.oSheet.Cells[1,4] = "Año";
this.oSheet.Cells[1,5] = "Tiraje";
int ind = 2;
for (int j = 0; j < obrasImprimir.Count; j++)
{
oSheet.Cells[1][ind] = obrasImprimir[j].Consecutivo;
oSheet.Cells[2][ind] = obrasImprimir[j].Titulo;
oSheet.Cells[3][ind] = obrasImprimir[j].NumMaterial;
oSheet.Cells[4][ind] = obrasImprimir[j].AnioPublicacion;
oSheet.Cells[5][ind] = obrasImprimir[j].Tiraje;
ind++;
}
this.oExcel.ActiveWorkbook.Save();
this.oExcel.Quit();
}
private void btnPrint_Click(object sender, EventArgs e)
{
if (dgv.RowCount > 0)
{
btnPrint.Enabled = false;
try
{
if (TempHolder.excelApp == null)
{
TempHolder.excelApp = new Microsoft.Office.Interop.Excel.Application();
}
if (TempHolder.excelApp.Visible)
{
Console.WriteLine("Excel is visible");
TempHolder.excelApp = new Microsoft.Office.Interop.Excel.Application();
}
_Workbook workbook = TempHolder.excelApp.Workbooks.Add(Type.Missing);
_Worksheet worksheet = null;
TempHolder.excelApp.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Exported from gridview";
// storing header part in Excel
int dgvColumnCount = dgv.ColumnCount;
int dgvRowCount = dgv.RowCount;
for (int i = 1; i < dgvColumnCount + 1; i++)
{
worksheet.Cells[1, i] = dgv.Columns[i - 1].HeaderText;
}
// storing Each row and column value to excel sheet
for (int i = 0; i < dgv.Rows.Count - 1; i++)
{
for (int j = 0; j < dgvColumnCount; j++)
{
worksheet.Cells[i + 2, j + 1] = dgv.Rows[i].Cells[j].Value.ToString();
}
}
//to add borders
worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[dgvRowCount, dgvColumnCount]].Borders.LineStyle = XlLineStyle.xlContinuous;
worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[dgvRowCount, dgvColumnCount]].Columns.AutoFit();
}
catch (Exception)
{
MessageBox.Show("Failed to export to Excel", "Oops", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
btnPrint.Enabled = true;
}
}
public static void f_save_file(System.Web.UI.Page page, ref Application app, ref _Workbook workBook, ref Sheets workSheets, ref _Worksheet workSheet, string outputFile)
{
//SQLHelper.ExecuteNonQuery("insert into t_debug values('workBook.SaveAs0')");
FileInfo file = new FileInfo(outputFile);
if (file.Exists)
{
file.Delete();
}
//SQLHelper.ExecuteNonQuery("insert into t_debug values('workBook.SaveAs1')");
workBook.SaveAs(outputFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
SQLHelper.ExecuteNonQuery("insert into t_debug values('workBook.SaveAs2')");
//Close the workbook
// workBook.Close(false, null, false);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
//Close the Excel Process
// app.Quit();
// System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
// app = null;
// GC.Collect();
// PageCommon.outExcel(outputFile);
int generation1 = System.GC.GetGeneration(app);
GC.Collect(generation1);
generation1 = System.GC.GetGeneration(workSheet);
GC.Collect(generation1);
generation1 = System.GC.GetGeneration(workBook);
GC.Collect(generation1);
generation1 = System.GC.GetGeneration(workSheets);
GC.Collect(generation1);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheets);
//Close the workbook
workBook.Close(false, null, false);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
//Close the Excel Process
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
workSheet = null;
workSheets = null;
GC.Collect();
GC.WaitForPendingFinalizers();
//下载到浏览器
//PageCommon.outExcel(outputFile);
//SQLHelper.ExecuteNonQuery("insert into t_debug values('PageCommon.outExcel')");
}
private void DemoOpenDSSUI_FormClosed( object sender, FormClosedEventArgs e )
{
if ( workbook != null )
workbook.Close( false, Type.Missing, Type.Missing );
workbook = null;
if ( application != null )
application.Quit();
application = null;
}
/// <summary>
/// handle tricare report sheet
/// </summary>
/// <param name="generateDate"></param>
public string handleBenchmarkReportSheet(DateTime generateDate)
{
string errorMsg = string.Empty;
DateTime endDay = generateDate.AddDays(-1);
DateTime startDay = generateDate.AddDays(-7);
DateTime priorEndDay = generateDate.AddDays(-8);
try
{
string endDateStr = endDay.ToString("yyyyMMdd");
string startDateStr = startDay.ToString("yyyyMMdd");
string priorEndDateStr = priorEndDay.ToString("yyyyMMdd");
string priorGenerateDateStr = startDateStr;
string priorReportPath = trackWeekly4Path + priorEndDateStr + @"\Tricare Benchmark Report -" + startDateStr + ".xls";
string ReportPath = trackWeekly4Path + endDateStr + @"\Tricare Benchmark Report -" + generateDate.ToString("yyyyMMdd") + ".xls";
string reportTempPath = trackWeekly4Path + endDateStr + @"\TRICATE Team Weekly-" + startDateStr + "-" + endDateStr + ".xls";
File.Copy(priorReportPath, ReportPath, true);
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(ReportPath, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
_Worksheet worksheet = _ExcelWBook.Worksheets["Dashboard"];
DateTime originalDate = Convert.ToDateTime("2013-1-3");
int weekNum = (int)(generateDate - originalDate).Days / 7;
string weeklyMsg = "Week-" + weekNum.ToString() + ": " + startDay.ToString("MM/dd/yyyy") + "-" + endDay.ToString("MM/dd/yyyy");
worksheet.get_Range("D1", "D1").Value = weeklyMsg;
worksheet.get_Range("E3", "I58").Value = null;
worksheet.get_Range("D3", "D58").Copy(Type.Missing);
worksheet.get_Range("I3", "I58").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
worksheet.get_Range("D3", "D58").Value = null;
_Workbook workbook2 = (_Workbook)(_ExcelApp.Workbooks.Open(reportTempPath, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
_Worksheet worksheet2 = workbook2.Worksheets["Benchmark Result"];
worksheet2.get_Range("D3", "H58").Copy(Type.Missing);
worksheet.get_Range("D3", "H58").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
worksheet.Activate();
worksheet.get_Range("A1", "A1").Select();
_ExcelWBook.Save();
_ExcelWBook.Close();
workbook2.Close();
_ExcelApp.Quit();
}
catch (Exception ex)
{
errorMsg = ex.Message;
}
return(errorMsg);
}
/// <summary>
/// Método responsável por criar um novo arquivo de planilha do excel (XLSX ou XLS).
/// </summary>
/// <param name="CaminhoArq">String contendo o caminho + nome + extensão da planilha;</param>
/// <param name="FecharExcelAoCriar">Valor booleando, onde, 'true' encerra os objetos instanciados</param>
/// <param name="MostrarExcel"><Valor booleano, onde, 'true' mostra a planilha de dados./param>
public void CriaExcel(String CaminhoArq, Boolean FecharExcelAoCriar, Boolean MostrarExcel)
{
if (mExcelApp == null)
{
throw new NotImplementedException("Excel não está propriamente instalado");
}
if (String.IsNullOrEmpty(CaminhoArq))
{
throw new NotImplementedException("Caminho do arquivo não existe");
}
if (System.IO.File.Exists(CaminhoArq))
{
throw new System.IO.FileLoadException(String.Format("O arquivo informado '{0}' já existe e não pode ser substituído", CaminhoArq));
}
if (!CaminhoArq.ToUpper().StartsWith("\\") && !CaminhoArq.ToUpper().StartsWith("C") && !CaminhoArq.ToUpper().EndsWith(".XLSX") && !CaminhoArq.ToUpper().EndsWith(".XLS"))
{
throw new ArgumentOutOfRangeException("Caminho informado não está no formato correto (não é unidade de rede nem unidade local ou não possuí a extensão 'XLSX' ou 'XLS'");
}
object misValue = System.Reflection.Missing.Value;
mWorkBook = mExcelApp.Workbooks.Add(misValue);
mWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)mWorkBook.Worksheets.get_Item(1);
//mWorkSheet.Cells[1, 1] = "ID";
//mWorkSheet.Cells[1, 2] = "Name";
//mWorkSheet.Cells[2, 1] = "1";
//mWorkSheet.Cells[2, 2] = "One";
//mWorkSheet.Cells[3, 1] = "2";
//mWorkSheet.Cells[3, 2] = "Two";
//Here saving the file in xlsx
mWorkBook.SaveAs(CaminhoArq, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, misValue,
misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
if (FecharExcelAoCriar)
{
mWorkBook.Close(true, misValue, misValue);
mExcelApp.Quit();
Marshal.ReleaseComObject(mWorkSheet);
Marshal.ReleaseComObject(mWorkBook);
Marshal.ReleaseComObject(mExcelApp);
}
_CaminhoArqExcel = CaminhoArq.ToUpper();
_NumPagPlanilha = 1;
_MostrarExcel = MostrarExcel;
if (_MostrarExcel)
{
AbrirExcel();
}
}
private void btnButikkExport_Click(object sender, EventArgs e)
{
_Application excel = new Microsoft.Office.Interop.Excel.Application();
_Workbook workbook = excel.Workbooks.Add(Type.Missing);
_Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "Avviksskjema_order_ " + lblDatecode.Text;
worksheet.Range["A1"].Value2 = "Ordre nummer:";
worksheet.Range["B1"].Value2 = lblOrdreNumber.Text.ToString();
worksheet.Range["A2"].Value2 = "Departure:";
worksheet.Range["B2"].Value2 = lblDeparture.Text.ToString();
worksheet.Range["A3"].Value2 = "Arrival:";
worksheet.Range["B3"].Value2 = lblArrival.Text.ToString();
worksheet.Range["A4"].Value2 = "Datecode:";
worksheet.Range["B4"].Value2 = lblDatecode.Text.ToString();
for (int colIndex = 4; colIndex < dataGridViewTest.Columns.Count; colIndex++)//copy columns headers
{
worksheet.Cells[6, colIndex - 3] = dataGridViewTest.Columns[colIndex].HeaderText;
worksheet.Cells[6, colIndex - 3].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
}
for (int rowIndex = 0; rowIndex < dataGridViewTest.Rows.Count; rowIndex++)//copy rest of rows
{
for (int colIndex = 4; colIndex < dataGridViewTest.Columns.Count; colIndex++)
{
worksheet.Cells[rowIndex + 7, colIndex - 3] = dataGridViewTest.Rows[rowIndex].Cells[colIndex].Value.ToString();
worksheet.Rows[rowIndex + 7].Rowheight = 13;
}
}
int lastRow = dataGridViewTest.Rows.Count + 6;
worksheet.Cells[dataGridViewTest.Rows.Count + 7, 12].Formula = "=sum(L7:L" + lastRow + ")";
lastRow++;
worksheet.Range[string.Format("L{0}", lastRow.ToString(), lastRow.ToString())].Font.Bold = true;
worksheet.Range[string.Format("L{0}", lastRow.ToString(), lastRow.ToString())].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGreen);
worksheet.Columns[1].ColumnWidth = 16;
worksheet.Columns[2].ColumnWidth = 30;
worksheet.Rows[6].Font.Bold = true;
worksheet.Range["A1:B4"].Font.Bold = true;
worksheet.Range["B1:B4"].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
string file = @"C:\innkjop\avvik_orders\avviksskjema_order_" + lblDatecode.Text.ToString() + ".xlsx";
workbook.SaveAs(file);
Process.Start("excel.exe", file);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public void CloseTest()
{
_Workbook target = Create_Workbook(); // TODO: Initialize to an appropriate value
object SaveChanges = null; // TODO: Initialize to an appropriate value
object Filename = null; // TODO: Initialize to an appropriate value
object RouteWorkbook = null; // TODO: Initialize to an appropriate value
target.Close(SaveChanges, Filename, RouteWorkbook);
Assert.Inconclusive("A method that does not return a value cannot be verified.");
}
public _Workbook CreateOrOpen(string filePath)
{
app = new Application();
Workbooks wbks = app.Workbooks;
this.filePath = filePath;
_Workbook _wbk = wbks.Add();
return(_wbk);
}
public ExcelWriter()
{
_excel = new Application
{
SheetsInNewWorkbook = 1,
Visible = true
};
_workbook = _excel.Workbooks.Add(Missing.Value);
}
// for excel exporting - right click on reference(ctrl+shift+L)
// COM -> excel obj library
// saveFileDialog1 used
private void BtnExportOpen_Click(object sender, EventArgs e)
{
// make new excel obj
_Application excel = new Microsoft.Office.Interop.Excel.Application();
// make a new workbook
_Workbook workbook = excel.Workbooks.Add(Type.Missing);
// make a worksheet and for now set it to null
_Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "Business Contacts";
// because both data grids and excel sheets are tabular
// use nested loops to write from one to the other
// this loop controls the row number
for (int rowIndex = 0; rowIndex < dataGridView1.Rows.Count - 1; rowIndex++)
{
// because there is column header so no need -1
for (int colIndex = 0; colIndex < dataGridView1.Columns.Count; colIndex++)
{
if (rowIndex == 0) // header row
{
// in excel, row and column indexes begin at 1, 1
// write out the header texts from the grid view to excel sheet
worksheet.Cells[rowIndex + 1, colIndex + 1] = dataGridView1.Columns[colIndex].HeaderText;
}
else
{
// fix the row index at 1, then change the index over its possible values
worksheet.Cells[rowIndex + 1, colIndex + 1] = dataGridView1.Rows[rowIndex].Cells[colIndex].Value.ToString();
}
}
} // end for
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
// save file to drive
workbook.SaveAs(saveFileDialog1.FileName);
// open in excel right after
Process.Start("excel.exe", saveFileDialog1.FileName);
}
} // end try
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
public void readExcel()
{
int count = 1;
string raws = "A" + count;
myRange = mySheet.get_Range(raws);
Array.Resize(ref question, 1);
while (Convert.ToString(myRange.Value) != null)
{
label1.Text = Convert.ToString(question.Length);//載入題數
raws = "A" + count;
myRange = mySheet.get_Range(raws);
//string q = Convert.ToString(myRange.Value);由於50%會變成0.5 故改用myRange.Text
string q = myRange.Text;
raws = "B" + count;
myRange = mySheet.get_Range(raws);
//string a = Convert.ToString(myRange.Value);
string a = myRange.Text;
raws = "C" + count;
myRange = mySheet.get_Range(raws);
//string b = Convert.ToString(myRange.Value);
string b = myRange.Text;
raws = "D" + count;
myRange = mySheet.get_Range(raws);
//string c = Convert.ToString(myRange.Value);
string c = myRange.Text;
raws = "E" + count;
myRange = mySheet.get_Range(raws);
//string d = Convert.ToString(myRange.Value);
string d = myRange.Text;
raws = "F" + count;
myRange = mySheet.get_Range(raws);
//string ans = Convert.ToString(myRange.Value);
string ans = myRange.Text;
question[count - 1] = new MyExcelCollection(q, a, b, c, d, ans);
Array.Resize(ref question, question.Length + 1);
raws = "A" + ++count;
myRange = mySheet.get_Range(raws);
//label5.Text = Convert.ToString(count - 1);
}
Array.Resize(ref question, question.Length - 1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myBook = null;
mySheet = null;
myRange = null;
myExcel = null;
GC.Collect();
}
private async Task GerarRelatorioFluviometrico()
{
foreach (var item in selectLstBox.Items)
{
string codigo = item.ToString().Split('-')[0];
Atividade.Text = $"Buscando dados estação {codigo} no Hidroweb-ANA";
var dadosVazaoEstacao = await ServiceANAHelper.DadosFluviometricosVazaoEstacao(Convert.ToInt32(codigo));
var dadosCotasEstacao = await ServiceANAHelper.DadosFluviometricosCotaEstacao(Convert.ToInt32(codigo));
try
{
if (dadosVazaoEstacao.EhValido && dadosCotasEstacao.EhValido)
{
Atividade.Text = $"Gerando planilha para estação: {codigo}";
var dadosSerieHistoricaCotas = DataTableParaSerieHistoricaCota(dadosCotasEstacao.Dados);
var dadosSerieHistoricaVazao = DataTableParaSerieHistoricaVazao(dadosVazaoEstacao.Dados);
var estacao = ListaEstacoesCache.Estacoes.First(c => c.Codigo == Convert.ToInt32(codigo));
_Workbook planilha = ExcelInteropHelper.CriarNovaPlanilhaFluviometrico("item");
planilha = ExcelInteropHelper.CriarAbaEstacaoFluviometrica(planilha, dadosSerieHistoricaVazao, estacao);
planilha = ExcelInteropHelper.CriarAbaCotas(planilha, dadosSerieHistoricaCotas, estacao);
planilha = ExcelInteropHelper.CriarAbaVazao(planilha, dadosSerieHistoricaVazao, estacao);
planilha = ExcelInteropHelper.CriarCotaVazaoDiaria(planilha, dadosSerieHistoricaCotas, dadosSerieHistoricaVazao, estacao);
planilha = ExcelInteropHelper.CriarGraficoCotaTempo(planilha, estacao);
planilha = ExcelInteropHelper.CriarGraficoCotaVazao(planilha, estacao);
Atividade.Text = $"Salvando planilha.";
planilha.SaveAs(escolherDiretorio.SelectedPath + $"/{codigo}", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, null,
null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
null, null, null);
ExcelInteropHelper.FecharAplicacao(planilha);
}
else
{
MessageBox.Show($"Não foi possível encontrar dados para {Convert.ToInt32(codigo)}", "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
}
}
catch (Exception ex)
{
boxBusca.Enabled = true;
boxSelecao.Enabled = true;
Atividade.Text = string.Empty;
MessageBox.Show(ex.Message, "Aviso", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1);
}
}
}
static void Main(string[] args)
{
Console.WriteLine("Creating new Excel.Application");
Application app = new Application();
if (app == null)
{
Console.WriteLine("ERROR: EXCEL couldn't be started!");
}
Console.WriteLine("Making application visible");
app.Visible = true;
Console.WriteLine("Getting the workbooks collection");
Workbooks workbooks = app.Workbooks;
Console.WriteLine("Openning " + @"E:\Games\Perfect World\Teleport calculator\Teleport.xls");
_Workbook workbook = 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);
Console.WriteLine("Getting the worksheets collection");
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)sheets.get_Item(1);
if (worksheet == null)
{
Console.WriteLine("ERROR: worksheet == null");
}
ExcelParser parser = new ExcelParser();
Console.WriteLine("Getting IDs");
parser.GetIDs(worksheet);
Console.WriteLine("Getting horizontal IDs");
parser.GetHorizontalIDs(worksheet);
Console.WriteLine("Making horizontal cost matrix");
parser.MakeCostMatrix(worksheet);
Console.WriteLine("Writing cost XML");
string xmlCostsPath = Path.Combine(System.Windows.Forms.Application.StartupPath, Settings.Default.XmlPath);
xmlCostsPath = Path.Combine(xmlCostsPath, Settings.Default.XmlCostsName);
parser.WriteCostsXml(xmlCostsPath);
Console.WriteLine("Writing tranlations XML");
string xmlTranslationsPath = Path.Combine(System.Windows.Forms.Application.StartupPath, Settings.Default.XmlPath);
xmlTranslationsPath = Path.Combine(xmlTranslationsPath, Settings.Default.TranslationsFolder);
parser.WriteTranlationsXml(xmlTranslationsPath);
Console.WriteLine("Press any key");
Console.ReadKey();
}
public void Export(ObservableCollection <List <CEGRebarInfo> > list)
{
_Application application = (Microsoft.Office.Interop.Excel.Application)Activator.CreateInstance(Marshal.GetTypeFromCLSID(new Guid("00024500-0000-0000-C000-000000000046")));
_Workbook workbook = application.Workbooks.Add(Type.Missing);
int count = workbook.Sheets.Count;
_Worksheet worksheet = (_Worksheet)workbook.Worksheets.Add((dynamic)workbook.Sheets[count], Type.Missing, Type.Missing, Type.Missing);
worksheet.Name = "Rebar Schedule";
dynamic val = worksheet.Range[(dynamic)worksheet.Cells[1, 1], (dynamic)worksheet.Cells[1, 14]];
val.Font.Bold = true;
//val.Merge();
val = worksheet.Range[(dynamic)worksheet.Cells[1, 1], (dynamic)worksheet.Cells[1, 14]];
val.Font.Bold = true;
//val.Merge();
worksheet.Cells[1, 1] = "Control Mark";
worksheet.Cells[1, 2] = "Bar Size";
worksheet.Cells[1, 3] = "Overall Length";
worksheet.Cells[1, 4] = "Type NO.";
worksheet.Cells[1, 5] = "Bend Dia.";
worksheet.Cells[1, 6] = "A";
worksheet.Cells[1, 7] = "B";
worksheet.Cells[1, 8] = "C";
worksheet.Cells[1, 9] = "D";
worksheet.Cells[1, 10] = "E";
worksheet.Cells[1, 11] = "F";
worksheet.Cells[1, 12] = "G";
worksheet.Cells[1, 13] = "H";
worksheet.Cells[1, 14] = "K";
val = worksheet.Range[(dynamic)worksheet.Cells[1, 1], (dynamic)worksheet.Cells[1, 14]];
val.Font.Bold = true;
val.Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
int num = 2;
foreach (var item in list)
{
worksheet.Cells[num, 1] = item.First().ControlMark;
worksheet.Cells[num, 2] = item.First().UnistImperial;
worksheet.Cells[num, 3] = item.First().DimLength;
worksheet.Cells[num, 4] = item.First().TypeNote;
worksheet.Cells[num, 5] = item.First().BenDia;
worksheet.Cells[num, 6] = item.First().BarLengthA;
worksheet.Cells[num, 7] = item.First().BarLengthB;
worksheet.Cells[num, 8] = item.First().BarLengthC;
worksheet.Cells[num, 9] = item.First().BarLengthD;
worksheet.Cells[num, 10] = item.First().BarLengthE;
worksheet.Cells[num, 11] = item.First().BarLengthF;
worksheet.Cells[num, 12] = item.First().BarLengthG;
worksheet.Cells[num, 13] = item.First().BarLengthH;
worksheet.Cells[num, 14] = item.First().BarLengthK;
num++;
}
application.Visible = true;
application.WindowState = XlWindowState.xlMaximized;
}
public void OpenWorkbook(string string_0)
{
this._Workbook_0 = this._Application_0.Workbooks.Open(string_0, this.object_0, this.object_0, this.object_0,
this.object_0, this.object_0, this.object_0, this.object_0, this.object_0, this.object_0, this.object_0,
this.object_0, this.object_0, this.object_0, this.object_0);
if (this._Workbook_0.Worksheets.Count > 0)
{
object obj2 = 1;
this._Worksheet_0 = this._Workbook_0.Worksheets[obj2] as _Worksheet;
}
}
public bool openTemplate()
{
if (File.Exists(templateFilePath))
{
_wbk = wbks.Add(templateFilePath);
shs = _wbk.Sheets;
return(true);
}
return(false);
}
public void Close(bool bool_0)
{
this.ClearClipboard();
object obj2 = bool_0;
if (this._Workbook_0 != null)
{
this._Workbook_0.Close(obj2, this.object_0, this.object_0);
this._Workbook_0 = null;
}
}
public void openExcel(string path)
{
myExcel = new Microsoft.Office.Interop.Excel.Application();
myExcel.Workbooks.Open(path);
myExcel.DisplayAlerts = false;
myExcel.Visible = false;
myBook = myExcel.Workbooks[1];
myBook.Activate();
mySheet = (_Worksheet)myBook.Worksheets[1];
mySheet.Activate();
}
public void CreateExcelWorkbook()
{
Application oXL = new Application();
_Workbook oWB = oWB = oXL.Workbooks.Add("");
_Worksheet oSheet = (_Worksheet)oWB.ActiveSheet;
oSheet.Name = "Units";
Range oRng;
object misvalue = System.Reflection.Missing.Value;
int lr = Units.Count + 2;
AddExcelMergedHeader(oSheet.Range["A1", "A2"], "ID");
AddExternalThickBorders(oSheet.Range["A1", oSheet.Cells[lr, 1]], 2d);
AddExcelMergedHeader(oSheet.Range["B1", "B2"], "TroopID");
AddExternalThickBorders(oSheet.Range["B1", oSheet.Cells[lr, 2]], 2d);
AddExcelMergedHeader(oSheet.Range["C1", "C2"], "Name");
AddExternalThickBorders(oSheet.Range["C1", oSheet.Cells[lr, 3]], 2d);
AddExcelMergedHeader(oSheet.Range["D1", "D2"], "Level");
AddExternalThickBorders(oSheet.Range["D1", oSheet.Cells[lr, 4]], 2d);
AddExcelMergedHeader(oSheet.Range["E1", "E2"], "Faction");
AddExternalThickBorders(oSheet.Range["E1", oSheet.Cells[lr, 5]], 2d);
AddExcelMergedHeader(oSheet.Range["F1", "F2"], "Sex");
AddExternalThickBorders(oSheet.Range["F1", oSheet.Cells[lr, 6]], 2d);
AddExcelMergedHeader(oSheet.Range["G1", "H1"], "Upgrade Path (ID)");
AddExternalThickBorders(oSheet.Range["G1", oSheet.Cells[lr, 8]], 2d);
oSheet.Range["G2"].Value = "1";
oSheet.Range["H2"].Value = "2";
int clmn = 9;
oRng = oSheet.Cells[1, clmn];
AddExcelHeadersForEnum(oSheet, ref clmn, typeof(Unit.Attribute));
AddExternalThickBorders(oSheet.Range[oRng, oSheet.Cells[lr, clmn - 1]], 2d);
oRng = oSheet.Cells[1, clmn];
AddExcelHeadersForEnum(oSheet, ref clmn, typeof(Unit.Proficiency));
AddExternalThickBorders(oSheet.Range[oRng, oSheet.Cells[lr, clmn - 1]], 2d);
oRng = oSheet.Cells[1, clmn];
AddExcelHeadersForEnum(oSheet, ref clmn, typeof(Unit.Skill));
AddExternalThickBorders(oSheet.Range[oRng, oSheet.Cells[lr, clmn - 1]], 2d);
oRng = oSheet.Cells[1, clmn];
AddExcelHeadersForEnum(oSheet, ref clmn, typeof(Unit.Flag));
AddExternalThickBorders(oSheet.Range[oRng, oSheet.Cells[lr, clmn - 1]], 2d);
oRng = (Range)oSheet.Range["A1", oSheet.Cells[2, clmn - 1]];
oRng.Font.Bold = true;
oRng.VerticalAlignment = XlVAlign.xlVAlignCenter;
oRng.HorizontalAlignment = XlHAlign.xlHAlignCenter;
oRng.Borders.LineStyle = XlLineStyle.xlContinuous;
oRng.Borders.Weight = 2d;
AddExternalThickBorders(oRng, 3d);
AddExcelUnitsLines(oSheet, 3);
oSheet.Columns.AutoFit();
oXL.Visible = true;
}
public static void FecharAplicacao(_Workbook workbook)
{
object misValue = System.Reflection.Missing.Value;
workbook.Close(true, misValue, misValue);
app.Quit();
//DesalocarObjeto(workbook.Worksheets);
DesalocarObjeto(workbook);
DesalocarObjeto(app);
}
private void button1_Click(object sender, EventArgs e)
{
progressBar1.Visible = true;
int m;
progressBar1.Minimum = 0;
progressBar1.Maximum = 40000;
for (m = 0; m <= 40000; m++)
{
progressBar1.Value = m;
}
_Application excel = new Microsoft.Office.Interop.Excel.Application();
_Workbook workbook = excel.Workbooks.Add(Type.Missing);
_Worksheet sheet = null;
sheet = workbook.ActiveSheet;
sheet.Name = "DsMuon";
// Thêm dòng tiêu đề
for (int c = 0; c < dgvMuonSach.Columns.Count; c++)
{
sheet.Cells[1, c + 1] = dgvMuonSach.Columns[c].HeaderText;
}
// Thêm các dòng nội dung
for (int i = 0; i < dgvMuonSach.Rows.Count; i++)
{
for (int j = 0; j < dgvMuonSach.Columns.Count; j++)
{
if (dgvMuonSach.Rows[i].Cells[j].Value != null)
{
sheet.Cells[i + 2, j + 1] = dgvMuonSach.Rows[i].Cells[j].Value.ToString();
}
}
}
SaveFileDialog file = new SaveFileDialog();
file.Filter = "Excel 2007 (*.xlsx)|*.xlsx|Excel 2003 (*.xls)|*.xls|All files (*.*)|*.*";
file.FilterIndex = 1;
progressBar1.Visible = false;
file.FileName = "SinhVienMuon";
if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(file.FileName);
workbook.Close();
excel.Quit();
XtraMessageBox.Show("Danh sách mượn sách đã được xuất ra Excel!", "OK", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static _Workbook CriarAbaDiaria(_Workbook workbook, IList <SerieHistoricaChuvas> serieHistorica, EstacaoData estacao)
{
GC.Collect();
_Worksheet worksheet = workbook.Worksheets[3];
SerieHistoricaChuvas linhaEstacao;
DateTime dataInicio = estacao.Inicio.HasValue ? estacao.Inicio.Value : serieHistorica.OrderBy(c => c.Data).First().Data;
DateTime dataIt = new DateTime(dataInicio.Year, 1, 1);
DateTime dataFim = estacao.Fim.HasValue ? estacao.Fim.Value : serieHistorica.OrderByDescending(c => c.Data).First().Data;
long daysQuantity = Convert.ToInt64((dataFim - dataIt).TotalDays) + DateTime.DaysInMonth(dataFim.Year, dataFim.Month);
object[,] dados = new object[daysQuantity, 3];
int i = 0;
int ultimaLinha = 0;
while (dataIt <= dataFim)//Cria todas as linhas até a data fim.
{
//Tenta buscar dados consistidos, senão busca os dados Brutos
linhaEstacao = serieHistorica.FirstOrDefault(c => c.Data == dataIt && c.NivelConsistencia == "2");
if (linhaEstacao == null)
{
linhaEstacao = serieHistorica.FirstOrDefault(c => c.Data == dataIt && c.NivelConsistencia == "1");
}
var totalDias = DateTime.DaysInMonth(dataIt.Year, dataIt.Month);
for (int j = 0; j < totalDias; j++)
{
dados[ultimaLinha, 0] = dataIt.Date;
if (linhaEstacao == null)
{
dados[ultimaLinha, 2] = "i";
}
else
{
dados[ultimaLinha, 1] = linhaEstacao.ChuvasArray[j + 1];
dados[ultimaLinha, 2] = linhaEstacao.NivelConsistencia;
}
ultimaLinha++;
dataIt = dataIt.AddDays(1);
}
}
Range range = worksheet.Cells[3, 2];
range = range.Resize[ultimaLinha, 3];
range.Value = dados;
return(workbook);
}
private static void SaveCloseQuitFile(_Workbook oWB, string newPath, Application oXL)
{
oWB.SaveAs(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, newPath),
XlFileFormat.xlWorkbookDefault,
Type.Missing, Type.Missing,
false, false, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
oXL.Quit();
}
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 Quit()
{
Book.Close();
App.Quit();
Marshal.ReleaseComObject(Book);
Marshal.ReleaseComObject(Books);
Book = null;
Books = null;
rng = null;
App = null;
Sheet = null;
GC.Collect();
}
public void openExcel(String _path)
{
filePatch = _path;
Application app;
Workbooks wbks;
app = new Application();
wbks = app.Workbooks;
_Workbook _wbk = wbks.Add(filePatch);
shs = _wbk.Sheets;
lineNum = 2;
}
/// <summary>
/// 关闭
/// </summary>
public void Close()
{
app.Workbooks.Close();
workbook = null;
worksheet = null;
app.Quit();
app = null;
oMissing = null;
//强制垃圾回收，否则每次实例化Excel，则Excell进程多一个。
System.GC.Collect();
}
// otwiera plik
public ExcelMaker(string path, Stats s)
{
Path = path;
XL = new Application();
XL.Visible = true;
//Get a new workbook.
WB = XL.Workbooks.Open(path);
WS = WB.ActiveSheet;
Stats = s;
}
private void DemoOpenDSSUI_FormClosed(object sender, FormClosedEventArgs e)
{
if (workbook != null)
{
workbook.Close(false, Type.Missing, Type.Missing);
}
workbook = null;
if (application != null)
{
application.Quit();
}
application = null;
}
public static void CreateTicketReport(IEnumerable <Ticket> tickets, string path)
{
if (string.IsNullOrEmpty(path) && File.Exists(path))
{
System.Windows.MessageBox.Show("Неверный путь");
return;
}
var excel = new Application();
excel.Visible = false;
excel.DisplayAlerts = false;
_Workbook workBook = excel.Workbooks.Open(path);
_Worksheet workSheet = (_Worksheet)workBook.ActiveSheet;
workSheet.Name = "Tickets";
workSheet.Cells.ClearContents();
workSheet.Cells[1, 1] = "Id";
workSheet.Cells[1, 2] = "Number";
workSheet.Cells[1, 3] = "Name";
workSheet.Cells[1, 4] = "Surname";
workSheet.Cells[1, 5] = "Departure_time";
workSheet.Cells[1, 6] = "Arrival_time";
workSheet.Cells[1, 7] = "Flight_number";
workSheet.Cells[1, 8] = "Departure_places";
workSheet.Cells[1, 9] = "Arrivals_places";
int index = 2;
foreach (var ticket in tickets)
{
workSheet.Cells[index, 1] = ticket.Id.ToString();
workSheet.Cells[index, 3] = ticket.Name;
workSheet.Cells[index, 5] = ticket.DepartureTime.ToString();
workSheet.Cells[index, 6] = ticket.ArrivalTime.ToString();
workSheet.Cells[index, 7] = ticket.FlightNumber.ToString();
workSheet.Cells[index, 8] = ticket.DeparturePlaces;
workSheet.Cells[index, 9] = ticket.ArrivalsPlaces;
++index;
}
workSheet.Columns.AutoFit();
workBook.Save();
workBook?.Close();
}
private void DailyReportExcel(object sender, RoutedEventArgs e)
{
_Application excel = new Exel.Application();
_Workbook workbook = excel.Workbooks.Add(Type.Missing);
_Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "ExportedFromDatGrid";
int cellRowIndex = 1;
int cellColumnIndex = 1;
for (int i = 0; i < new EnkuDesignDBContext().Transactions.ToList().ToArray().Length; i++)
{
Console.WriteLine("First for");
for (int j = 0; j < DataGrid.Columns.Count; j++)
{
Console.WriteLine("*******Second for");
if (cellRowIndex == 1)
{
Console.WriteLine("iiiiiiIIIIFFFFFF");
worksheet.Cells[cellRowIndex, cellColumnIndex] = DataGrid.Columns[j].Header.ToString();
}
TextBlock textBlock = DataGrid.Columns[j].GetCellContent(DataGrid.Items[i]) as TextBlock;
worksheet.Cells[cellRowIndex + 1, cellColumnIndex] = textBlock.Text;
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
}
Console.WriteLine("----------------------Out of the loops");
workbook.SaveAs("Daily.xlsx");
MessageBox.Show("Exported Successfully!!");
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
/// <summary>
/// Activates a workbook via its name.
/// </summary>
/// <param name="name">The name of the workbook</param>
/// <returns>true if the workbook was found, false otherways</returns>
public bool activateWorkbook(string name)
{
foreach (_Workbook workbook in excel.Workbooks)
{
if (workbook.Name == name)
{
workbook.Activate();
this.workbook = workbook;
this.worksheet = (_Worksheet) workbook.ActiveSheet;
return true;
}
}
return false;
}
public void ExportDataToExcel(string fileName, string[] tableName, string[] sheetName, DataSet ds, DateTime generateDate)
{
PreExitExcel();
string filename = fileName.Substring(0, fileName.LastIndexOf("."));
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
for (int i = 0; i < tableName.Length; i++)
{
if (tableName[i] == "TriBenchMarkRpt_MatchedBacklog")
{
_IniRow = 2;
_sheetName = sheetName[i].ToString();
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 2] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString();
}
_IniRow++;
}
}
else
{
_IniRow = 3;
_sheetName = sheetName[i].ToString();
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
int rowcount = ds.Tables[i].Rows.Count;
for (int j = 0; j < ds.Tables[tableName[i].ToString()].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableName[i].ToString()].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableName[i].ToString()].Rows[j][k].ToString();
}
_IniRow++;
}
}
}
string strDate = generateDate.AddDays(-1).ToString("MMdd");
ExcelHelper excelHelper = new ExcelHelper();
_fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename, strDate);
_ExcelApp.Rows.RowHeight = "15";
//ExApp.DisplayAlerts = false;
_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
_ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
Marshal.ReleaseComObject(_ExcelWSheet);
Marshal.ReleaseComObject(_ExcelWBook);
Marshal.ReleaseComObject(_ExcelApp);
}
catch (Exception e)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
throw e;
}
}
private void btnXZYB_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = @"F:\昆钢项目\"; //指定打开文件默认路径
openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
app = new Excel.Application();
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
object MissingValue = Type.Missing;
workbook = workbooks.Add(openFileDialog.FileName);
txtYBWJ.Text = openFileDialog.FileName;
}
}
//导出线程
private void Export()
{
try
{
model = GetModel.getMode(dgvs, ttName, tjTime);//获取导出数据的模型
//删除大于模板的列
if (model.Columns.Count != 0 && model.Printtable.TableName != "A_InitialData")
{
//判断一个string是否在string[]中
List<string> lst = model.Columns;
for (int j = 0; j < model.Printtable.Columns.Count; j++)
{
if (!lst.Contains(model.Printtable.Columns[j].ColumnName.ToString()))
{
model.Printtable.Columns.Remove(model.Printtable.Columns[j].ColumnName);
j--;
}
}
}
object objOpt = Missing.Value;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
}
catch(Exception ee)
{
if (!ee.Message.Equals("正在中止线程。"))
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
}
//string time = System.DateTime.Now.ToShortDateString() + " " + System.DateTime.Now.ToLongTimeString();
//myop.addOper(time, "无法创建Excel对象，可能您的机子未安装Excel", "NO");
return;
}
excel.Visible = false;
wkb = excel.Workbooks.Add(objOpt);
_Worksheet wks = (_Worksheet)wkb.ActiveSheet;
wks.Visible = XlSheetVisibility.xlSheetVisible;
int rowIndex = 1;
int colIndex = 1;
int x = 0;
int y = 0;
System.Data.DataTable table = ProcessDataTable(model.Printtable);
#region[标题栏]
//标题栏
var RowAll1 = wks.get_Range(wks.Cells[rowIndex, 1], wks.Cells[rowIndex, table.Columns.Count]);
RowAll1.Merge(0);
excel.Cells[rowIndex, 1] = model.Printname;
RowAll1.Font.Size = model.Titlefontsize;
RowAll1.Font.FontStyle = FontStyle.Bold;
RowAll1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
rowIndex++;
#endregion
#region[副标题信息]
//统计时间
var RowAll2 = wks.get_Range(wks.Cells[rowIndex, 1], wks.Cells[rowIndex, table.Columns.Count / 2]);
RowAll2.Merge(0);
excel.Cells[rowIndex, 1] = "打印时间:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
RowAll2.Font.Size = model.Subfontsize;
RowAll2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
var RowAll3 = wks.get_Range(wks.Cells[rowIndex, table.Columns.Count / 2 + 1], wks.Cells[rowIndex, table.Columns.Count]);
RowAll3.Merge(0);
excel.Cells[rowIndex, table.Columns.Count / 2 + 1] = model.Tjtime;
RowAll3.Font.Size = model.Subfontsize;
RowAll3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
rowIndex++;
#endregion
#region[主体信息]
x = rowIndex;
foreach (DataColumn col in table.Columns)
{
excel.Cells[x, colIndex] = dgvs.Columns[col.ColumnName].HeaderText;
excel.Columns.NumberFormatLocal = "@";
colIndex++;
}
//foreach (DataRow row in table.Rows)
//{
// rowIndex++;
// colIndex = 0;
// foreach (DataColumn col in table.Columns)
// {
// try
// {
// colIndex++;
// // excel.Cells[rowIndex, colIndex] = "1";
// excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
// }
// catch (Exception ex)
// {
// }
// //excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
// }
//}
int rowCount = table.Rows.Count;
int colCount = table.Columns.Count;
object[,] dataArray = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[i, j] = table.Rows[i][j].ToString();
}
}
try
{
wks.get_Range("A4", wks.Cells[rowCount + 3, colCount]).Value2 = dataArray;
}
catch(Exception ee)
{
if (!ee.Message.Equals("正在中止线程。"))
{
MessageBox.Show("Excel2003最多只支持65536行数据导出，请重新选择导出数据", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
try
{
wkb.Close(false, objOpt, objOpt);
excel.Quit();
if (this.IsHandleCreated)
this.Invoke(new MethodInvoker(delegate()
{
this.Close();
}));
}
catch { }
}
y = rowIndex;
var RowAll = wks.get_Range("A3", wks.Cells[rowCount + 3, colCount]);
//wks.get_Range(wks.Cells[x, 1], wks.Cells[y, table.Columns.Count]);
RowAll.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
RowAll.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
RowAll.EntireColumn.AutoFit();//列宽自适应
rowIndex = 4 + rowCount;
#endregion
#region[签名栏]
if (table.Columns.Count >= 4)
{
var RowAll4 = wks.get_Range(wks.Cells[rowIndex, 1], wks.Cells[rowIndex, (table.Columns.Count / 4) > 0 ? (table.Columns.Count / 4) : 1]);
RowAll4.Merge(0);
excel.Cells[rowIndex, 1] = model.Signcontent.Split(',')[0].ToString();
RowAll4.Font.Size = model.Signfontsize;
RowAll4.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
var RowAll5 = wks.get_Range(wks.Cells[rowIndex, table.Columns.Count / 4 + 1], wks.Cells[rowIndex, (table.Columns.Count / 2) > 0 ? (table.Columns.Count / 2) : 1]);
RowAll5.Merge(0);
excel.Cells[rowIndex, table.Columns.Count / 4 + 1] = model.Signcontent.Split(',')[1].ToString();
RowAll5.Font.Size = model.Signfontsize;
RowAll5.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
var RowAll6 = wks.get_Range(wks.Cells[rowIndex, table.Columns.Count / 2 + 1], wks.Cells[rowIndex, (table.Columns.Count * 3 / 4) > 0 ? (table.Columns.Count * 3 / 4) : 1]);
RowAll6.Merge(0);
excel.Cells[rowIndex, table.Columns.Count / 2 + 1] = model.Signcontent.Split(',')[2].ToString();
RowAll6.Font.Size = model.Signfontsize;
RowAll6.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
var RowAll7 = wks.get_Range(wks.Cells[rowIndex, table.Columns.Count * 3 / 4 + 1], wks.Cells[rowIndex, table.Columns.Count]);
RowAll7.Merge(0);
excel.Cells[rowIndex, table.Columns.Count * 3 / 4 + 1] = model.Signcontent.Split(',')[3].ToString();
RowAll7.Font.Size = model.Signfontsize;
RowAll7.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
else
{
var RowAll8 = wks.get_Range(wks.Cells[rowIndex, 1], wks.Cells[rowIndex, table.Columns.Count]);
RowAll8.Merge(0);
string s = string.Empty;
foreach (string str in model.Signcontent.Split(','))
{
s += str + " ";
}
excel.Cells[rowIndex, 1] = s;
RowAll8.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
#endregion
#region[获得父窗体句柄]
System.IntPtr IntPart;
IntPart = GetForegroundWindow();
WindowWrapper ParentFrm = new WindowWrapper(IntPart);
#endregion
#region[导出对话框]
string excelFileName = string.Empty;
SaveFileDialog sf = new SaveFileDialog();
sf.Filter = "*.xls|*.*";
if (sf.ShowDialog(ParentFrm) == DialogResult.OK)
{
try
{
excelFileName = sf.FileName;
wkb.SaveAs(excelFileName, objOpt, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
wkb.Close(false, objOpt, objOpt);
excel.Quit();
excel = null;
if (this.IsHandleCreated)
this.Invoke(new MethodInvoker(delegate()
{
label1.Text = "导出成功！";
label1.Left = (this.Width - label1.Width) / 2;
btn_Export.Text = "确定";
btn_Export.Click -= new EventHandler(btn_Export_Click);
btn_Export.Click += new EventHandler(btn_Export_Exit);
pictureBox2.Visible = false;
lblExport.Visible = false;
}));
//MessageBox.Show("导出成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
wkb.Close(false, objOpt, objOpt);
excel.Quit();
if (this.IsHandleCreated)
this.Invoke(new MethodInvoker(delegate()
{
this.Close();
}));
}
}
else
{
wkb.Close(false, objOpt, objOpt);
excel.Quit();
if (this.IsHandleCreated)
this.Invoke(new MethodInvoker(delegate()
{
this.Close();
}));
}
#endregion
}
catch (Exception ee)
{
if (!ee.Message.ToString().Equals("正在中止线程。"))
MessageBox.Show("Excel导出失败[" + ee.Message + "][" + ee.StackTrace + "]");
}
}
public void SaveAndCloseExcel(_Workbook workbook)
{
for (var sheetCount = 1; sheetCount <= workbook.Sheets.Count; sheetCount++)
{
var verSheet = (Worksheet)workbook.Worksheets.Item[sheetCount];
if ("Sheet1" != verSheet.Name) continue;
workbook.Save();
verSheet.Delete();
workbook.Save();
break;
}
workbook.Save();
workbook.Close(null, null, null);
if (_xlApp != null)
{
_xlApp.Quit();
_xlApp =
null;
}
GC.Collect();
}
private void ultraToolbarsManager1_ToolClick(object sender, Infragistics.Win.UltraWinToolbars.ToolClickEventArgs e)
{
switch (e.Tool.Key.ToString())
{
case "Query":
if (!Decision())//判断时间区间//杨滔添加
{
return;
}
{
Query();
break;
}
case "Update":
{
if (this.ultraGrid3.ActiveRow == null || this.ultraGrid3.ActiveRow.Selected == false || this.ultraGrid3.Rows.Count <= 0)
{
MessageBox.Show("请选择一条信息", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (DialogResult.Yes == MessageBox.Show("您确认要修改该条记录吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information))
{
Update();
Query();
}
break;
}
case "Add":
{
Add();
Query();
break;
}
case "Delete":
{
if (DialogResult.Yes == MessageBox.Show("是否确认要删除当前数据?", "删除提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
{
Delete();
Query();
}
break;
}
case "Print":
{
Print();
Query();
break;
}
case "Print1":
{
Print1();
Query();
break;
}
case "ChooseExcel":
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.InitialDirectory = @"C:\桌面\"; //指定打开文件默认路径
openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
app = new Excel.Application();
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
object MissingValue = Type.Missing;
workbook = workbooks.Add(openFileDialog.FileName);
txtYBWJ.Text = openFileDialog.FileName;
}
break;
}
case "BatchUpload":
{
//this.ClearData();
if (this.txtYBWJ.Text == "")
{
MessageBox.Show("请选择要导入的预报信息！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
this.ReadAndSaveExcelInfo();
Query();
this.txtYBWJ.Text = "";
break;
}
default:
break;
}
}
/// <summary>
/// handle tricare report sheet
/// </summary>
/// <param name="generateDate"></param>
public string handleBenchmarkReportSheet(DateTime generateDate)
{
string errorMsg = string.Empty;
DateTime endDay = generateDate.AddDays(-1);
DateTime startDay = generateDate.AddDays(-7);
DateTime priorEndDay = generateDate.AddDays(-8);
try
{
string endDateStr = endDay.ToString("yyyyMMdd");
string startDateStr = startDay.ToString("yyyyMMdd");
string priorEndDateStr = priorEndDay.ToString("yyyyMMdd");
string priorGenerateDateStr = startDateStr;
string priorReportPath = trackWeekly4Path + priorEndDateStr + @"\Tricare Benchmark Report -" + startDateStr + ".xls";
string ReportPath = trackWeekly4Path + endDateStr + @"\Tricare Benchmark Report -" + generateDate.ToString("yyyyMMdd") + ".xls";
string reportTempPath = trackWeekly4Path + endDateStr + @"\TRICATE Team Weekly-" + startDateStr + "-" + endDateStr + ".xls";
File.Copy(priorReportPath, ReportPath, true);
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(ReportPath, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
_Worksheet worksheet = _ExcelWBook.Worksheets["Dashboard"];
DateTime originalDate = Convert.ToDateTime("2013-1-3");
int weekNum = (int)(generateDate - originalDate).Days / 7;
string weeklyMsg = "Week-" + weekNum.ToString() + ": " + startDay.ToString("MM/dd/yyyy") + "-" + endDay.ToString("MM/dd/yyyy");
worksheet.get_Range("D1", "D1").Value = weeklyMsg;
worksheet.get_Range("E3", "I58").Value = null;
worksheet.get_Range("D3", "D58").Copy(Type.Missing);
worksheet.get_Range("I3", "I58").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
worksheet.get_Range("D3", "D58").Value = null;
_Workbook workbook2 = (_Workbook)(_ExcelApp.Workbooks.Open(reportTempPath, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
_Worksheet worksheet2 = workbook2.Worksheets["Benchmark Result"];
worksheet2.get_Range("D3", "H58").Copy(Type.Missing);
worksheet.get_Range("D3", "H58").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
worksheet.Activate();
worksheet.get_Range("A1", "A1").Select();
_ExcelWBook.Save();
_ExcelWBook.Close();
workbook2.Close();
_ExcelApp.Quit();
}
catch (Exception ex)
{
errorMsg = ex.Message;
}
return errorMsg;
}
void windowActivate(Workbook Wb, Window Wn)
{
OfficeConsole.The.sendEvent("Excel.WindowActivateEvent " + Wn.Caption + "/" + Wb.Name);
this.workbook = Wb;
this.worksheet = (_Worksheet) Wb.ActiveSheet;
}
void windowDeactivate(Workbook Wb, Window Wn)
{
OfficeConsole.The.sendEvent("Excel.WindowDeactivateEvent " + Wn.Caption + "/" + Wb.Name);
if (this.workbook == Wb)
{
this.workbook = null;
this.worksheet = null;
}
}
/// <summary>
/// Create excel file.
/// </summary>
public void CreateExcelFile()
{
_excel = new Microsoft.Office.Interop.Excel.Application();
if (_excel == null)
{
throw new InvalidProgramException("Error in opening Excel application.");
}
_excel.Visible = false;
_workbook = (_Workbook)_excel.Workbooks.Add(XlSheetType.xlWorksheet);
_worksheet = (_Worksheet)_workbook.ActiveSheet;
_currentSheetIndex = 0;
if (_workbook == null || _worksheet == null)
{
throw new InvalidProgramException("Error in getting Excel object model.");
}
}
void workbookActivate(Workbook Wb)
{
OfficeConsole.The.sendEvent("Excel.WorkbookActivateEvent " + Wb.Name);
this.workbook = Wb;
this.worksheet = (_Worksheet) Wb.ActiveSheet;
}
private void button1_Click(object sender, EventArgs e)
{
if (myArray == null)
{
MessageBox.Show("请先读取数据");
return;
}
//開啟一個新的應用程式
myExcel = new Excel.Application();
//加入新的活頁簿
myExcel.Workbooks.Add(true);
//停用警告訊息
myExcel.DisplayAlerts = true;
//讓Excel文件可見
myExcel.Visible = true;
//引用第一個活頁簿
myBook = myExcel.Workbooks[1];
//設定活頁簿焦點
myBook.Activate();
//加入新的工作表在第1張工作表之後
myBook.Sheets.Add(Type.Missing, myBook.Worksheets[1], 1, Type.Missing);
//引用第一個工作表
mySheet = (Worksheet)myBook.Worksheets[1];
//命名工作表的名稱為 "Array"
mySheet.Name = "Array";
//設工作表焦點
mySheet.Activate();
int UpBound1 = myArray.GetUpperBound(0);//二維陣列數上限
int UpBound2 = myArray.GetUpperBound(1);//二維陣列數上限
//寫入報表名稱
myExcel.Cells[1, 4] = "全自动生成報表";
//設定範圍
myRange = (Range)mySheet.Range[mySheet.Cells[2, 1], mySheet.Cells[UpBound1 + 2, UpBound2 + 1]];
myRange.Select();
//用陣列一次寫入資料
myRange.Value2 = myArray;
//設定儲存路徑
string PathFile = Directory.GetCurrentDirectory() + @"\我的报表.xlsx";
//另存活頁簿
myBook.SaveAs(PathFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//關閉活頁簿
//myBook.Close(false, Type.Missing, Type.Missing);
////關閉Excel
//myExcel.Quit();
////釋放Excel資源
//System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myBook = null;
mySheet = null;
myRange = null;
myExcel = null;
GC.Collect();
}
//读取数据
private void btRead_Click(object sender, EventArgs e)
{
int MAXLINE = 5000;
int i = 0, j = 0, k = 0, m = 0;//m为总行数
int fileCount = lvFile.Items.Count;
string DataTag;
int eCount = 0;//有效工作簿数
int sCount = 0;//当前表中工作簿数
Point point;
Object missing = Type.Missing;
int iCount = lbContent.Items.Count;
//重点区域，范围型读取单元格区域
RangeSelector mainRange = new RangeSelector(tbMainRange.Text);
//预判断块读取还是固定位置读取，初始化总数组大小
if (mainRange.getWidth() > 0)
myArray = new String[MAXLINE, mainRange.getWidth() + iCount + 1];//最多千行
else
myArray = new String[MAXLINE, iCount + 1];//最多千行
//開啟一個新的應用程式
myExcel = new Excel.Application();
for (i = 0; i < fileCount; i++)
{
//停用警告訊息
myExcel.DisplayAlerts = false;
//讓Excel文件可見
myExcel.Visible = true;
//引用第一個活頁簿
myBook = myExcel.Workbooks.Open(lvFile.Items[i].SubItems[2].Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//設定活頁簿焦點
myBook.Activate();
//判断所有工作簿
sCount = myBook.Worksheets.Count;
for (k = 1; k <= sCount; k++)
{
//大表判断条件
if (cbSheetSelect.Text != "全部" && Int16.Parse(cbSheetSelect.Text) != k) continue;
//选择当前表
mySheet = (Worksheet)myBook.Worksheets[k];
//設工作表焦點
mySheet.Activate();
//特征值判断
if (tbSheetPos.Text != "")
{
point = pointPos(tbSheetPos.Text);
if (mySheet.Cells[point.Y, point.X].Value != tbSheetCont.Text) continue;
}
eCount++;
//备注列判断
if (tbDataTag.Text == "") DataTag = lvFile.Items[i].SubItems[0].Text; //未设置备注默认使用文件名
else
{
Point tagpos = pointPos(tbDataTag.Text);
DataTag = Convert.ToString(mySheet.Cells[tagpos.Y, tagpos.X].Value);
}
string mainStart = tbMainStart.Text;
string mainEnd = tbMainEnd.Text;
//判断选择哪种模式
if (mainRange.Count() > 1)
{
mainRange = new RangeSelector(tbMainRange.Text);//重新恢复原区域值
//重点区域起始位置判断
Point nowPos = mainRange.getCurPos();
for (j = 0; j < mainRange.Count(); j++)
{
string myCell = Convert.ToString(mySheet.Cells[nowPos.Y, nowPos.X].Value);
if (mainStart == "") break;
if (myCell == mainStart) break;
mainRange.acc();
}
//mainRange.lineacc(); //移到关键字下一行
mainRange.SetStartVal(mainRange.getCurPos());
//读取内容
while (m < MAXLINE) //最大读取行数上限估计
{
nowPos = mainRange.getCurPos();
string lineFirstCell = Convert.ToString(mySheet.Cells[nowPos.Y, nowPos.X].Value);
if (lineFirstCell == null|| lineFirstCell=="") break; //首字为空
if (lineFirstCell == tbMainEnd.Text) break; //符合结束字符串
if (mainRange.pos > mainRange.Count()) break;//读取完了就退出
for (j = 0; j < mainRange.getWidth(); j++)//读取一行
{
point = mainRange.getCurPos();
myArray[m, j] = Convert.ToString(mySheet.Cells[point.Y, point.X].Value); //不管什么类型都转为字符串
mainRange.acc();
}
myArray[m, j] = DataTag;
m++;
}
}
else
{
//准备读取单元格相关信息，固定位置读取单元格
if (iCount >= 1)
{
List<Array> ListOfLine = new List<Array>(); //所有的读取行集合
String[] myLine = new String[iCount]; //单行对象
RangeSelector[] rsContentA = new RangeSelector[iCount];
for (j = 0; j < iCount; j++)
{
rsContentA[j] = new RangeSelector(lbContent.Items[j].ToString());
}
j = 0;
foreach (RangeSelector cont in rsContentA)
{
cont.acc();
point = cont.getCurPos();
myArray[m, j] = Convert.ToString(mySheet.Cells[point.Y, point.X].Value); //不管什么类型都转为字符串
j++;
if (j > iCount) break;//xxxxxxx
}
myArray[m, j - 1] = DataTag;
m++;
}
}
}
//关闭当前活页簿
myBook.Close();
System.Windows.Forms.Application.DoEvents();
}
myExcel.Quit();
}
public void ExportDataToExcel_New(string saveFileFullPath, string fileName, string[] tableName, string[] sheetName, DataSet ds)
{
PreExitExcel();
string filename = fileName.Substring(0, fileName.LastIndexOf("."));
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
for (int i = 0; i < tableName.Length; i++)
{
_IniRow = 3;
_sheetName = sheetName[i].ToString();
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[sheetName[i].ToString()];
int rowcount = ds.Tables[i].Rows.Count;
for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[i].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[i].Rows[j][k].ToString();
}
_IniRow++;
}
}
ExcelHelper excelHelper = new ExcelHelper();
_ExcelApp.Rows.RowHeight = "15";
//ExApp.DisplayAlerts = false;
_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
_ExcelWBook.SaveAs(saveFileFullPath, XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
Marshal.ReleaseComObject(_ExcelWSheet);
Marshal.ReleaseComObject(_ExcelWBook);
Marshal.ReleaseComObject(_ExcelApp);
}
catch (Exception e)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
throw e;
}
}
//获取Sheet Name
public string GetFirstSheetName(string filePath)
{
string sheetName = string.Empty;
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(filePath, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
_ExcelWSheet = (Worksheet)_ExcelWBook.Sheets[1];
sheetName = _ExcelWSheet.Name;
_ExcelApp.DisplayAlerts = false;
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
return sheetName;
}
catch (Exception)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
return sheetName;
}
}
/// <summary>
/// Load an Excel workbook.
/// </summary>
/// <param name="file">the filename of the workbook</param>
public bool load(string file)
{
if (this.excel == null) return false;
try
{
this.workbook = excel.Workbooks.Open(file,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing);
this.worksheet = (_Worksheet) this.workbook.ActiveSheet;
//Thread.Sleep(5000);
return true;
}
catch
{
this.workbook = null;
}
return false;
}
public void AEExportDataToExcel(string fileName, string[] tableNames, string[] sheetNames, DataSet ds, int[] irow, DateTime generateDate)
{
PreExitExcel();
string filename1 = fileName.Substring(0, fileName.LastIndexOf("."));
try
{
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(excelTemplatePath + filename1, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
for (int i = 0; i < tableNames.Length; i++)
{
_IniRow = irow[i];
switch (tableNames[i].ToString())
{
case "AnnuityOpenPeriod":
case "AnnuityUrgent":
case "AnnuityStatusUrgent":
_SName = "AnnuityFinal";
break;
case "AnnuityRpt":
_SName = "AnnuityRpt";
break;
case "AnnuityStatus":
_SName = "AnnuityStatus";
break;
case "EBusinessRpt":
_SName = "EBusinessRpt Grouped";
break;
default: ;
break;
}
_ExcelWSheet = (_Worksheet)_ExcelWBook.Sheets[_SName];
for (int j = 0; j < ds.Tables[tableNames[i]].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[tableNames[i]].Columns.Count; k++)
{
_ExcelWSheet.Cells[_IniRow, k + 1] = ds.Tables[tableNames[i]].Rows[j][k].ToString();
}
_IniRow++;
}
}
string strDate = generateDate.AddDays(-1).ToString("MMdd");
ExcelHelper excelHelper = new ExcelHelper();
_fullFilePath = string.Format(excelHelper.getFilename(generateDate) + "\\{0}-{1}.xls", filename1, strDate);
_ExcelApp.Rows.RowHeight = "15";
_ExcelWBook.SaveAs(_fullFilePath, XlFileFormat.xlWorkbookNormal,
null, null, false, false, XlSaveAsAccessMode.xlExclusive, false, false, null, null, null);
_ExcelApp.DisplayAlerts = false;
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
Marshal.ReleaseComObject(_ExcelWSheet);
Marshal.ReleaseComObject(_ExcelWBook);
Marshal.ReleaseComObject(_ExcelApp);
}
catch (Exception e)
{
_ExcelWBook.Close(Missing.Value, Missing.Value, Missing.Value);
_ExcelApp.Workbooks.Close();
_ExcelApp.Quit();
throw e;
}
}
/// <summary>
/// Create a new worksheet
/// </summary>
/// <param name="name">the name of the new worksheet</param>
public void createWorksheet(string name)
{
if (this.excel == null) return;
_Workbook oldWorkbook = this.workbook;
_Worksheet oldWorksheet = this.worksheet;
try
{
workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
worksheet = (_Worksheet)workbook.ActiveSheet;
worksheet.Visible = XlSheetVisibility.xlSheetVisible;
worksheet.Name = name;
#region Example
// Microsoft.Office.Interop.Excel.Range worksheetRange;
// string[] cellValue = new string[4];
// cellValue[0] = "Company A";
// cellValue[1] = "Company B";
// cellValue[2] = "Company C";
// cellValue[3] = "Company D";
// worksheetRange = worksheet.get_Range("A2", "D2");
//#if OFFICEXP
// worksheetRange.set_Value(Missing.Value, cellValue);
//#else
// worksheetRange.Value = cellValue;
//#endif
// double[] dcv = new double[4];
// dcv[0] = 75.0;
// dcv[1] = 14.0;
// dcv[2] = 7.0;
// dcv[3] = 4.0;
// worksheetRange = worksheet.get_Range("A3", "D3");
//#if OFFICEXP
// worksheetRange.set_Value(Missing.Value, dcv);
//#else
// worksheetRange.Value = dcv;
//#endif
// worksheetRange = worksheet.get_Range("A2:D3", Missing.Value);
// Thread.Sleep(2000); //Let it hang around for a few seconds.
// Microsoft.Office.Interop.Excel._Chart chart = (_Chart)workbook.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// chart.Name = "A quick chart";
// chart.ChartWizard(worksheetRange, (int)XlChartType.xl3DPie, 7, (int)XlRowCol.xlRows, 1, 0, 2, "Market Share", Missing.Value, Missing.Value, Missing.Value);
// chart.Visible = XlSheetVisibility.xlSheetVisible;
// // Here is the way to call _Workbook.Worksheet's default property by indexer syntax.
// Microsoft.Office.Interop.Excel._Worksheet worksheet2 = (_Worksheet)workbook.Worksheets["Market Share!"];
// ((_Worksheet)workbook.Worksheets["Market Share!"]).Name = "Fred";
#endregion
}
catch
{
this.workbook = oldWorkbook;
this.worksheet = oldWorksheet;
}
}
private void close()
{
m_app.DisplayAlerts = false;
m_app.AlertBeforeOverwriting = false;
m_app.Quit();
IntPtr t = new IntPtr(m_app.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
m_app = null;
m_book = null; m_books = null;
ReleaseObj(m_book);
ReleaseObj(m_books);
ReleaseObj(m_sheet);
ReleaseObj(m_sheets);
ReleaseObj(m_app);
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
/// <summary>
/// 执行EXCEL导入
/// </summary>
/// <param name="iOpt">1-101,2-261</param>
/// <param name="sCzdw">导入单位名称</param>
/// <returns>返回错误列表</returns>
public ArrayList ExcelToDatabase(int iOpt, string sCzdw)
{
ArrayList lstTmp = new ArrayList();
OpenFileDialog openFileDialog = new OpenFileDialog();
//openFileDialog.InitialDirectory = @"E:\计量系统\安装3.0\"; //指定打开文件默认路径
openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
app = new Excel.Application();
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
object MissingValue = Type.Missing;
workbook = workbooks.Add(openFileDialog.FileName);
}
string strTmpFile = openFileDialog.FileName.ToLower();
if (strTmpFile == "")
{
lstTmp.Add("请选择导入的Excel文件！");
return lstTmp;
}
string strTmp = chkExcute(iOpt, strTmpFile);
if (strTmp != "")
{
lstTmp.Add(strTmp);
return lstTmp;
}
lstTmp = ReadAndSaveExcelInfo(iOpt, sCzdw);
return lstTmp;
}
void workbookDeactivate(Workbook Wb)
{
OfficeConsole.The.sendEvent("Excel.WorkbookDeactivateEvent " + Wb.Name);
if (this.workbook == Wb)
{
this.workbook = null;
this.worksheet = null;
}
}
/// <summary>
/// 从EXCEL导入数据
/// </summary>
private void openExcel()
{
OpenFileDialog openFileDialog = new OpenFileDialog();
// openFileDialog.InitialDirectory = @"E:\计量系统\安装3.0\"; //指定打开文件默认路径
openFileDialog.Filter = "Excel文件|*.xls"; //指定打开默认选择文件类型名
openFileDialog.RestoreDirectory = true;
openFileDialog.FilterIndex = 1;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
app = new Excel.Application();
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
object MissingValue = Type.Missing;
workbook = workbooks.Add(openFileDialog.FileName);
}
if (openFileDialog.FileName == "")
{
MessageBox.Show("请选择导入的Excel文件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
ReadAndSaveExcelInfo();
}
