public void PrintArrayToSheetTemplate(String[,] arr, string sheetName)
{
if ((excelbook != null) & (arr.GetLength(0) > 0))
{
Microsoft.Office.Interop.Excel.Worksheet destSheet = null;
//----- Добавление листа на базе шаблона
if (!(IsSheetExist(sheetName)))
{
destSheet = excelbook.Worksheets.get_Item("template");
destSheet.Copy(excelbook.Worksheets.get_Item("template"));
destSheet = excelbook.Worksheets.get_Item("template (2)");
destSheet.Name = sheetName;
}
else
{
destSheet = excelbook.Worksheets.get_Item(sheetName);
}
destSheet.Select();
destSheet.UsedRange.Clear();
var rng = destSheet.get_Range("A1", System.Reflection.Missing.Value).get_Resize(arr.GetLength(0), arr.GetLength(1));
rng.set_Value(System.Reflection.Missing.Value, arr);
OnReportMessage("Выгружены данные в лист " + sheetName);
}
}
private void HyperLinkWriteWellData_Click(object sender, RoutedEventArgs e)
{
if (this.m_CaseList != null)
{
if (this.m_WorkBook != null)
{
Microsoft.Office.Interop.Excel.Worksheet workSheet = this.m_WorkBook.Sheets["SamplePlacement"];
workSheet.Select(Type.Missing);
TecanSamplePlacementQueue tecanSamplePlacementQueue = new TecanSamplePlacementQueue();
for (int i = this.m_CaseList.Count - 1; i >= 0; i--)
{
if (tecanSamplePlacementQueue.Queue.Count != 0)
{
TecanSample tecanSample = tecanSamplePlacementQueue.Queue.Dequeue();
workSheet.Cells[tecanSample.WellCell.RowIndex, tecanSample.WellCell.ColumnIndex] = TecanSample.GetWellCellValue(this.m_CaseList[i].ReportNo, this.m_CaseList[i].PLastName);
}
}
}
else
{
MessageBox.Show("The spread sheet doesn't appear to be open.");
}
}
else
{
MessageBox.Show("The well data cannot be written in this mode.");
}
}
public override void RunCommand(object sender)
{
var engine = (Core.Automation.Engine.AutomationEngineInstance)sender;
var vInstance = v_InstanceName.ConvertToUserVariable(engine);
var excelObject = engine.GetAppInstance(vInstance);
Microsoft.Office.Interop.Excel.Application excelInstance = (Microsoft.Office.Interop.Excel.Application)excelObject;
string sheetToDelete = v_SheetName.ConvertToUserVariable(sender);
Microsoft.Office.Interop.Excel.Worksheet workSheet = excelInstance.Sheets[sheetToDelete];
workSheet.Select();
}
public static void WriteExcel()
{
int i = 0;
int j = 0;
Random rand = new System.Random(10);
Microsoft.Office.Interop.Excel.Application oXL = default(Microsoft.Office.Interop.Excel.Application);
Microsoft.Office.Interop.Excel.Workbook oWB = default(Microsoft.Office.Interop.Excel.Workbook);
Microsoft.Office.Interop.Excel.Worksheet oSheet = default(Microsoft.Office.Interop.Excel.Worksheet);
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
//oXL.Workbooks.Add() '添加一个新的Excel文件
oWB = oXL.Workbooks.Open("E:\\DDtt.xlsx");
oXL.Sheets.Add();
WriteExcel_k++; //SN计数器
oXL.Sheets[1].Name = "测试SN" + System.Convert.ToString(WriteExcel_k);
oSheet = oWB.Worksheets[oXL.Sheets[1].Name];
oSheet.Select();
((Microsoft.Office.Interop.Excel._Worksheet)oSheet).Activate();
//循环写入数据
oWB.Worksheets[1].Select();
oSheet = oWB.Worksheets[1];
((Microsoft.Office.Interop.Excel._Worksheet)oSheet).Activate();
for (i = 1; i <= 5; i++)
{
for (j = 1; j <= 5; j++)
{
oSheet.Cells[i, j].Value = rand.Next(); //随机数字
}
}
oWB.Save();
//oSheet.SaveAs("E:\DDtt.xlsx")
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
//oXL.Quit()
}
oWB = null;
oXL = null;
}
private void HyperLinkOpenSpreadsheet_Click(object sender, RoutedEventArgs e)
{
if (this.ListBoxFileList.SelectedItem != null)
{
this.m_ExcelApplication = new Microsoft.Office.Interop.Excel.Application();
this.m_ExcelApplication.Visible = true;
this.m_ExcelApplication.DisplayAlerts = true;
this.m_WorkBook = this.m_ExcelApplication.Workbooks.Open(this.ListBoxFileList.SelectedItem.ToString());
Microsoft.Office.Interop.Excel.Worksheet workSheet = this.m_WorkBook.Sheets["SamplePlacement"];
workSheet.Select(Type.Missing);
}
else
{
MessageBox.Show("Please select a file to open.");
}
}
private void AddDocumentNoInLastRow(string filename, string docno)
{
try
{
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks objWorkbooks = ObjExcel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = objWorkbooks.Open(filename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[1];
workSheet.Select(Type.Missing);
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row;
string strRange = string.Format("B{0}", lastRow + 2);
Microsoft.Office.Interop.Excel.Range docIsoRange = workSheet.get_Range(strRange, Type.Missing);
//docIsoRange.EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
docIsoRange.set_Value(System.Type.Missing, docno);
//lastCell.EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
//range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(docIsoRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
book.Save();
book.Close(false, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
ObjExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel);
}
catch (Exception ex)
{
//
}
}
/// <summary>
/// エクセルファイルの指定したシートを2次元配列に読み込む.
/// </summary>
/// <param name="filePath">エクセルファイルのパス</param>
/// <param name="sheetIndex">シートの番号 (1, 2, 3, ...)</param>
/// <param name="startRow">最初の行 (>= 1)</param>
/// <param name="startColmn">最初の列 (>= 1)</param>
/// <param name="lastRow">最後の行</param>
/// <param name="lastColmn">最後の列</param>
/// <returns>シート情報を格納した2次元文字配列. ただしファイル読み込みに失敗したときには null.</returns>
public ArrayList Read(string filePath, int sheetIndex,
int startRow, int startColmn,
int lastRow, int lastColmn)
{
// ワークブックを開く
if (!Open(filePath))
{
return(null);
}
Microsoft.Office.Interop.Excel.Worksheet sheet = mWorkBook.Sheets[sheetIndex];
sheet.Select();
var arrOut = new ArrayList();
for (int r = startRow; r <= lastRow; r++)
{
// 一行読み込む
var row = new ArrayList();
for (int c = startColmn; c <= lastColmn; c++)
{
var cell = sheet.Cells[r, c];
if (cell == null || cell.Value == null)
{
row.Add("");
}
row.Add(cell.Value);
}
arrOut.Add(row);
}
// ワークシートを閉じる
Marshal.ReleaseComObject(sheet);
sheet = null;
// ワークブックとエクセルのプロセスを閉じる
Close();
return(arrOut);
}
private bool WriteHeaderOfResExcel(Microsoft.Office.Interop.Excel.Application excelTo, Microsoft.Office.Interop.Excel.Application excelFrom, int iStartRowID)
{
if (excelTo == null)
{
return(false);
}
if (excelFrom == null)
{
return(false);
}
try
{
Microsoft.Office.Interop.Excel.Worksheet fromSheet = excelFrom.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
Microsoft.Office.Interop.Excel.Worksheet toSheet = excelTo.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
object MissingValue = Type.Missing;
int ColCnt = fromSheet.UsedRange.Columns.Count;
// 选择
fromSheet.Select(MissingValue);
// 复制.
fromSheet.get_Range(fromSheet.Cells[1, 1], fromSheet.Cells[iStartRowID - 1, ColCnt]).Copy(MissingValue);
toSheet.Activate();
toSheet.Select(MissingValue);
// 粘贴格式.
toSheet.get_Range(toSheet.Cells[1, 1], toSheet.Cells[iStartRowID - 1, ColCnt]).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormats, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, MissingValue, MissingValue);
// 粘贴数据.
toSheet.get_Range(toSheet.Cells[1, 1], toSheet.Cells[iStartRowID - 1, ColCnt]).PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, MissingValue, MissingValue);
fromSheet.get_Range(fromSheet.Cells[1, 1], fromSheet.Cells[1, 1]).Copy(MissingValue);
}
catch
{
return(false);
}
return(true);
}
public ResidentCardForm()
{
InitializeComponent();
ClearForm();
LanguageCheckedList.Items.AddRange(Common.Languages);
PrefecturesCombo.Items.AddRange(Common.Prefectures);
if (!File.Exists(SaveFilePath))
{
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbook workbook = ExcelApp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Sheets[1];
worksheet.Select(Type.Missing);
//workbook.SaveAs(SaveFilePath);
workbook.Close(false);
ExcelApp.Quit();
}
ResidentGrid.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
DataSet dataSet = ResidentCardSQL.GetResident();
DataTable dataTable = new DataTable();
dataTable.Columns.Add("Name", typeof(string));
for (int i = 0; i < 4; i++)
{
DataRow row = dataTable.NewRow();
row["Name"] = "名前";
dataTable.Rows.Add(row);
}
dataSet.Tables.Add(dataTable);
ResidentGrid.DataSource = dataTable;
}
/*********************************
*
* 入力ファイルの読み込み
*
* *******************************/
public static void inputTapa(string in_filename)
{
Box.during_make_inputbord = true;
Microsoft.Office.Interop.Excel.Application ExcelApp
= new Microsoft.Office.Interop.Excel.Application();
Console.Write("infilename >> " + in_filename + "\n");
// エクセルの非表示
ExcelApp.Visible = false;
// エクセルファイルのオープンと
// ワークブックの作成
Microsoft.Office.Interop.Excel.Workbook WorkBook = ExcelApp.Workbooks.Open(in_filename);
// 1シート目の選択
Microsoft.Office.Interop.Excel.Worksheet sheet = WorkBook.Sheets[1];
sheet.Select();
// A1セルから見た下への連続データ数
int row_count
= sheet.get_Range("A1").End[Microsoft.Office.Interop.Excel.XlDirection.xlDown].Row;
// A1セルから見た右への連続データ数
int column_count
= sheet.get_Range("A1").End[Microsoft.Office.Interop.Excel.XlDirection.xlToRight].Column;
MAX_BOARD_ROW = row_count; // 行数
MAX_BOARD_COL = column_count; // 列数
BOX_SUM = MAX_BOARD_COL * MAX_BOARD_ROW;
if (DEBUG)
{
Console.WriteLine("row_count >> " + row_count + "\ncolumn_count >> " + column_count + "\n");
Microsoft.Office.Interop.Excel.Range tmp_range;
for (int i = 1; i <= row_count; i++)
{
for (int j = 1; j <= column_count; j++)
{
tmp_range = sheet.Cells[i, j];
if (tmp_range != null)
{
Console.Write(tmp_range.Value.ToString() + ' ');
}
}
Console.Write("\n");
}
}
resetBoard();
// 各セルのデータの取得
for (int i = 1; i <= row_count; i++)
{
for (int j = 1; j <= column_count; j++)
{
int tmp_num = 0;
string st = "";
Microsoft.Office.Interop.Excel.Range range = sheet.Cells[i, j];
if (range.Value != null)
{
st = range.Value.ToString(); // セル(i,j)のデータを文字列で取得
}
else
{
Console.Write("Error: セル(" + i + "," + j + ")の読み込み中にエラー(中身がnull)\n");
}
if (st.Equals("-"))
{
Tapa.box[i][j].hasNum = false;
}
else if (int.TryParse(st, out tmp_num)) // tmp_num=(int)stが数字だった場合
{
int origin_num = tmp_num;
Tapa.box[i][j].hasNum = true;
// ####### (begin) マスの数字を昇順に並べ替える
List <int> tmp_box_num_list = new List <int>();
do // 数字を桁毎にリストに追加
{
tmp_box_num_list.Insert(0, tmp_num % 10);
tmp_num /= 10;
} while (tmp_num > 0); // do-whileは0の場合を許可するため
tmp_box_num_list.Sort(); // 昇順にソート
int digit_pow = (int)Math.Pow(10, origin_num.ToString().Length - 1); // 10^(桁数-1)
tmp_num = 0;
foreach (int _num in tmp_box_num_list)
{
tmp_num += _num * digit_pow;
digit_pow /= 10;
}
Tapa.box[i][j].boxNum = tmp_num;
// ####### (end) マスの数字を昇順に並べ替える
numbox_coord_list.Add(Tapa.box[i][j].coord); // 数字マスの座標Listに追加
Tapa.not_deployedbox_coord_list.Remove(Tapa.box[i][j].coord); // 未定マスリストから除外
}
else
{
Console.WriteLine("Error: セル(" + i + "," + j + ")の読み込み中にエラー(中身が数字でも'-'でもない)\n");
}
}
}
//ワークブックを閉じる
WorkBook.Close();
//エクセルを閉じる
ExcelApp.Quit();
Box.during_make_inputbord = false;
}
private void button3_Click(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
String resourceName = "templateMerge.xlsx";
String path = System.Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
Assembly asm = Assembly.GetExecutingAssembly();
string res = string.Format("{0}.Resources." + resourceName, asm.GetName().Name);
Stream stream = asm.GetManifestResourceStream(res);
try
{
using (Stream filea = File.Create(path + @"\" + resourceName))
{
CopyStream(stream, filea);
}
}
catch (IOException ex)
{
MessageBox.Show(ex.Message);
}
Microsoft.Office.Interop.Excel.Application xls = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlsWorkBook = xls.Workbooks.Open(path + @"\" + resourceName);
try
{
int zadnjiROwO25 = 6;
int zadnjiROwTacke = 5;
int broj = 1;
string line;
System.IO.StreamReader file =
new System.IO.StreamReader(SacuvanTxt);
while ((line = file.ReadLine()) != null)
{
if (line.Substring(line.Length - 3) == "POL")
{
Microsoft.Office.Interop.Excel.Worksheet O25 = xlsWorkBook.Worksheets[1];
O25 = xlsWorkBook.Sheets[1];
O25.Select(true);
O25.Cells[zadnjiROwO25, 1] = line.Split(',')[0];
O25.Cells[zadnjiROwO25, 3] = line.Split(',')[1];
O25.Cells[zadnjiROwO25, 4] = line.Split(',')[2];
O25.Cells[zadnjiROwO25, 5] = line.Split(',')[3];
zadnjiROwO25++;
Microsoft.Office.Interop.Excel.Worksheet Tacke = xlsWorkBook.Worksheets[2];
Tacke = xlsWorkBook.Sheets[2];
Tacke.Select(true);
Tacke.Cells[zadnjiROwTacke, 1] = broj;
Tacke.Cells[zadnjiROwTacke, 2] = line.Split(',')[0];
Tacke.Cells[zadnjiROwTacke, 3] = "POL";
Tacke.Cells[zadnjiROwTacke, 4] = line.Split(',')[1];
Tacke.Cells[zadnjiROwTacke, 5] = line.Split(',')[2];
Tacke.Cells[zadnjiROwTacke, 6] = line.Split(',')[3];
broj++;
zadnjiROwTacke++;
}
else
{
Microsoft.Office.Interop.Excel.Worksheet Tacke = xlsWorkBook.Worksheets[2];
Tacke = xlsWorkBook.Sheets[2];
Tacke.Select(true);
Tacke.Cells[zadnjiROwTacke, 1] = broj;
Tacke.Cells[zadnjiROwTacke, 2] = line.Split(',')[0];
Tacke.Cells[zadnjiROwTacke, 4] = line.Split(',')[1];
Tacke.Cells[zadnjiROwTacke, 5] = line.Split(',')[2];
Tacke.Cells[zadnjiROwTacke, 6] = line.Split(',')[3];
broj++;
zadnjiROwTacke++;
}
}
file.Close();
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx, *.xls)|*.xlsx; *.xls";
saveDialog.FilterIndex = 2;
saveDialog.FileName = "Excel.xlsx";
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
Microsoft.Office.Interop.Excel.Worksheet Tacke = xlsWorkBook.Worksheets[2];
Tacke.SaveAs(saveDialog.FileName);
// Microsoft.Office.Interop.Excel.Worksheet O25 = xlsWorkBook.Worksheets[1];
// O25.SaveAs(saveDialog.FileName);
MessageBox.Show("Zavrseno", "Informacija");
}
xlsWorkBook.Close(true);
xls.Quit();
Marshal.ReleaseComObject(xlsWorkBook);
//Marshal.ReleaseComObject(Tacke);
Marshal.ReleaseComObject(xls);
}
catch
{
xlsWorkBook.Close(true);
xls.Quit();
Marshal.ReleaseComObject(xlsWorkBook);
//Marshal.ReleaseComObject(Tacke);
Marshal.ReleaseComObject(xls);
}
}
/// <summary>
/// Excel出力
/// </summary>
/// <param name="dt"></param>
private void OutputExcel(DataTable dt)
{
// Excel起動
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWb = ExcelApp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet excelWs = excelWb.Sheets[1];
Microsoft.Office.Interop.Excel.Range xlCellsTo = null; // セル終点（中継用）
Microsoft.Office.Interop.Excel.Range xlRangeTo = null; // セル終点
Microsoft.Office.Interop.Excel.Range xlTargetRange = null; // 出力対象レンジ
// Excelウインドウ非表示
ExcelApp.Visible = false;
// EXCEL出力処理
try
{
base.SetBusyForInput();
excelWs.Select(Type.Missing);
// 読み込む範囲を指定する
object[,] values = new object[dt.Rows.Count + 1, dt.Columns.Count];
// ヘッダカラム設定
DataRow columnRow = dt.Rows[0];
for (int i = 0; i < columnRow.Table.Columns.Count; i++)
{
values[0, i] = columnRow.Table.Columns[i].Caption;
}
// 明細部設定
int cnt = 1;
foreach (DataRow row in dt.Rows)
{
values[cnt, 0] = row.Field <int>("締年月").ToString();
values[cnt, 1] = row.Field <int>("品番コード").ToString();
values[cnt, 2] = row.Field <string>("自社品番");
values[cnt, 3] = row.Field <string>("自社色");
values[cnt, 4] = row.Field <string>("自社品名");
values[cnt, 5] = row.Field <string>("色名称");
values[cnt, 6] = row.Field <DateTime?>("賞味期限") == null ? null : row.Field <DateTime?>("賞味期限").Value.ToShortDateString();
values[cnt, 7] = row.Field <int>("倉庫コード").ToString();
values[cnt, 8] = row.Field <string>("倉庫名");
values[cnt, 9] = row.Field <decimal?>("仕入単価").ToString();
values[cnt, 10] = row.Field <int>("在庫数量").ToString();
values[cnt, 11] = row.Field <decimal?>("調整単価");
values[cnt, 12] = row.Field <decimal?>("調整数量");
cnt++;
}
//ヘッダのみ色を変更
xlTargetRange = excelWs.Range["A1", "M1"];
xlTargetRange.Interior.Color = System.Drawing.Color.FromArgb(135, 231, 173);
// 終点セル取得
xlCellsTo = excelWs.Cells;
xlRangeTo = xlCellsTo[dt.Rows.Count + 1, columnRow.Table.Columns.Count] as Microsoft.Office.Interop.Excel.Range;
// 対象レンジ設定
xlTargetRange = excelWs.Range["A1", xlRangeTo];
// データセット
xlTargetRange.Value = values;
// カラム幅セット
xlTargetRange.Columns.AutoFit();
// 罫線セット
xlTargetRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
xlTargetRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
xlTargetRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
xlTargetRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
xlTargetRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
xlTargetRange.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
// 書式を文字列に設定
//xlTargetRange.NumberFormatLocal = "@";
// 書式を数字に設定
//xlTargetRange = excelWs.Range["J1", xlRangeTo];
//xlTargetRange.NumberFormatLocal = "0.00";
#region 前を付けて保存Dialogを出力
WinForms.SaveFileDialog sfd = new WinForms.SaveFileDialog();
// はじめに表示されるフォルダを指定する
//sfd.InitialDirectory = @"C:\";
// [ファイルの種類]に表示される選択肢を指定する
sfd.Filter = "Excelファイル(*.xlsx)|*.xlsx";
// 「CSVファイル」が選択されているようにする
sfd.FilterIndex = 1;
// タイトルを設定する
sfd.Title = "保存先のファイルを選択してください";
// ダイアログボックスを閉じる前に現在のディレクトリを復元するようにする
sfd.RestoreDirectory = true;
#endregion
base.SetFreeForInput();
if (sfd.ShowDialog() == WinForms.DialogResult.OK)
{
base.SetBusyForInput();
ExcelApp.DisplayAlerts = false; // (Excelアプリ)上書きアラート false
// EXCEL出力
excelWb.SaveAs(sfd.FileName);
base.SetFreeForInput();
MessageBox.Show("EXCELファイルの出力が完了しました。");
}
excelWb.Close(false);
ExcelApp.Quit();
}
catch (Exception ex)
{
base.SetFreeForInput();
MessageBox.Show(ex.Message);
}
finally
{
//オブジェクト解放
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlCellsTo);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRangeTo);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlTargetRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
}
}
private void insertData(String filePath)
{
/***** MEMOSファイルの読み込み Start *****/
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
// エクセルを非表示
ExcelApp.Visible = false;
// エクセルファイルのオープン
Microsoft.Office.Interop.Excel.Workbooks WorkBooks = ExcelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook WorkBook = WorkBooks.Open(filePath);
// １シート目の選択
Microsoft.Office.Interop.Excel.Worksheet sheet = WorkBook.Sheets[1];
sheet.Select();
/***** MEMOSファイルの読み込み End *****/
/***** DBにアクセス *****/
String connectionString = "Data Source=ECOLOGDB2016;Initial Catalog=ECOLOGDBver3;Integrated Security=True;Connection Timeout=60";
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
// データ挿入
try
{
// MEMOSデータの読み込み（ヘッダは除く）
for (int i = 2; i <= sheet.UsedRange.Rows.Count; i++)
{
indexLabel.Text = i + " / " + sheet.UsedRange.Rows.Count;
MemosDatum datum = new MemosDatum();
for (int j = 1; j <= END_OF_COLUMN; j++)
{
Microsoft.Office.Interop.Excel.Range range = sheet.Cells[i, j];
datum.setDatumByIndex(j, range.Value);
}
/***** データベースに挿入*****/
// トランザクション生成
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.Transaction = sqlTransaction;
try
{
// 新規にinsert文を発行
String sqlIns = getInsertString(datum);
sqlCommand.CommandText = sqlIns;
// 指定したSQLコマンドを実行してデータを挿入する
sqlCommand.ExecuteNonQuery();
// 上手くいったらコミット
sqlTransaction.Commit();
this.successTextBox.AppendText("Success : " + datum.date + ", " + datum.tripDirection + System.Environment.NewLine);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
this.failedTextBox.AppendText("Failed : " + datum.date + ", " + datum.tripDirection + System.Environment.NewLine);
// 失敗すると例外となるのでロールバック
sqlTransaction.Rollback();
}
}
}
finally
{
sqlConnection.Close();
}
}
// Workbookを閉じる
WorkBook.Close();
WorkBook = null;
WorkBooks.Close();
WorkBooks = null;
// エクセルを閉じる
ExcelApp.Quit();
ExcelApp = null;
errorCodeLabel.Text = "All inserting complete.";
}
/// <summary>
/// Excel取込
/// </summary>
private List <ExcelInputMenber> InputExcel()
{
// Excel起動
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelWb = ExcelApp.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet excelWs = excelWb.Sheets[1];
string inpFileNm;
int val = -1;
DateTime wkDt;
DateTime defaultDt = new DateTime(9999, 12, 31);
DateTime defaultMiniDt = new DateTime(1899, 12, 30);
try
{
#region ファイルを開くDialog
// ファイルを開くDialog
System.Windows.Forms.OpenFileDialog ofDlg = new WinForms.OpenFileDialog();
// デフォルトのフォルダを指定する
ofDlg.InitialDirectory = @"C:";
// [ファイルの種類]に表示される選択肢を指定する
ofDlg.Filter = "Excelファイル(*.xlsx)|*.xlsx";
//ダイアログのタイトルを指定する
ofDlg.Title = "取込ファイルを選択してください";
//ダイアログを表示する
if (ofDlg.ShowDialog() == WinForms.DialogResult.OK)
{
inpFileNm = ofDlg.FileName;
}
else
{
return(null);
}
#endregion
base.SetBusyForInput();
// ブック（ファイル）を開き、１つ目のシートを選択する
excelWb = ExcelApp.Workbooks.Open(inpFileNm);
excelWs = excelWb.Sheets[1];
excelWs.Select();
// 取り込む範囲を指定する
Microsoft.Office.Interop.Excel.Range xlInputRange = excelWs.UsedRange;
// 指定された範囲のセルの値をオブジェクト型の配列に読み込む
object[,] InputObject = (System.Object[, ])xlInputRange.Value2;
int rowCnt = xlInputRange.Rows.Count;
// クローズ
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlInputRange);
excelWb.Close();
ExcelApp.Quit();
// Listに変換
List <ExcelInputMenber> inpList = new List <ExcelInputMenber>();
for (int i = 2; i <= rowCnt; i++)
{
ExcelInputMenber men = new ExcelInputMenber();
men.行番号 = i;
men.締年月 = InputObject[i, 1] != null?
Int32.TryParse(InputObject[i, 1].ToString(), out val) ? val : (int?)null:
(int?)null;
men.品番コード = InputObject[i, 2] != null?
Int32.TryParse(InputObject[i, 2].ToString(), out val) ? val : (int?)null:
(int?)null;
men.自社品番 = InputObject[i, 3] != null ? InputObject[i, 3].ToString() : null;
men.自社色 = InputObject[i, 4] != null ? InputObject[i, 4].ToString() : null;
men.自社品名 = InputObject[i, 5] != null ? InputObject[i, 5].ToString() : null;
men.色名称 = InputObject[i, 6] != null ? InputObject[i, 6].ToString() : null;
men.賞味期限 = InputObject[i, 7] != null ?
(DateTime.TryParse(InputObject[i, 7].ToString(), out wkDt) ? wkDt : (Int32.TryParse(InputObject[i, 7].ToString(), out val) ? defaultMiniDt.AddDays(val) : defaultDt)) :
defaultDt;
men.庫コード = InputObject[i, 8] != null?
Int32.TryParse(InputObject[i, 8].ToString(), out val) ? val : (int?)null:
(int?)null;
men.庫名 = InputObject[i, 9] != null ? InputObject[i, 9].ToString() : null;
men.仕入単価 = InputObject[i, 10] != null ? InputObject[i, 10].ToString() : null;
men.在庫数量 = InputObject[i, 11] != null ? InputObject[i, 11].ToString() : null;
men.調整単価 = InputObject[i, 12] != null ? InputObject[i, 12].ToString() : null;
men.調整数量 = InputObject[i, 13] != null ? InputObject[i, 13].ToString() : null;
inpList.Add(men);
}
return(inpList);
}
catch (Exception ex)
{
MessageBox.Show("Excelファイルの取込に失敗しました。");
throw ex;
}
finally
{
base.SetFreeForInput();
//オブジェクト解放
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
}
}
private void ExportExcelWithInsertTemplate(string filename, string ext)
{
string templateFilename = string.Format("{0}\\{1}\\{2}.{3}", Application.StartupPath, UiUtility.ExcelTemplatePath, this.TPL_FILE_NAME, ext);
try
{
//declare for using Ms.Excel Object
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
ObjExcel.DisplayAlerts = false;
//for template excel
Microsoft.Office.Interop.Excel.Workbooks objTempbooks = ObjExcel.Workbooks;
//for current excel
Microsoft.Office.Interop.Excel.Workbooks objWorkbooks = ObjExcel.Workbooks;
//-----------------------------------------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Workbook book = objWorkbooks.Open(filename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets[this.SheetName];
workSheet.Select(Type.Missing);
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
Microsoft.Office.Interop.Excel.Range insertRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
insertRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
//insert last row
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
//int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row;
//string strRange = string.Format("A{0}", lastRow + 1);
//Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing);
//insertLastRange.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);
//-----------------------------------------------------------------------------------------------------------------------------
Microsoft.Office.Interop.Excel.Workbook tmpBook = objTempbooks.Open(templateFilename, Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Sheets tmpSheets = tmpBook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet tmpWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)tmpSheets[this.SheetName];
tmpWorkSheet.Select(Type.Missing);
Microsoft.Office.Interop.Excel.Range copyRange = tmpWorkSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
copyRange.EntireRow.Copy(Type.Missing);
Microsoft.Office.Interop.Excel.Range pasteRange = workSheet.get_Range(UiUtility.ExcelHeaderRange, Type.Missing);
pasteRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//-----------------------------------------------------------------------------------------------------------------------------
//Microsoft.Office.Interop.Excel.Range copyLastRange = tmpWorkSheet.get_Range("A11", Type.Missing);
//copyLastRange.EntireRow.Copy(Type.Missing);
//Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)workSheet.UsedRange;
//int lastRow = range.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing).Row;
//string strRange = string.Format("A{0}", lastRow + 1);
//Microsoft.Office.Interop.Excel.Range insertLastRange = workSheet.get_Range(strRange, Type.Missing);
//insertLastRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAllUsingSourceTheme, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
//-------------------------------------------------------------------------------------------------------------------------------------
book.Application.CutCopyMode = Microsoft.Office.Interop.Excel.XlCutCopyMode.xlCopy;
//book.CheckCompatibility = false;
tmpBook.Close(false, Type.Missing, Type.Missing);
if (this._ShippingOrder_Hdr != null)
{
//Set Shipping Order Information.
//SO_NO
Microsoft.Office.Interop.Excel.Range sonoRange = workSheet.get_Range("K2", Type.Missing);
sonoRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.SO_NO);
//SO_DATE
Microsoft.Office.Interop.Excel.Range sodateRange = workSheet.get_Range("K3", Type.Missing);
if (this._ShippingOrder_Hdr.SO_DATE.HasValue)
{
sodateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.SO_DATE.Value));
}
else
{
sodateRange.set_Value(System.Type.Missing, string.Empty);
}
//Customer
Microsoft.Office.Interop.Excel.Range custRange = workSheet.get_Range("B4", Type.Missing);
custRange.set_Value(System.Type.Missing, string.Format("{0} - {1}", this._ShippingOrder_Hdr.PARTY_ID, this._ShippingOrder_Hdr.PARTY_NAME));
//PO_REF_NO
Microsoft.Office.Interop.Excel.Range porefRange = workSheet.get_Range("B5", Type.Missing);
porefRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REF_NO);
//PO_REF_DATE
Microsoft.Office.Interop.Excel.Range podateRange = workSheet.get_Range("G5", Type.Missing);
if (this._ShippingOrder_Hdr.REF_DATE.HasValue)
{
podateRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy}", this._ShippingOrder_Hdr.REF_DATE.Value));
}
else
{
podateRange.set_Value(System.Type.Missing, string.Empty);
}
//ETD
Microsoft.Office.Interop.Excel.Range etdRange = workSheet.get_Range("K5", Type.Missing);
if (this._ShippingOrder_Hdr.ETA.HasValue)
{
etdRange.set_Value(System.Type.Missing, string.Format("{0:dd-MM-yyyy HH:mm}", this._ShippingOrder_Hdr.ETA.Value));
}
else
{
etdRange.set_Value(System.Type.Missing, string.Empty);
}
//REMARK
Microsoft.Office.Interop.Excel.Range remarkRange = workSheet.get_Range("B6", Type.Missing);
remarkRange.set_Value(System.Type.Missing, this._ShippingOrder_Hdr.REMARK);
var focusRange = workSheet.get_Range("A1", "A1").Select();
book.Save();
book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sonoRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sodateRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(custRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(porefRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(podateRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(etdRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(remarkRange);
}
else
{
var focusRange = workSheet.get_Range("A1", "A1").Select();
book.Save();
book.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(focusRange);
}
//-------------------------------------------------------------------------------------
//System.Runtime.InteropServices.Marshal.ReleaseComObject(insertLastRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(copyRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(tmpBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(insertRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
ObjExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ObjExcel);
}
catch (Exception ex)
{
//
}
}
private void insertData(String filePath, int driverID, int carID, int sensorID, bool nullAllowance)
{
DataTable TRIPS_TABLE = new DataTable();
/***** LeafSpyファイルの読み込み Start *****/
Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
//エクセルを非表示
ExcelApp.Visible = false;
//エクセルファイルのオープン
Microsoft.Office.Interop.Excel.Workbooks WorkBooks = ExcelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook WorkBook = WorkBooks.Open(filePath);
//1シート目の選択
Microsoft.Office.Interop.Excel.Worksheet sheet = WorkBook.Sheets[1];
sheet.Select();
/***** LeafSpyファイルの読み込み End *****/
/***** DBにアクセス *****/
String connectionString = "Data Source=ECOLOGDB2016;Initial Catalog=ECOLOGDBver3;Integrated Security=True;Connection Timeout=60";
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
sqlConnection.Open();
/***** TRIPS_TABLE生成 Start *****/
string query = "SELECT TRIP_ID, START_TIME, END_TIME FROM [ECOLOGDBver3].[dbo].[TRIPS_LINKS_LOOKUP2] WHERE DRIVER_ID = " + driverID + " AND CAR_ID = " + carID + "AND SENSOR_ID = " + sensorID;
SqlCommand command = new SqlCommand(query, sqlConnection);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(TRIPS_TABLE);
/***** TRIPS_TABLE生成 End *****/
// データ挿入
try
{
// LeafSpyデータの読み込み ヘッダを除いてスタート
for (int i = 2; i <= sheet.UsedRange.Rows.Count; i++)
{
indexLabel.Text = i + " / " + sheet.UsedRange.Rows.Count;
LeafSpyDatum datum = new LeafSpyDatum();
for (int j = 1; j < END_OF_COLUMN; j++)
{
// 挿入しないデータはスルー
if (j >= REJECT_COLUMNS_START && j <= REJECT_COLUMNS_END)
{
continue;
}
Microsoft.Office.Interop.Excel.Range range = sheet.Cells[i, j];
datum.setDatumByIndex(j, range.Value);
}
// 規定値を設定
datum.setDriverId(driverID);
datum.setCarId(carID);
/**** トリップ判別 Start ****/
int tripID = 0;
for (int k = 0; k < TRIPS_TABLE.Rows.Count; k++)
{
// +- 5minute
DateTime startTime = (DateTime)TRIPS_TABLE.Rows[k][1];
DateTime startEdge = startTime.AddMinutes(-5);
DateTime endTime = (DateTime)TRIPS_TABLE.Rows[k][2];
DateTime endEdge = endTime.AddMinutes(5);
if (startEdge < datum.dateTime && datum.dateTime < endEdge)
{
tripID = (int)TRIPS_TABLE.Rows[k][0];
datum.setTripId((int)TRIPS_TABLE.Rows[k][0]);
// データベースに挿入
// トランザクション生成
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.Transaction = sqlTransaction;
try
{
// 新規にInsert文を発行
String sqlIns = getInsertString(datum, false);
sqlCommand.CommandText = sqlIns;
// 指定した SQL コマンドを実行してデータを挿入する
sqlCommand.ExecuteNonQuery();
// 旨くいったらコミット
sqlTransaction.Commit();
this.successInsertingTextBox.AppendText("Success : " + tripID + " , " + datum.dateTime + System.Environment.NewLine);
}
catch (Exception e)
{
tripID = -1;
Console.WriteLine(e.Message);
this.failedInsertingTextBox.AppendText("Failed : " + datum.dateTime + System.Environment.NewLine);
// 失敗すると例外となるので，ロールバック
sqlTransaction.Rollback();
}
}
}
/**** トリップ判別 End ****/
// NULL許容ならばTrip ID NULLでインサート
if (tripID == 0 && nullAllowance == true)
{
// データベースに挿入
// トランザクション生成
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
SqlCommand sqlCommand = sqlConnection.CreateCommand();
sqlCommand.Transaction = sqlTransaction;
try
{
// 新規にInsert文を発行
String sqlIns = getInsertString(datum, true);
sqlCommand.CommandText = sqlIns;
// 指定した SQL コマンドを実行してデータを挿入する
sqlCommand.ExecuteNonQuery();
// 旨くいったらコミット
sqlTransaction.Commit();
this.successInsertingTextBox.AppendText("Success : " + "NULL" + " , " + datum.dateTime + System.Environment.NewLine);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
this.failedInsertingTextBox.AppendText("Failed : " + datum.dateTime + System.Environment.NewLine);
// 失敗すると例外となるので，ロールバック
sqlTransaction.Rollback();
}
}
}
}
finally
{
sqlConnection.Close();
}
}
//workbookを閉じる
WorkBook.Close();
WorkBook = null;
WorkBooks.Close();
WorkBooks = null;
//エクセルを閉じる
ExcelApp.Quit();
ExcelApp = null;
errorCodeLabel.Text = "All inserting complete";
}
/// <summary>
/// 写入Excel的Sheet表
/// </summary>
/// <param name="wb"></param>
/// <param name="tables"></param>
private void WriteExcelSheet(Microsoft.Office.Interop.Excel.Workbook wb)
{
List <string> SheetNames = GetSheetsName(wb); //Sheet表名字集合,防止相同名称,出现错误!
for (int i = 0; i < _tables.Count; i++)
{
System.Data.DataTable table = _tables[i];
int rows = table.Rows.Count;
int cols = table.Columns.Count;
//获取Sheet表
Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[i + 1]; //wb.Worksheets 所以从1开始
//选中表
wsheet.Select();
//设置表名字
wsheet.Name = GetCorrectSheetName(SheetNames, table.TableName, (i + 1)); //Name要特别注意
//保存DataTable和Sheet表的绑定
SheetTable.Add(wsheet.Name, table);
//设置总标题
DrawTitle(wsheet, cols);
//设置HeaderText
if (IsDrawHeader)
{
DrawText(wsheet, _headerdtext, _headerfont, TextAlign.xlHAlignLeft, cols);// Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft, cols);
}
//数据列表标题
//Microsoft.Office.Interop.Excel.Range r = ret.get_Range(ret.Cells[1, 1], ret.Cells[1, table.Columns.Count]);
if (_isbodylistheader)
{
_rowindex++;
Microsoft.Office.Interop.Excel.Range range = wsheet.Range[wsheet.Cells[_rowindex, 1], wsheet.Cells[_rowindex, cols]];
object[] header = new object[cols];
for (int j = 0; j < cols; j++)
{
header[j] = table.Columns[j].ToString();
}
range.Value2 = header;
SetFont(range, _bodyheaderfont);
if (_isalldisplayborder)
{
range.Borders.LineStyle = 1; //边框线
range.Borders.Weight = (int)_borderweight;
}
}
if (rows > 0)
{
_rowindex++;
Microsoft.Office.Interop.Excel.Range range = wsheet.get_Range("A" + _rowindex, Missing.Value);
object[,] objData = new Object[rows, cols];
for (int row = 0; row < rows; row++)
{
for (int col = 0; col < cols; col++)
{
string converttxt = String.Empty;
if (_isautoconverttext)
{
converttxt = "'";
}
objData[row, col] = converttxt + table.Rows[row][col].ToString(); //随后数据后面加单引号
}
}
range = range.get_Resize(rows, cols);
range.Value2 = objData;
SetFont(range, _bodyfont);
if (_isalldisplayborder)
{
range.Borders.LineStyle = 1; //加线框
range.Borders.Weight = (int)_borderweight;
}
else
{
//((Microsoft.Office.Interop.Excel.Range)range.Columns["A:A", Type.Missing]).Borders.LineStyle = 1;
//((Microsoft.Office.Interop.Excel.Range)range.Columns["A:A", Type.Missing]).Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium;
//指定的列显示边框
foreach (var vk in _columnsborder)
{
Microsoft.Office.Interop.Excel.Borders borders = ((Microsoft.Office.Interop.Excel.Range)range.Columns[vk.Key, Type.Missing]).Borders;
borders.LineStyle = 1;
borders.Weight = (int)vk.Value;
}
}
if (IsAutoFit)
{
range.EntireColumn.AutoFit(); //自动适应列
}
//更新行索引
_rowindex += (rows - 1);
}
//设置FooterText
if (IsDrawFooter)
{
if (rows == 0)
{
_rowindex += 2; //与Body隔一行
}
DrawText(wsheet, _footertext, _footerfont, DrawFooterTextAlign, cols);
}
//所有列自动换行
wsheet.Columns.WrapText = _iswraptext;
//设置列宽
if (!IsAutoFit)
{
foreach (var vk in _columnswidth)
{
((Microsoft.Office.Interop.Excel.Range)wsheet.Columns[vk.Key, System.Type.Missing]).ColumnWidth = vk.Value;
}
}
//保存名字
SheetNames.Add(wsheet.Name);
//还原
_rowindex = _saveindex;
}
//选中第一个表
((Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1]).Select();
}
